Friday, September 6, 2013

Shrink Data/Log Files : Microsoft SQL Server

This is a quick post on how to Shrink a data or log file running a query. Tried Shrinking by going through Tasks->Shrink, but didn't work. This query managed to shrink the needed log file to 1MB (which was passed as a parameter).

Tested on Microsoft SQL Server 2008R2.
USE [AdventureWorks2008R2];
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE [AdventureWorks2008R2]
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2008R2_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE [AdventureWorks2008R2]
SET RECOVERY FULL;
GO
For more information, visit DBCC SHRINKFILE (Transact-SQL)

Happy Coding.

Regards,
Jaliya