transaction log in sql server 2000
-
tran log of my database is now hge in size and my disc space is almost full. what do i do now? should i take backup of tran log or change the path of transaction log file to another disc location.. please suggest..
I'm assuming you aren't backing up your transaction log at all. If that is the case then change the recovery model of your database to Simple. Then detach your database, delete the transaction log file and re-attach your database. A new log file will be created for you. To set your recovery model to simple use:
ALTER DATABASE [databasename] SET RECOVERY SIMPLEDo NOT do this while users are logged in. Wait for a time when there is no activity on your database. If you don't have a maintenance window which allows you to do this you will need to truncate your log and then shrink it. To shrink your transaction log file, first get the logical name of your transaction log by running sp_helpdb 'your database name'. There will be 2 resultsets, the second one will have a column "name", for the row that refers to your log file, that is the logical name. Then you can run the following script:BACKUP LOG [Database Name] WITH TRUNCATE_ONLY GO DBCC SHRINKFILE([LOGICAL FILE NAME], [SIZE]) GOwhere [size] is an integer value in megabytes. Again, this is only if you are not using your transaction log. The commands will be different if you need to backup your log file. Refer to BOL.Mark's blog: developMENTALmadness.blogspot.com Funniest variable name: lLongDong - spotted in legacy code, was used to determine how long a beep should be. - Dave Bacher
-
I'm assuming you aren't backing up your transaction log at all. If that is the case then change the recovery model of your database to Simple. Then detach your database, delete the transaction log file and re-attach your database. A new log file will be created for you. To set your recovery model to simple use:
ALTER DATABASE [databasename] SET RECOVERY SIMPLEDo NOT do this while users are logged in. Wait for a time when there is no activity on your database. If you don't have a maintenance window which allows you to do this you will need to truncate your log and then shrink it. To shrink your transaction log file, first get the logical name of your transaction log by running sp_helpdb 'your database name'. There will be 2 resultsets, the second one will have a column "name", for the row that refers to your log file, that is the logical name. Then you can run the following script:BACKUP LOG [Database Name] WITH TRUNCATE_ONLY GO DBCC SHRINKFILE([LOGICAL FILE NAME], [SIZE]) GOwhere [size] is an integer value in megabytes. Again, this is only if you are not using your transaction log. The commands will be different if you need to backup your log file. Refer to BOL.Mark's blog: developMENTALmadness.blogspot.com Funniest variable name: lLongDong - spotted in legacy code, was used to determine how long a beep should be. - Dave Bacher
-
tran log of my database is now hge in size and my disc space is almost full. what do i do now? should i take backup of tran log or change the path of transaction log file to another disc location.. please suggest..
In answer to your question:
sohne wrote:
change the path of transaction log file to another disc location..
It's a good idea to keep the transaction log on a different disk to the database - doing this will speed up SQL Server.
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)