Tuesday, August 18, 2009 6:59 AM
We are planing to migrate SQL Server 2000 to SQL Server 2008.
In production SQL Server 2000, we are clearing database transaction log using below steps.
Rename Log file
New log file will be recreated
Delete Renamed Log file
Its working fine in SQL Server 2000, But same steps not working in SQL Server 2008.
I also followed below Urls steps How to clear log file in SQL Server 2008. But no result.
Can you please tell me how can i clear database transaction log file in SQl Server 2008.
Tuesday, August 18, 2009 7:13 AMModeratorWhat are you trying to achieve here? It seems a very strange set of steps - certainly not something i've seen before.
Why do you want to delete your log file and create a new one? Can't you just put your database in SIMPLE recovery mode so that the size of the transaction log is kept to a minimum and you can always run DBCC SHRINKFILE(YourTransactionLog, TRUNCATEONLY) if you need to physically reduce the size.
every day is a school day
Tuesday, August 18, 2009 7:15 AMHi Krishna,
The procedure you have been using is highly unrecommended and dangerous.
Please read the following article which I believe will clear things up:
- Marked As Answer by Xiao-Min Tan – MSFTModerator Tuesday, August 25, 2009 9:41 AM
Tuesday, August 18, 2009 7:26 AM
Why you have to detach the database for clearing transaction log ? Personnaly there is no advantage to make this.
To detach an database you have to set the base offline (and the activity at the same time). In an production environnement i don't think it's a good pratice.
If the T-LOG is no important for you , you can set recovery SIMPLE for your database. (The TLOG will be cleared automaticly)
If the TLOG is important for you strategy backup you have to set recovery FULL for your database and implement BACKUP LOG on a schedule. This actions are made in online mode.
MCDBA | MCITP SQL Server 2005 | LPI Linux 1
- Marked As Answer by Xiao-Min Tan – MSFTModerator Tuesday, August 25, 2009 9:42 AM