Shrinking file in MSSQL 2008

If you have worked with previous version of SQL server, you know there is always an issue – the log file growing very fast and big.  If you have plenty of storage, then this might not be a problem for you.  Anyway, this has no exception in the latest version of SQL, we still have to do something to truncate and shrink these files, however they have changed the script command a little bit, so I would like to share with everyone as usual.

Here is the link to the official Technet page http://technet.microsoft.com/en-us/library/ms189493.aspx, it gives good examples and explanations.

For example if we need to shrink the size of log file to 100MB, the code will look like

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

 

Run this script in the query screen, you will notice the file size changes in seconds.