Database dumps and restore may fill up MSDB database

This article was previously published under Q152354
This article has been archived. It is offered "as is" and will no longer be updated.
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.
The MSDB database is full.
In SQL Server 6.5, the following tables have been added to the MSDBdatabase 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 restorehistory in the MSDB database. This information includes who performed thebackup, when it was done, and which devices or files it is stored on.

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

The following stored procedure can be used to delete entries from thesesystem tables in the MSDB database.
use mastergosp_configure 'allow', 1goreconfigure with overridegodrop 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   endgosp_configure 'allow', 0goreconfigure 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'				
If you wish to automate the code, you can use something similar to the following:
declare @DeleteBeforeDate datetime-- Modify the second parameter as necessary.-- It is currently set to delete anything older than 60  @DeleteBeforeDate = DATEADD(day, -60, getdate())select  @DeleteBeforeDate   exec sp_cleanbackupRestore_log  @DeleteBeforeDate				
NOTE: If you receive an 1105 for object 'syslogs' please see the following article in the Microsoft Knowledge Base: 110139 - INF: Causes of SQL Transaction Log Filling Up.

Article ID: 152354 - Last Review: 10/26/2013 13:15:00 - Revision: 5.0

Microsoft SQL Server 6.5 Standard Edition

  • kbnosurvey kbarchive kbprb KB152354