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 variablesDECLARE @SqlStatement as nvarchar(max)DECLARE @LogFileLogicalName as sysname-- Alter the database to simple recoverySET @SqlStatement = 'ALTER DATABASE ' + DB_NAME() + ' SET RECOVERY SIMPLE'EXEC ( @SqlStatement )-- Make sure it has been alteredSELECT [name], [recovery_model_desc] FROM sys.databases WHERE [name] = DB_NAME()-- Set the log file name variableSELECT @LogFileLogicalName = [Name] FROM sys.database_files WHERE type = 1-- Shrink the logfileDBCC Shrinkfile(@LogFileLogicalName, 1)-- Alter the database back to FULLSET @SqlStatement = 'ALTER DATABASE ' + DB_NAME() + ' SET RECOVERY FULL'EXEC ( @SqlStatement )-- Make sure it has been changed back to fullSET @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.
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.
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.