Subject: General

Question: When trying to perform a backup via SQL Server Enterprise Manager, an error indicating a duplicate key in the sysbackuphistory table may occur.

Answer: This problem can be fixed by purging the sysbackuphistory table. Please see the Microsoft article below on how to purge this table

PSS ID Number: Q152354
Article last modified on 01-21-1998

6.5

WINDOWS
===============================
The information in this article applies to:
- Microsoft SQL Server version 6.5 -

SYMPTOMS
========

SQL Executive-based tasks fail and the following error message is returned:

Error: 1105, Severity : 17, State 2
Can't allocate space for object '%.*s' in database '%.*s' because the
'%.*s' segment is full. If you ran out of space in Syslogs, dump the
transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to
increase the size of the segment.

The SQL Executive-based tasks that might fail include:

1. Scheduled dumps and restores.
2. Tasks set up for replication.
3. Alerts.
4. Scheduled Web tasks.

CAUSE
=====

The MSDB database is full.

WORKAROUND
==========

In SQL Server 6.5, the following tables have been added to the MSDB
database to aid in maintaining backup information:

sysbackupdetail : Specifies a summary of the devices used to backup (dump).
sysbackuphistory : Specifies a summary of each backup operation (dump).
sysrestoredetail : Specifies a summary of the devices used to restore (load).
sysrestorehistory : Specifies a summary of each restore operation (load).

SQL Server automatically maintains a complete online backup and restore
history in the MSDB database. This information includes who performed the
backup, when it was done, and which devices or files it is stored on.

The System Administrator has to monitor these tables to delete old entries
in these tables to make sure that MSDB database does not fill up.

The following stored procedure can be used to delete entries from these system tables in the MSDB database.

use master
go
sp_configure 'allow', 1
go
reconfigure with override
go
drop proc sp_cleanbackupRestore_log
go
create proc sp_cleanbackupRestore_log
@DeleteBeforeDate datetime
as
begin
Delete from msdb.dbo.sysbackupdetail where backup_id
in (Select backup_id from msdb.dbo.sysbackuphistory where backup_start <= @DeleteBeforeDate)
Delete from msdb.dbo.sysbackuphistory where backup_start <=
@DeleteBeforeDate
Delete from msdb.dbo.sysrestoredetail where restore_id
in (Select restore_id from msdb.dbo.sysrestorehistory where backup_start <= @DeleteBeforeDate)
Delete from msdb.dbo.sysRestorehistory where backup_start <=
@DeleteBeforeDate
end
go
sp_configure 'allow', 0
go
reconfigure with override

You will then need to run the newly created stored procedure. For example, if you wanted to delete all the entries in the tables listed in the stored procedure that occured before January 2, 1997, you would run the following:
exec sp_cleanbackupRestore_log '1/2/97'