Shrink DB for SQL 2008

Problem
There are times when a SQL Server transaction log file grows so large that it has to be shrunk back to a more reasonable size.  Before shrinking the log file it is necessary to remove the recent transaction log entries from the log.  That use to be done with the BACKUP LOG.... WITH TRUNCATE_ONLY command.  The TRUNCATE_ONLY option was removed in SQL Server 2008.  How do you remove the log entries so the transaction log file can be shrunk?

While Executing this query an stored procedure named as ns_shrink_db_log will be created in master database. It is one time work.

For Shrink database you should be execute the Stored Procedure only (ns_shrink_db_log)

EXEC dbo.ns_shrink_db_log

Here's the code: Copy and  Paste into the SQL Query Analyser

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[ns_shrink_db_log]    @db_name SYSNAME = NULL   , @target_size_mb INT = 2   , @backup_location NVARCHAR(200) = NULL   , @backup_file_name NVARCHAR(200) = NULL   , @maximum_attempts INT = 10    /* Shrinks the log file of @db_name to the @target_size_mb * exec [dbo].[ns_shrink_db_log] 'scratch', 2, 'c:\temp\'              , 'scratch_shirnk_backup', 4 ************************************************************/ AS SET NOCOUNT ON SELECT @db_name = COALESCE(@db_name, DB_NAME())      DECLARE @logical_log_file_name SYSNAME,         @backup_log_sql NVARCHAR(MAX),         @shrink_sql NVARCHAR(MAX),         @checkpoint_sql NVARCHAR(MAX),         @db_id INT = DB_ID (@db_name),         @start_size_mb INT,         @final_size_mb INT,         @attempts INT = 0,         @recovery_model INT,         @recovery_model_desc SYSNAME,         @rc INT = 0 -- return code SELECT @logical_log_file_name = name,        @start_size_mb = size / 128    FROM MASTER..sysaltfiles    WHERE dbid=@db_id AND  fileid=2    SELECT @recovery_model = recovery_model        , @recovery_model_desc = recovery_model_desc    FROM sys.databases    WHERE database_id=@db_id    PRINT 'Starting size of [' + @db_name + '].['             + @logical_log_file_name             + '] is '             + CONVERT(VARCHAR(20), @start_size_mb) + ' MB '             + ' recovery model = ' + @recovery_model_desc IF @start_size_mb <= @target_size_mb BEGIN    PRINT '['+@db_name+'] does not need shrinking'     END      ELSE BEGIN                    IF @recovery_model != 3         AND (@backup_file_name IS NULL OR @backup_location IS NULL) BEGIN         RAISERROR ('Null backup file location or name. aborting.', 16, 1)         SET @rc = 50000         GOTO get_out     END    WHILE @attempts < @maximum_attempts       AND @target_size_mb < (SELECT CONVERT(INT, size/128) FROM MASTER..sysaltfiles                                WHERE dbid = @db_id AND                                       name = @logical_log_file_name) -- not target       BEGIN         SET @attempts = @attempts + 1                  IF @recovery_model= 3 BEGIN            SET @checkpoint_sql = 'use ['+@db_name+']; '                                + 'checkpoint'            PRINT @checkpoint_sql            EXEC (@checkpoint_sql)             END         ELSE BEGIN            SET @backup_log_sql =  'BACKUP LOG ['+ @db_name + '] '                               + ' to disk = ''' + @backup_location                               + CASE WHEN RIGHT(RTRIM(@backup_location), 1)='\'                                     THEN '' ELSE '\' END                               + @backup_file_name                               + CONVERT(VARCHAR(10), @attempts)                              + '.trn'''            PRINT @backup_log_sql                          EXEC (@backup_log_sql) -- See if a trunc of the log shrinks it.         END                       SET @shrink_sql = 'use ['+@db_name+'];'                        + 'dbcc shrinkfile (['+@logical_log_file_name+'], '                        + CONVERT(VARCHAR(20), @target_size_mb) + ')'        EXEC (@shrink_sql)     END END SELECT @final_size_mb = size/128    FROM MASTER..sysaltfiles    WHERE dbid = @db_id AND name = @logical_log_file_name    PRINT  'Final size of [' + @db_name + '].['             + @logical_log_file_name             + '] is ' +        CONVERT(VARCHAR(20),@final_size_mb)        + ' MB'      get_out: RETURN @rc

EXEC [dbo].[ns_shrink_db_log] 'scratch', 2, 'c:\temp\', 'scratch_shrink_backup', 4