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

Comments

Popular posts from this blog

Agent Installation on Windows Server. SQL Server (Failover Cluster) target addition in OEM 12c

Oracle 10g/11g Linux:SMS the alert logs ORA- errors generated in timestamp

Oracle 11g: Install Instant Client 11.2.0.3.0 on Linux x86_64 Server.