Feeds:
Posts
Comments

Archive for the ‘Shrink’ Category

SQL Server – Transaction Log File is Full; How to Shrink the Transaction Log

Posted in Shrink, Transaction Log, tagged , , , on October 12, 2012| 2 Comments »

Here is a typical scenario every SQL Server DBA faces:  Transaction Log grows unexpectedly or becomes full!!

So, do I need to increase the Log size? Fortunately we have a better solution – SHRINK the log file!

Typically log truncation frees space in the log file for reuse by the Transaction Log thus releasing space and making it not to over grow in size. This truncation occurs automatically when:

  • Database is backed up – under Simple recovery model
  • Transaction log is backed up – under Full recover model

When the database is in simple recovery mode I usually don’t expect the T-log to be blowing out. This happened to me and I figured out this is because of increasing number of uncommitted transactions!

In this case I am running a purge job to purge data from a management data warehouse (MDW) database (about 1600+ GB) which is configured to receive data from multiple instances as part of the MDW Data Collection configuration setup. And below is what I did to overcome this problem.

First I checked the log size and log space used% for every database using the following DBCC command:

DBCC SQLPERF(LogSpace)

After getting to know the log size (around 900+ GB), I shrinked it using the below command to some 800+ MB

USE DatabaseName;
GO
--Truncate the log by changing the database recovery model to SIMPLE 
--(If it?s not already in SIMPLE recovery model)
ALTER DATABASE DatabaseName
SET RECOVERY SIMPLE;
GO
--Shrink the truncated log file to 1 MB 
--(However it won?t shrink a file past the size needed to store the data in the file)
DBCC SHRINKFILE (DatabaseName_Log, 1);
GO
--Reset the database recovery model.
ALTER DATABASE DatabaseName
SET RECOVERY FULL;
GO

Shrinking occurs only when at least one virtual log file (VLF) is free, which means in my case I have tons of inactive VLFs as I am able to shrink it to 800 MB from 900 GB! (amazing isn’t it?)

Please refer BOL for the options above. Also, this can be done using GUI by right clicking on database ->Tasks->Shrink->Files

Here is more information from MSDN BOL:

Read Full Post »

SQL Server – Msg 2555, Level 16, State 1, Line 1 – Error DBCC SHRINKFILE with EMPTYFILE option

Posted in Files and FileGroups, Shrink, tagged , , , on October 7, 2012| 4 Comments »

When tried to empty one of the tempdb data file using the below T-SQL command:

(My goal here is to REMOVE the file using ALTER DATABASE command followed by. For more information on removing data/log files, please refer to this post How to Best Remove Extra TempDB Data Files and Log Files)

--To empty 'tempdev12' data file
USE tempdb
GO
DBCC SHRINKFILE (tempdev12, EMPTYFILE); 
GO

I got the below error message:

DBCC SHRINKFILE: Page 9:44720 could not be moved because it is a work table page. Msg 2555, Level 16, State 1, Line 1 Cannot move all contents of file “tempdev12” to other places to complete the emptyfile operation. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I figured out this is due to the existing connections and existing temp tables. To avoid this we can either

  • Kill all the existing connections to the database OR
  • Re-start the SQL Server

In may case it is not a critical production instance,  so I just restarted the server and it worked.

For more information on how to use SHRINKFILE command please visit my previous post Transaction Log is Full – How to Shrink the Transaction Log

Read Full Post »