Microsoft SQL Server 2008 – Tip 1 – Purge Transaction Log

by Otto on November 4, 2008

We’ve been migrating some of our production databases at work to SQL 2008 and I’m finding that Microsoft changed a lot. They updated the BACKUP LOG t-sql command and removed the TRUNCATE_ONLY switch, which means quickly and easily purging the transaction log requires a bit more work. I spent a little while yesterday and wrote the following T-SQL to purge the logs.


------------------------------------------------------------------------------
-- Otto R. Radke - http://ottoradke.com
-- Info: T-SQL script to shrink a database's transaction log. Just set the
-- database name below and run the script and it will shrink the
-- transaction log.
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- Update the line below with the name of the database who's transaction
-- log you want to shrink.
------------------------------------------------------------------------------
USE YourDatabaseName
------------------------------------------------------------------------------
-- Don't change anything below this line.
------------------------------------------------------------------------------
GO
-- Declare variables
DECLARE @SqlStatement as nvarchar(max)
DECLARE @LogFileLogicalName as sysname
-- Alter the database to simple recovery
SET @SqlStatement = 'ALTER DATABASE ' + DB_NAME() + ' SET RECOVERY SIMPLE'
EXEC ( @SqlStatement )
-- Make sure it has been altered
SELECT [name], [recovery_model_desc] FROM sys.databases WHERE [name] = DB_NAME()
-- Set the log file name variable
SELECT @LogFileLogicalName = [Name] FROM sys.database_files WHERE type = 1
-- Shrink the logfile
DBCC Shrinkfile(@LogFileLogicalName, 1)
-- Alter the database back to FULL
SET @SqlStatement = 'ALTER DATABASE ' + DB_NAME() + ' SET RECOVERY FULL'
EXEC ( @SqlStatement )
-- Make sure it has been changed back to full
SET @SqlStatement = 'SELECT [name], [recovery_model_desc] FROM ' + DB_NAME() + '.sys.databases WHERE [name] = ''' + DB_NAME() + ''''
EXEC ( @SqlStatement )
------------------------------------------------------------------------------

If you want a copy of the source, let me know and I’ll either update this post with the source or email it to you.

{ 24 comments }

Hector Figueroa February 23, 2009 at 10:27 am

Thank you. Your help was much faster and precise than anyone elses. Very much appreciated.

Robin March 4, 2009 at 11:19 am

Works Fine. Thank You.

Jerald Carter May 13, 2009 at 11:08 am

Excellent work!

Thank you!

Dean Johnson November 2, 2009 at 5:34 pm

Thank you! I have looked for days for this information, and I appreciate that you posted it for others.

Sumant November 5, 2009 at 4:31 am

Good work.
Thank you

harsha August 11, 2010 at 10:03 am

Hi,

Thanks for the post.

can you help me with some thing? thanks in advance

we have transaction servers from which data is replicated to report db on different server from which reports are generated. now we are planning to purge the customer.employer etc data from the transaction servers itself instead of reporting server in real time.

how can we do that?

Fritz November 3, 2010 at 1:17 pm

Awesome script. Thank you.

Danny November 8, 2010 at 6:05 pm

Fantantic, it worked even for me! First try. You are amazing.
Our company can not afford disk space and I freed up 100gig is a matter of seconds in our dev/test environments!

Danny

Drew January 26, 2011 at 7:42 pm

Otto,

Many thanks for this post & script. Saved my skin.

-Drew-

Marc April 22, 2011 at 5:23 pm

Thanks Otto.

Ignacio May 20, 2011 at 2:33 pm

Great script! Thanks

sdewa June 2, 2011 at 6:07 pm

thank you.. work nice

madhu June 9, 2011 at 10:11 pm

thanks a lot

Lim Byoung In August 10, 2011 at 5:07 pm

Great post!
thanks a lot.

acho August 22, 2011 at 2:27 am

I need some script to purge logfile daily
Thanks very much

izy October 19, 2011 at 3:57 am

nice post, but shrinking your logfiles is not a good practice.

Peter November 22, 2011 at 4:05 am

Thanks a lot! It works fine! Great job!

Lionel December 11, 2011 at 7:42 pm

thanks, this helped me

TA April 9, 2012 at 7:36 am

Thanks for the help. Saved a lot of searching.

Anoop May 1, 2012 at 10:13 am

hi OTTO, i have been working on reporting tasks for around 7 months, yesterday I recieved this error first time since I startes working, when I was generating a critical report. but I had to stop in between due to this error.
‘The transaction log for is full’

Also , It states that due to this my C drive shows very less space (around 3 mb) hence I am unable to complete my task.
This error was thrown when i was deleting around 3,51,831 records from one of my database table.

I am unable to understand how to resolve this. Is it due to less disk space in C drive or something related to transaction log ?
I am not much aware of the transaction related errors.

Could you please suggest a resolution or email me the code as you stated above. It would also help if you could explain me in simple terms as to what caused this error so that my future tasks are not affected.

highly appreciate your help.

Otto May 1, 2012 at 10:52 am

Anoop, I think the problem is due to lack of space on your C drive. If you can, reboot the server, this should free up space in your TEMPDB database, which might give you the necessary space to start cleaning up your other databases.

Transaction logs can be a real problem if you don’t have Maintenance Plans setup on your databases. I wrote this script to deal with test databases where I didn’t want to run Maintenance Plans.

Hope that helps!

Raul May 3, 2012 at 7:47 am

Cool! Yes I would like a copy of the source.
Thanks,
Raul

Wes May 22, 2012 at 9:24 am

Great script! There is one small flaw. If the database name contains a dash in it, the script should be changed to something like this…
——————————————————————————
USE YourDatabaseName
— ex: USE [my-database]
——————————————————————————
— Don’t change anything below this line.
——————————————————————————
GO
— Declare variables
DECLARE @SqlStatement as nvarchar(max)
DECLARE @LogFileLogicalName as sysname
— Alter the database to simple recovery
SET @SqlStatement = ‘ALTER DATABASE [‘ + DB_NAME() + ‘] SET RECOVERY SIMPLE’
EXEC ( @SqlStatement )
— Make sure it has been altered
SELECT [name], [recovery_model_desc] FROM sys.databases WHERE [name] = DB_NAME()
— Set the log file name variable
SELECT @LogFileLogicalName = [Name] FROM sys.database_files WHERE type = 1
— Shrink the logfile
DBCC Shrinkfile(@LogFileLogicalName, 1)
— Alter the database back to FULL
SET @SqlStatement = ‘ALTER DATABASE [‘ + DB_NAME() + ‘] SET RECOVERY FULL’
EXEC ( @SqlStatement )
— Make sure it has been changed back to full
SET @SqlStatement = ‘SELECT [name], [recovery_model_desc] FROM [‘ + DB_NAME() + ‘].sys.databases WHERE [name] = ”’ + ‘[‘ + DB_NAME() + ‘]’ + ””
EXEC ( @SqlStatement )

Mark May 26, 2012 at 7:02 pm

Great work….wish i had found your script a few hours ago LOL!

Comments on this entry are closed.

Previous post:

Next post: