RSS

Five Seconds Transaction Log Shrink

12 Jul

This is somewhat of a quick or lazy way to truncate and shrink the transaction log because all you have to do is replace the database name and change your size requirements. It’s not the cleanest or best approach I admit, but sometimes I find myself on a development or staging server and I need some space quickly. There is no requirement on the server to preserve historic data, and since it is not highly transactional I don’t care what is on the log file to begin with. This happens often when running data aggregations in our DataMart environment where log files can grow up to 100GB+ with staging data that is not useful after the process is complete.

Please remember to use this at your own risk and I would recommend not using it at all if you do not fully understand the implications of truncating and shrinking log files. Remember, shrinking the transaction log can cause performance issues so I would not recommend doing this on a production server.

Here is the code, once you paste it into Management Studio remember to change the [DatabaseName] text to the database you would like to do maintenance on; there are three places.


/*Replace [DatabaseName] with the name of your database*/

USE [DatabaseName]

DECLARE @logfilename varchar(max)
DECLARE @size INT
---Change the size to the desired size you would like
---Size is in MB
SET @size = 1024

SELECT @logfilename = name
FROM sys.database_files
WHERE TYPE =1

PRINT @logfilename

/*The actual shrinking will happen below*/
/*Setting to SIMPLE Recovery mode guarantees that the log will be truncated so that you can shrink it*/
ALTER DATABASE [DatabaseName] SET RECOVERY SIMPLE

DBCC SHRINKFILE (@logfilename,@size)

/*Only use this code if your database was set to FULL Recovery Model to start with or if you would like to change it to that*/
ALTER DATABASE [DatabaseName] SET RECOVERY FULL

Advertisements
 

Tags: , , , ,

5 responses to “Five Seconds Transaction Log Shrink

  1. Marc Jellinek

    September 9, 2012 at 9:26 PM

    You’ve accomplished two things: shrinking your transaction log… and destroying your ability to restore from full + transaction log backups (you’ve broken the recovery chain). I would recommend STRONGLY against putting this into production

     
    • Ayman El-Ghazali

      September 9, 2012 at 9:37 PM

      Thanks Marc for reiterating my point about not doing this in production. I should probably bold and highlight that part of the post so no one misses it.

       
    • PS

      February 21, 2013 at 10:20 AM

      Can I know then what’s the best way to handle Transaction Log?

       

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

 
%d bloggers like this: