Shrink the Log file

 

The article contains the stored procedure that shrink the Log file successfully and no need to any down time. It shrinks the log file on the fly.

The Stored procedure is ready to use and it is my recommendation to juniors, please don't make any changes on it.

/*

   Date: 03-April-2012

   by : joydeep Das

   Note: Shrint the Log file on runtime

*/

 

 

 

IF EXISTS (SELECT *

           FROM   sysobjects

           WHERE  type = 'P'

                  AND name = 'up_RUN_LOGSHRINKER')

      BEGIN

            DROP  PROCEDURE  up_RUN_LOGSHRINKER

      END

 

GO

 

CREATE Procedure [dbo].[up_RUN_LOGSHRINKER]

      (

         @P_DBName VARCHAR(MAX) =  NULL

      )

AS

      DECLARE @sqlString       AS NVARCHAR(MAX)

      DECLARE @LogincalFileStr AS SYSNAME

      DECLARE @dbName          AS VARCHAR(MAX)

      DECLARE @dbRecovMod      AS VARCHAR(MAX)

      DECLARE @FlgStat         AS INT

     

BEGIN

     SET NOCOUNT ON;

    

     SET  @FlgStat = 0;

     SET  @sqlString = 'ALTER DATABASE ' + @P_DBName + ' SET RECOVERY SIMPLE'

     EXEC (@sqlString)

    

     SELECT @dbName=RTRIM(LTRIM([name])),

            @dbRecovMod=RTRIM(LTRIM([recovery_model_desc]))

     FROM   sys.databases WHERE [name] = DB_NAME()

           

     IF  @dbName = @P_DBName AND @dbRecovMod = 'SIMPLE'

         BEGIN 

            SELECT @LogincalFileStr = [Name] FROM sys.database_files WHERE type = 1

            IF ISNULL(@LogincalFileStr,'')<>''

               BEGIN

                   DBCC SHRINKFILE(@LogincalFileStr, 1)

                   SET @FlgStat = 1

               END

         END

     SET @sqlString = 'ALTER DATABASE ' + @P_DBName + ' SET RECOVERY FULL'

     EXEC (@sqlString)  

    

     SELECT @dbName     = RTRIM(LTRIM([name])),

            @dbRecovMod = RTRIM(LTRIM([recovery_model_desc]))

     FROM   sys.databases WHERE [name] = DB_NAME()

    

 

     IF @dbName =  @P_DBName AND @dbRecovMod = 'FULL'

        BEGIN

           SET @FlgStat = 1

        END

     ELSE

        BEGIN

           SET @FlgStat = 0

        END  

    

     IF @FlgStat=1

        BEGIN

           PRINT 'LOG-Shrink Successfull'

        END

     ELSE

        BEGIN

           PRINT 'LOG-Shrink NOT Successfull -- Contact System Admin'

        END  

END

GO

 

 

-- TO run this for Specified DB

USE my_db

EXEC up_RUN_LOGSHRINKER

     @P_DBName = 'my_db'

 

Hope you like it.

 

Posted by: MR. JOYDEEP DAS