Archive for October, 2012

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:


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

USE DatabaseName;
--Truncate the log by changing the database recovery model to SIMPLE 
--(If it?s not already in SIMPLE recovery model)
--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);
--Reset the database recovery model.

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 »

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

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 »