Procedure To Shrink Log Files Of Specific Database

We had a requirement, where we need to shrink log file of specific database at run time. I have created this procedure to just pass DB name only when ever I want to shrink log file

Quick Download

/***************************************************/

CREATE PROCEDURE SHRINK_LOGFILE (@name varchar(8000))

AS

BEGIN

/**********************************************

Created BY  SAURABH SINHA

Date : 30 /09/2015

Description: Shrink specific database log files

Syntax: EXEC SHRINK_LOGFILE ‘DBNAME’

**********************************************/

DECLARE @DBName AS NVARCHAR(100),

          @LogFileName AS NVARCHAR(100),

          @exec_stmt nvarchar(625)

       SET NOCOUNT ON

      CREATE TABLE #logfiles

      (

          dbname NVARCHAR(100),

          filename NVARCHAR(100),

      )

    

          SELECT @exec_stmt = 'INSERT INTO #logfiles

           SELECT ''' + @Name + ''' , name FROM ' + quotename(@Name, N'[') + N'.dbo.sysfiles

           WHERE groupid = 0'

          EXECUTE (@exec_stmt)

     

      SELECT

          @DBName = dbname, @LogFileName = [filename]

      FROM

          #logfiles

     

          SELECT @exec_stmt = ' USE ' + quotename(@DBName) + N' DBCC SHRINKFILE (' + quotename(@LogFileName)+')'

          --print (@exec_stmt)

          EXECUTE (@exec_stmt)

         END

/***************************************************/