MSSQL, Tips and TricksSQL Server shrinking log file size

SQL Server shrinking log file size

This is a quick post to show how to shrink the size of the log file for a database.
Be extremely careful when doing this and always backup the entire database. Try on a test database or server first.
Do this at your own risk.

-- database name = MyDatabaseName
-- the database log file name = MyDatabaseName_log
-- database recovery type = Full
USE MyDatabaseName;
GO

-- Truncate the log by changing the database recovery model to SIMPLE.
-- You need to do this if your database recovery mode is set to FULL
ALTER DATABASE MyDatabaseName SET RECOVERY SIMPLE;
GO

-- Shrink the log file to 100 MB.
DBCC SHRINKFILE (MyDatabaseName_log, 100);
GO

-- Reset the database recovery model.
ALTER DATABASE MyDatabaseName SET RECOVERY FULL;
GO

Categories: MSSQL, Tips and Tricks Tags:

Comments

No Comments Yet. Be the first?

Post a comment

Your email address will not be published. Required fields are marked *