syshistory

[kevin]

"A history record could not be written to msdb.sysbackuphistory or
msdb.sysrestorehistory" This error message keep showing in the error
log when up the schedule jobs try to run but come back failed.

Is my msdb database corrupt is so when I ran dbcc checkdb, newalloc,
etc. it show everything was ok. Why/What is causing that error message.

[Ray Miao]

Needs more space, just expand it.


------------
kevin at 4/5/00 8:58:36 PM

"A history record could not be written to msdb.sysbackuphistory or
msdb.sysrestorehistory" This error message keep showing in the error
log when up the schedule jobs try to run but come back failed.

Is my msdb database corrupt is so when I ran dbcc checkdb, newalloc,
etc. it show everything was ok. Why/What is causing that error message.

[Craig]

Load this SP into MSDB. I run it every month. Change the -60 to -0 and run it. This will truncate your history and correct this error. Then, change it back to some value and run it regularly to prune your MSDB.

/*
** CRECLEAN.SQL
**
** This script creates a stored proc in MSDB to run sp_cleanbackupResotre_log
**
** This sp will delete all rows from MSDB related to backups and restores
** that are over 60 days old.
*/

USE MSDB
GO

/*
** Drop it if it exists.
*/
IF EXISTS ( SELECT name FROM sysobjects
WHERE type = 'P' AND name = 'sp_cleanbackupRestore_log' )
DROP PROCEDURE sp_cleanbackupRestore_log
GO

CREATE PROC sp_cleanbackupRestore_log
as
BEGIN
Declare @DeleteBeforeDate datetime
Select @DeleteBeforeDate = DATEADD (day,-60,getdate())
Delete from msdb.dbo.sysbackupdetail where backup_id
in (Select backup_id from 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 sysrestorehistory where backup_start <=
@DeleteBeforeDate)
Delete from msdb.dbo.sysRestorehistory where backup_start <= @DeleteBeforeDate
PRINT &#34;MSDB cleanup sp complete&#34;
END
GO



------------
Ray Miao at 4/6/00 8:11:21 AM

Needs more space, just expand it.


------------
kevin at 4/5/00 8:58:36 PM

&#34;A history record could not be written to msdb.sysbackuphistory or
msdb.sysrestorehistory&#34; This error message keep showing in the error
log when up the schedule jobs try to run but come back failed.

Is my msdb database corrupt is so when I ran dbcc checkdb, newalloc,
etc. it show everything was ok. Why/What is causing that error message.