Cool Tips‎ > ‎Backup and Restore‎ > ‎

Backup - Cleanup Symantec Backup Exec 10d History

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Not Tested
                 SQL Server 2008R2:    Not Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:  ChillyDBA
Date:     11 Jan 2008

Description

This script is very much one to be used with care, and only in specific circumstances.
I inherited an enterprise backup solution that used Symantec Backup Exec 10d to backup SQL databases across a 100Mb link to a remote disk array.  It performed like a trained tabby cat (ie 'as and when it pleased') and it was a nightmare even to perform simple tasks such as opening a window.
Weeks of hair tearing frustration finally uncovered the problem - the backup software had never had its history archived.  This resulted in 5 years of DB and log backup history for over 50 servers being maipulated each time the management console was manipulated.
 
I located the archiving function, but this was unable to cope with the volume of data in a timely manner over the slow (ie overloaded) WAN.
 
Plan B was the only plan left and it was a risky one - to perform the archiving manually and directly on the Symanted Backup Exec database.  After some extensive and careful investigation and testing, I produced the script below.  This successfully deleted several GB of data from the history tables and resurrected the software into something usable and reliable.
 
It is likely that the underlying DB structure hasn't changed much over the years, so the script may still work.
However, please USE WITH CARE.
 

Code

CREATE PROCEDURE DBA_DeleteJobHistory
AS

SET NOCOUNT ON

DECLARE @DateTo DATETIME,
   @JobHistoryID UNIQUEIDENTIFIER,
   @Counter    INT

-- keep the batch to something achievable in an overnight period - in this case 45 days (yes, it was that bad)
SELECT     @DateTo = DATEADD(dd, -45, GETDATE())

--to minimise the chance of a table scan on an already problematic table,
--just get the next single row and hope there is an index on date
SET ROWCOUNT  1

SELECT @JobHistoryID = JobHistoryID
FROM JobHistorySummary (NOLOCK)
WHERE EndTime < @DateTo

SET ROWCOUNT  0

WHILE @JobHistoryID IS NOT NULL
BEGIN
   DELETE DBO.JobHistoryDetailInfo where JobHistoryDetailID in
       (select JobHistoryDetailID from DBO.JobHistoryDetail where JobHistoryID = @JobHistoryID)
      
   DELETE DBO.JobHistoryDetail where JobHistoryID = @JobHistoryID

   DELETE DBO.ReportJob WHERE ReportJobID in
       (Select ReportJobID from dbo.ReportJobRun Where JobHistoryID=@JobHistoryID)
      
   DELETE DBO.ReportJobRun WHERE JobHistoryID = @JobHistoryID
  
   DELETE DBO.JobHistorySummary Where JobHistoryID = @JobHistoryID

   --to minimise the chance of a table scan on an already problematic table,
   --just get the next single row and hope there is an index on date
   SET ROWCOUNT  1  

   SELECT @JobHistoryID = JobHistoryID
   FROM JobHistorySummary (NOLOCK)
   WHERE EndTime < @DateTo
   AND JobHistoryID <> @JobHistoryID
  
   IF @@ROWCOUNT = 0
       SELECT @JobHistoryID = NULL

   SET ROWCOUNT  0

END

SET NOCOUNT OFF



ċ
SYMANTEC BACKUP EXEC 10d - remove job history.sql
(2k)
Andy Hughes,
Jun 7, 2012, 10:53 AM
v.3
Comments