Archive for the ‘MSSQL’ Category
July 28th, 2009 | 
Author: Recently I was trying to move VMware VirtualCenter database from one server to another and found that for 250MB database I have about more than 30GB of transaction log (LDF) files. I wanted to get rid of this file. There are 2-3 methods but the method I’ve used was thew following :
1) Open SQL Management Studio Express
2) Detach the database from the SQL Server Management Studio.
3) Go to the location of transaction log and rename it (don’t delete the file yet!!)
4) Attach the database without the log file. When you do this step make sure you highlight the log file entry in the wizard and remove it. (Final attach would happen only with one file i.e. with the MDF file).
At this point there would be a new log file created by the SQL Server.
5) Edit the database properties, and select the preferred increment policy of your newly created log file, enabling automtic grow, define increment step and also define maximum dimension to avoid the problem above.
4) Delete the log file once the database is attached and you have verified it.
You can save a lot of space with above steps.
I’ve tested this procedure with VMware VirtualCenter 2.5 Update4, Microsoft SQL 2005 Express with the latest service pack, Microsoft Management Studio Express, and all works fine.
If i remeber well, this would work even with SQL 2000.
Hope this help
Bye
Riccardo
Print This Post
