in

ASP.NET Weblogs

MindFill - Brian Carroll's Blog

Do While (WhatYouKnow) < (WhatYouKnow + 1)

Truncating and Shrinking a SQL Database Log

I don't do this often enough to remember, but I've included it here for future reference.  I find that I usually only need to do this when a client fails to backup their transacation logs for a while and then they call (in panic) because they have run out of drive space. 

Truncate the log to the oldest transaction:
BACKUP LOG dbname WITH NO_LOG

To make the transaction file physically smaller, issue this command:
DBCC SHRINKFILE ('logical filename', target MB)

If the backup doesn't seem to shrink the log file as much as expected, issue the following command to see the open transactions:
DBCC OPENTRAN('dbname')

Published May 13 2004, 10:48 AM by bkcarroll
Filed under:

Comments

No Comments

Leave a Comment

(required)  
(optional)
(required)  
Add