Skip to Content
avatar image
Former Member

How to TRUNCATE transaction log of a SQL Server database?

Hello All,

I hav installed BW 3.5 on Windows / MS SQL database.

Now my transaction log is growing very high in my Quality & Production server. Please guide me how can i truncate its size.

In PRD currently log size has reached 140 GB.

Wat shd i do system is becoming too slow.

Regards & Thanks,

Ankita.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Jan 10, 2008 at 07:46 AM

    Hi,

    Run the following Transact-SQL statements in Query analyzer:

    1. Clear log file :

    BACKUP LOG DatabaseName WITH NO_LOG

    2. Shrink the transaction log file.

    DBCC SHRINKFILE (<FileName>, <TargetSize>) WITH NO_INFOMSGS

    3. If the DBCC SHRINKFILE statement does not shrink the transaction log file to the target size, run the BACKUP LOG statement that is mentioned in step 1 to make more of the virtual log files inactive

    4. Run the DBCC SHRINKFILE statement that is mentioned in step 2. After this operation, the transaction log file should be similar to the target size.

    Regards,

    wojtek

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Ankita,

      Do you backup your transaction logs? That should be standard practice in a production enivorment at the least. The backup should remove the data and shrink the file, or remove enough data for you to manually shrink it.

      A large transaction log will not slow down your system. It is a file that is only appended to and has no real bearing on the speed of the database (unless its on the same physical disk and there is I/O contention not related to size).

      Depending your your BI implentation a 140 GB log file is not out of the ordinary, but it needs to be managed with backups and administration.

      Daniel,

      If you backup the transaction file to tape, set the target size to 60 GBs, and run the DBCC Shrinkfile statement provided above you can decrease the size of your log file without data loss and without compromising your backup strategy.

      There is no single prerequisite for truncating a log. It is a last resort if there is no other option and a backup is impossible. It should be avoided whenever possible because you destroy your recovery chain.