Home » Database

Shrink SQL Server Log File

8 February 2011 No Comment

Shrink SQL Server Log FileQuestion: I have a transaction log file in SQL Server 2000 that is over 1GB in size. Does it need to be this big? How do I shrink it to a small size such as 10MB?

Answer: If the size of your data file is less than 1GB, then your transaction log file should be less too.

You can run a SQL script that will truncate your log file. Be sure to perform a full backup before running this script because all the transaction logs will be wiped off afterwards.

Open your SQL Server 2000 Enterprise Manager. From there, run the following:

USE <database_name>
GO
DBCC SHRINKFILE(<transaction_log_name>, <new_log_size>)
BACKUP LOG <database_name> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<transaction_log_name>, <new_log_size>)

For example, if you have a database called Books and the transaction log file is called Books_Log, then you would run the following:

USE Books
GO
DBCC SHRINKFILE(Books_Log, 10)
BACKUP LOG <Books WITH TRUNCATE_ONLY
DBCC SHRINKFILE(Books_Log, 10)

The above script will shrink the Books transaction log file to 10MB.

Leave your response!

Add your comment below, or trackback from your own site. You can also subscribe to these comments via RSS.

Be nice. Keep it clean. Stay on topic. No spam.

You can use these tags:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This is a Gravatar-enabled weblog. To get your own globally-recognized-avatar, please register at Gravatar.