[RESOLVED] Monsterous Transaction Log

[Dave Sell]

All,

we have a MSSQL DB that has a transaction log approaching a GB! How do I tame it? We researched the issue and it seems we need to create some kind of "restore point" on the database, but have been unable to determine how to do this.

Any ideas? Will DBCC ShrinkDatabase do it? Remember, it is the transaction log that is out of control in size.

Thanks!

Dave

[kaffenils]

You should either backup the transaction log or truncate it with the TRUNCATE_ONLY option.

BACKUP LOG YouDbName [WITH TRUNCATE_ONLY]

After you have truncated it, you can shrink the actual file by executing the DBCC SHRINKDATABASE statement, but as long as you have enough disk space I don't see a reason for shrinking the physical size.

[Dave Sell]

You should either backup the transaction log or truncate it with the TRUNCATE_ONLY option.

BACKUP LOG YouDbName [WITH TRUNCATE_ONLY]

After you have truncated it, you can shrink the actual file by executing the DBCC SHRINKDATABASE statement, but as long as you have enough disk space I don't see a reason for shrinking the physical size.

Great! I did all this and was able to shrink the T-log somewhat, but this code doesn't work:

BACKUP LOG YouDbName [WITH TRUNCATE_ONLY] TO MyDevice

This code does work but am wondering how to use the truncate function:

BACKUP LOG YouDbName TO MyDevice

[Dave Sell]

Looks like I got it now. I used:

BACKUP LOG MyDBName WITH TRUNCATE_ONLY

Then,

DBCC SHRINKDATABASE (MyDBName)

Works like a charm, thanks!

[Dave Sell]

For anyone else interested, you can backup the T-Log to disk by making a device then backing up to it:

-- (1) Create the log backup device.
/*
USE myDBName
EXEC sp_addumpdevice 'disk', 'myDBNameBackup',
   'c:\Temp\myDBName.dat'
*/

-- (2) Perform the backup - this option may only reduce the LOG file by 50%
/*
BACKUP LOG myDBName  
   TO myDBNameBackup
*/

-- (2) Alternative - this option brings the LOG file back down to 1 MB.
/*
BACKUP LOG myDBName WITH TRUNCATE_ONLY
*/

-- (3) Shrink or compact the database
/*
DBCC SHRINKDATABASE (myDBName)
*/