Shrink SQL Database

Problem

How to shrink a SQL Database and manage log file size?

Solution

In order to shrink a db you need to set the recovery mode to simple first. You may set it to full at end.

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

In order to manage the size of log files in SQL Server you need to right click on the database in SSMS. Go to Properties. Go to File page and click on three dots next to database file(s) with their File Type set to LOG. Then you can manage the file size.

Leave a comment