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

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

Page updated

Google Sites

Report abuse