Quickly Shrink a SQL Server Transaction log

Posted At : May 9, 2007 1:23 PM | Posted By : Erik Voldengen
Related Categories: Database

Okay, say you encounter a SQL Server Database that's got a HUGE transaction log, and you need to shrink it quickly. MS has several KB articles such as this one.

But if you really want to shrink the log file fast, and you don't need to keep the transaction log, here's a short cut:

Obviously, you'll need to stop using the database for a few minutes. All SQL processes that access the Database need to be closed.

The first step is to detach the Database. Do not skip this step; it's rather important for the health of your DB:

--detach the database:
EXEC sp_detach_db dbName

The next step is to delete the transaction log file (the .ldf file, NOT the .mdf). Note you are DELETING the transaction log. Obviously, it will no longer be available.

Finally, reattach the Database:

--attach the database:
EXEC sp_attach_single_file_db dbName, 'c\mssql\data\dbName.mdf'