Me Myself & C#

Manoj Garg’s Tech Bytes – What I learned Today

Truncating Transaction Log in SQLServer 2000

Posted by Manoj Garg on July 17, 2007

Few days back I had a situation when size of my database transcation log was growing too  fast (at a point it reached 60 GB). so I had to find some way to truncate it to some minimal size. After lot of googling, I found how to truncate the transaction log.

Run these two commands in Query Analyser.

  1. BACKUP LOG <<DBName>> WITH TRUNCATE_ONLY
  2. DBCC SHRINKFILE(<<DBName>>_log, <<Desired transaction filesize>> )

Remarks :

  • DBCC SHRINKFILE applies to the files in the current database. If you try to  run this command in a different DB then the DB you want to shrink, SQL Server will give following error

Server: Msg 8985, Level 16, State 1, Line 1
Could not locate file ‘<<DBName>>_log’ in sysfiles.

  • SQL Server uses <<Desired transaction filesize>> to calculate the target size for the entire log; therefore, target_size is the amount of free space in the log after the shrink operation. Target size for the entire log is then translated to target size for each log file. DBCC SHRINKFILE attempts to shrink each physical log file to its target size immediately. If no part of the logical log resides in the virtual logs beyond the log file’s target size, the file is successfully truncated and DBCC SHRINKFILE completes with no messages. However, if part of the logical log resides in the virtual logs beyond the target size, SQL Server frees as much space as possible and then issues an informational message. The message tells you what actions you need to perform to move the logical log out of the virtual logs at the end of the file. (Remarks are taken from MSDN)

References…..

  1. Microsoft Knowledge Base (KB272318)
  2. DBCC SHRINKFILE
Ha-P Querying 🙂
Advertisements

3 Responses to “Truncating Transaction Log in SQLServer 2000”

  1. Tarun said

    Manoj,
    Thanks much for sharing this. I’ve few questions —
    1. What happens if SQL Server is not able to truncate the file to the desired size? What kind of exceptions does it throw? What is the alternative in that scenario?
    2. What is the optimal size of the transaction log after which it starts affecting performance?

    I would really appreciate if you could blog about the answers to the following questions as well.

  2. Manoj Garg said

    Hi Tarun,

    Updated the post in response to your question 1. For Q2, I need to search more. Will update the blog as soon as I get some answer for this.

    Thanks for bringing up these questions.


    Manoj 🙂

  3. Hello Manoj,

    First of all thanx a lot for sharing this information. sometimes back my friend had this problem and i suggested him this article after this he resolved his problem. Hoping some more article from you. 🙂

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

 
%d bloggers like this: