Technical Article

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

Read 746 times
(13 in last 30 days)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating