Daily Shrink Database Log Script



1.Create Procedure.Procedure name is Daily_Shrink_Log


USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE procedure Daily_Shrink_Log
as
DECLARE @DB_Name nvarchar(255)
DECLARE @DB_LogFileName nvarchar(255)


DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name  not IN ('master','model','msdb','tempdb')

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @DB_Name

WHILE @@FETCH_STATUS = 0
BEGIN

SET @DB_LogFileName = @DB_Name +'_'+'log'   


EXEC
(
'USE ['+@DB_Name+']; '+
'DBCC SHRINKFILE( '''+@DB_LogFileName+''', 2)'
)
     
       FETCH NEXT FROM db_cursor INTO @DB_Name
END

CLOSE db_cursor

DEALLOCATE db_cursor

2. Execute normally like EXEC Daily_DB_LOG_SHRINK

No comments:

Post a Comment

Dear User,

Thank you very much for your kind response