Wednesday, April 23, 2008

Shrinking SQL Server Log File

I've been trying to work out how to do this for ages and today I've finally found the answer from Books Online. I think that a lot of us experience this. When you're searching the help files and resources you can't find the answer but later when you're not looking for it you stumble across it. For my notes, here is the example. Interestingly on most of my DB's I don't need full recovery so I've skipped the last step and left the DB's at simple recovery.

USE AdventureWorks;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks
SET RECOVERY FULL;
GO

No comments:

Post a Comment