Truncate SQL transaction logs

[bmurphysr]

The SQL 2000 transaction logs have grown to 21 GB. What is the best way to truncate the logs without causing any issues to the VC.

[esiebert7625]

Shutdown VC first and then follow these instructions...

http://sqlserver2000.databases.aspfaq.com/how-do-i-reclaim-space-in-sql-server.html

Also here's some info I documented on how to reclaim space in the db...

How can I reclaim space in my SQL database after I have purged old data from it?

• If you have deleted old task/events or perfomance data by default your database will not automatically shrink to recover this free space and reduce the physical size of your VC database file on your host OS disk. You can set your VC database to automatically shrink by loading SQL Server Enterprise Manager, selecting your VC database, then Properties and on the Options tabs you can check the “Auto Shrink” option. Alternately you can manually shrink the database by doing the below steps. This procedure will shrink both the database (mdf file) and transaction log (ldf file).

o Load the SQL Query Analyzer on the SQL Server and login with your virtualcenter database credentials

o Type the following in the query window

 DBCC Shrinkdatabase ( database name, target free space percent ) ie. DBCC Shrinkdatabase ( virtualcenter, 10 )

o This will shrink your database down and reduce the physical file size and leave 10% room for further growth. Your database will auto grow onit’s own once it uses up the 10% growth space.

Fyi…if you find this post helpful, please award points using the Helpful/Correct buttons.

-=-=-=-=-=-=-=-=-=-=-==-=-=-=-=-=-=-=-=-=-=-=-

Thanks, Eric

Visit my website: http://vmware-land.com

-=-=-=-=-=-=-=-=-=-=-==-=-=-=-=-=-=-=-=-=-=-=-

[VirtualNoitall]

Hello,

Not sure how much disk space you have but the easiest way to solve this, if you have the space, is to run a transaction log backup and follow that with a job to shrink the database.

Both these can be quick and easily setup in a database maintenance plan to run on a regular basis. You could even setup the database maintenance plan as you want to to run and then find the individual jobs and run them now fo fix it up.

The following link talks about the commands to complete this two step process manually: http://www.mssqlcity.com/FAQ/Trouble/LogTrunc.htm

[Dave_Mishchenko]

You database is likely set to Full recovery mode and you don't have any transaction logs backup jobs scheduled. You should first decide what sort of recovery you need for this database.

If you're running a full nightly backup and would be OK to restore your database back to that backup should it fail then you should

1) Bring up the properties on the database, go to options and set the recover mode to simple.

2) Then right click on the databases, select all tasks \ shrink database. The click on Files and shrink the log file only. Somewhere in the range of 500 MB to 1 GB should be fine.

With the database is simple recovery mode, you would not have to worry about backing up the transaction logs.

If you could not afford to go back to the last full backup and would need to recover to something more recent then you would

1) Set the database recovery model to simple. Then click OK. Then change it back to Full recovery mode. This will eliminate the need to run a 21 GB transaction log backup.

2) Run a full backup.

3) Shrink the log file as described above.

4) Edit the maintenance plan for the db to run transaction log backup (say every 3 to 4 hours).

Also, you want to avoid shrinking the main database file as you'll take an I/O hit when it needs to expand again, but this shouldn't be a big deal if the SQL server is just running the VC database.

[bmurphysr]

Thanks Dave I used your second method it just seemed to make more sense for me. Thanks for the help.