How to TRUNCATE transaction log of a SQL Server database?

[Former Member]

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.

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

Currently occupied transaction log size is 100 gb ..Can I make it as 60 gb by setting the target size. If we do ..will it lead for data loss ..I shrink the database but I could get 100 gb only.Now I am going out of space for transaction log..what is the prequisite for truncating translog

[Former Member · Accepted solution]

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.