SQL Server Tip – Shrinking a SQL Server log

If you want to shrink the size of the transaction log, which you think has become much larger than you would like, there are a two options.

Detach and Attach
First I will explain a trick for quickly remove the log file by detaching and automatically recreate a new log file by attaching the database. Remember that this would not work if the database has more than one log file. When you detach the database using sp_detach_db or using the UI in SQL Server Management Studio, SQL Server will know that the database was cleanly shutdown. This will ensure that the log file does not need to be available to attach the database again, so you could delete it. When you attach the database again, SQL Server will create a new log file for you, which will be of the minimum size.

The following are the steps to do:

  1. Detach the database using SP_DETACH_DB procedure (Ensure no processes are using the database files.) 
  2. Delete the log file
  3. Attach the database again using SP_ATTACH_DB procedure

or

  1. Open SQL Server Management Studio
  2. Open content sensitive menu of the database
  3. Choose Tasks > Detach
  4. Delete the log file
  5. Open content sensitive menu of the server
  6. Choose Attach
  7. Select the the database .mdf


    Note: Make sure you remove the the log file from the database details, as you can see in the upper screenshot.

Since you are detaching and attaching the database within the same server you will not have the problem of broken logins.

Backup and Shrink file
This option is not a trick, but the official way to shrink the transaction log file. Before you will use it, I will like to mention this option not always result in the way you want it. I haven’t found out why, but sometimes the size of the log file will not result in the size you requested.

The following are the steps to do:

  1. Backup the the transaction log using BACKUP LOG database WITH TRUNCATE_ONLY. Where database is the name of the database.
  2. Shrink the file using DBCC SHRINKFILE(database_log, 50). Where 50 is the preferred size in MB of the log file.

or

  1. Open SQL Server Management Studio
  2. Open content sensitive menu of the database
  3. Choose Tasks > Back Up
  4. Choose Back up type – Transaction Log
  5. Open content sensitive menu of the database
  6. Choose Tasks > Shrink > Files
  7. Choose File type – Log
  8. Choose Shrink Action (it’s good to leave some empty space. e.g. 50 MB)

Summary
For shrinking the log consisting of one or more log files, you can find very good documentation by searching the article kb256650 (for SQL 7.0) and article kb272318 (for SQL 2000) in Microsoft Knowledge Base. Another interesting article is How to stop the transaction log of a SQL Server database from growing unexpectedly, which applies to SQL 2000 and 2005.

 

Technorati tags:

6 thoughts on “SQL Server Tip – Shrinking a SQL Server log”

  1. No offense but telling someone to shutdown SQL and delete a transaction log is just about one of the worst things you can do. Don’t delete the transaction log to save space…ever.

    http://www.sqlskills.com/BLOGS/PAUL/post/Importance-of-proper-transaction-log-size-management.aspx

    Take a look at option 7 from that survey and find out why you shouldn’t do that. I apologize if I come off as snarky but just want to make you aware of why something as simple as deleting a t-log can cause you a world of hurt.

  2. What terrible advice. You should *NEVER* manage the log by deleting it or dumping the contents using TRUNCATE_ONLY or NO_LOG – you don’t even explain the implications of doing this. These commands are so dangerous my team removed them from SQL Server 2008.

    Proper transaction log size management includes taking log backups, avoiding long running operations or just switching to SIMPLE. See http://www.sqlskills.com/BLOGS/PAUL/post/Importance-of-proper-transaction-log-size-management.aspx (no advertizing or anything)

    Thanks

  3. Never, ever, ever delete the log file. This is not an answer. Also, using backup log with truncate_only can seriously jeopardize your backup/recovery methodology. Never mind that it is deprecated and no longer works in SQL Server 2008. Please see this recent blog post and read all the links that follow; I hope you will post a correction to this blog post so that more people are not lulled into the feeling that this “solution” makes sense.

    http://sqlblog.com/blogs/aaron_bertrand/archive/2009/07/27/oh-the-horror-please-stop-telling-people-they-should-shrink-their-log-files.aspx

Leave a Reply

Your email address will not be published. Required fields are marked *

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