Shrink the SQL Server DB Log File

Posted: July 2, 2012 in SQLServer
Tags: , ,

— Check to see log size and log space used
DBCC sqlperf(logspace)

— Check to see if there are any open transactions
DBCC OPENTRAN

— Shrink the log

–The following example shrinks the DB log file to 1 MB.
–To allow the DBCC SHRINKFILE command to shrink the file, the file is first truncated by setting the database recovery model to SIMPLE.

— Ex: AdventureWorks DB
— Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE;
GO
— Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks_Log, 1);
GO
— Reset the database recovery model.
ALTER DATABASE AdventureWorks SET RECOVERY FULL;
GO

— Following command will help to see the progress of the shrink operation
SELECT percent_complete, start_time, status, command, estimated_completion_time, cpu_time, total_elapsed_time
FROM sys.dm_exec_requests

Reference:

http://stackoverflow.com/questions/646845/sql-server-2008-log-will-not-truncate-driving-me-crazy

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

http://www.calazan.com/how-to-check-the-progress-of-the-shrink-database-task-in-sql-server-2005/

http://blog.sqlauthority.com/2008/07/25/sql-server-dbcc-shrinkfile-takes-long-time-to-run/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

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