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 Algae Services
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:
Write commentsPlease do not enter spam links