Tue, Nov 04 2008
Posted By
Otto R. Radke at 7:53 AM
Body

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 BRW
------------------------------------------------------------------------------
-- 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.

Comments
0 total comments on this post.
Add your comment
 
Name
Email
Website
Comment
Submit
Search
About Me

Otto R. Radke is the one of the founders of iTech Northwest, a Microsoft .NET software company specializing in collaboration, communication, and integration applications and services.

More...

 

Otto R. Radke ottoradke.com and all content copyright © 1969-2008 by Otto R. Radke, unless otherwise noted.
This work is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License.

Top of Page