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

/***************************************************/
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
/***************************************************/

No comments:

Post a Comment