Backup Log when it reaches threshold

,

I use this script on some (not all) databases to backup the Logs when they reach a certain level of being full.  For example, when the log is 60% full the log will be backed up.

Change the ????? values and this script is ready to go.

SET NOCOUNT ON

DECLARE @sql_command  VARCHAR(255) 
DECLARE @SpaceUsed    INT
DECLARE @DBName       VARCHAR(60)
DECLARE @Threshold    INT
SET @Threshold = ???????
SET @DBName    = '??????'

CREATE TABLE #TempForLogSpace (DBName            varchar(40),
                               LogSize_MB        int, 
                               LogSpaceUsed_pct  int, 
                               Status             int) 

SELECT @sql_command = 'dbcc sqlperf (logspace)' 

INSERT #TempForLogSpace 

EXEC (@sql_command) 

IF ((SELECT tfls.LogSpaceUsed_pct FROM #TempForLogSpace tfls WHERE DBName = @DBName) > 60)
   BEGIN
      BACKUP LOG [????] TO [???] 
             WITH  NOINIT,
             NOUNLOAD,  
             NAME = N'???????',  
             NOSKIP,  
             STATS = 10,  
             NOFORMAT 
   END

DROP TABLE #TempForLogSpace 
SET NOCOUNT OFF

Rate

Share

Share

Rate