Just in Chronicles

Life as a Voyage

Downsizing MS-SQL Server 2008 R2 Database Log

leave a comment »

When MS-SQL database files need to do backup and restore, we need to shrink the size of log file as it’s rarely necessary. In order to downsize the log file, try the following script on your MS-SQL Server Management Studio.

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

Reference: http://technet.microsoft.com/en-us/library/ms189493.aspx

Advertisements

Written by Justin Yoo

05/01/2013 at 21:35

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s