How do I shrink/truncate the transaction log?

June 4, 2008 at 10:22 pm 2 comments

Every DBA’s nightmare… Your boss is running around the office with his hands waving in the air “We’ve run out of disk space! We need more disk space!”. After a bit of investigation you realise the transaction log has blown up to a disastrous size! But don’t stress, it is possible to truncate, or shrink the log file.

Note, however, that you will lose your current transaction log. All transaction logs between your last full backup and the time that you run the script below will be useless. If you need to restore, you will need to use your last full backup, or wait for your next full backup.

You will then need to run two SQL scripts:

a) Backup the current transaction log to NULL

BACKUP LOG DBName WITH TRUNCATE_ONLY

Where DBName is the name of the database whose transaction log you are trying to decrease in size.

b) Shrink the transaction log

DBCC SHRINKFILE(DBName_Log, 1)

Where DBName_Log is the name of the transaction log you are trying to shrink. This can be found on the Transaction Log tab of the Database Properties window.

Note: Once you have finished this task, it is recommended that you perform a Full backup of your database as soon as possible.

Advertisements

Entry filed under: SQL Scripts. Tags: , , , , , , , , , .

Welcome! How do I list all Primary Keys in a SQL Server 2000/2005 database?

2 Comments Add your own

  • 1. Joe Herr  |  September 12, 2008 at 11:54 pm

    Thanks. That’s useful info for a novice DBA like myself.

    Reply
  • 2. dba4life  |  October 5, 2008 at 8:41 pm

    Hi Joe,

    Thanks for visiting! I’m glad you’ve found my blog useful 🙂

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


Calendar

June 2008
M T W T F S S
    Jul »
 1
2345678
9101112131415
16171819202122
23242526272829
30  

Archives

Blog Stats

  • 32,513 hits

%d bloggers like this: