SQL Server Transaction Logs Shrink

As a DBA, I watched our database transaction log file, sometimes it grows really crazy and occupied even the full hard drive.  How to resolve this issue.

1. Check if there is a effective transaction log backup.

We all know that there are two database backup mode, full recovery mode and simple mode. Under Full mode, the transaction log won’t shrink till there is transaction log backup happens. But there is a trick, If you just switch into Full Recovery mode, but never take an initial Full Backup, SQL Server will not honor your request to be in Full Recovery model. Your transaction log will continue to operate as it has in simple until you switch to Full Recovery Model AND Take your first Full Backup.

2. Other concerns my cause the trans log keep growing

  • Uncommitted Transactions
  • Extremely Large Transactions
  • Operations: DBCC DBREINDEX and CREATE INDEX
  • While Restoring from Transaction Log Backups
  • Client Applications Do Not Process All Results
  • Queries Time Out Before a Transaction Log Completes the Expansion and You Receive False ‘Log Full’ Error Messages
  • Un-replicated Transactions

3. If it still doesn’t work

There are maybe two solutions you can consider.

a. setup a limit of the transaction log size

b. force the log file to shrink either manually or by script/schedule job

Back up your database! 

Execute the following, substituting <log_file_name_Log> with the appropriate logical name of the database log file, no quotes needed:

  1. DBCC SHRINKFILE(<log_file_name_Log>)
  2. BACKUP LOG <database> WITH TRUNCATE_ONLY
  3. DBCC SHRINKFILE(<log_file_name_Log>)

Afterwords, perform a full backup of the database.

The file should shrink to a ridiculously small shadow of its former self, most likely it could be 1MB.

Advertisements

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