Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
Database dumps and restore may fill up MSDB database
Article ID: 152354 - View products that this article applies to.
This article was previously published under Q152354
SQL Executive-based tasks fail and the following error message is returned:
The SQL Executive-based tasks that might fail include:
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.
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.
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:
If you wish to automate the code, you can use something similar to the following:
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: September 27, 2004 - Revision: 4.0