How to Shrink The Transaction Log in Microsoft SQL 2005/2008

In Microsoft SQL the data goes through a transaction log file before it get written to an actual data file. The transaction log is reset after a full database backup and a transaction file is backed up.  The purpose of the transaction log file is so that you could have up to a millisecond recovery if for some reason something could go wrong with your database or the database server.

If you have a lot activity in your database, the transaction log file will grow in hundreds of megabytes or gigabytes. I have seen databases sizes to 400MB and its transaction log file up 30GB. If your transaction log file is big then your database server may get slower and eventually will run out of resources.

Here are some commands to help shrink the transaction log file.

MSSQL 2000 and MSSQL 2005


BACKUP Log DatabaseName WITH NO_LOG
DBCC SHRINKFILE (DatabaseName _Log, 100)

MSSQL 2008

Use DatabaseName
GO
Alter Database DatabaseName Set Recovery Simple
GO
DBCC SHRINKFILE ('Databsename_log', 1)
GO
Alter Database DatabaseName Set Recovery Full
GO

About onlinejt

Blogger

,

No comments yet.

Leave a Reply

*