PRB: Loading Transaction Log Dumps into MSDB Fails with Error 4306

• Microsoft SQL Server version 6.5

SYMPTOMS

If users are dumping the database occasionally and subsequently dumping the transaction log, the following error will be generated during the restore process when attempting to load the first transaction log in the sequence:

Msg 4306, Level 16, State 1
There was activity on database since last load, unable to load. Must restart load sequence with the load database to continue.

CAUSE

This problem is caused by the insertion of a new record in the MSDB SysRestoreHistory table immediately after the database load. This causes the timestamp in the database to be updated to a value greater than the values in the transaction log dumps, and generates the error message.

WORKAROUND

To work around this problem, perform the following steps:

1. Set the 'Read only' option on for the MSDB database to True, either by using the SQL Enterprise Manager, or from ISQL/w, as in the following example:

   
         exec sp_dboption <dbname>, 'read only', true
         go 




2. Load the MSDB database. The 'Read only' option will not allow the restore process to insert a new entry into the SysRestoreHistory table, and will leave the timestamp at the same value as when the database was dumped. At the end of the restore process, the following error message will be generated, indicating that the restore history record could not be inserted because the database is 'Read only':

   Msg 3906, Level 16, State 1
   Attempt to BEGIN TRANsaction in database 'msdb' failed because database is READ ONLY.
   
   Could not insert a backup or restore history/detail record in msdb.dbo.sysbackuphistory or sysrestorehistory. This may indicate a problem with the MSDB database. DUMP/LOAD was still successful.

   Note that the last part of the error message indicates that the dump or load was still successful.



3. Load any transaction logs for MSDB in sequential order.



4. After all of the transaction log dumps have been loaded, clear the 'Read only' option on the database.

MORE INFORMATION

As part of the dump and load facility in Microsoft SQL Server 6.5, four new tables were added to track dump and load history for each of the databases. SysBackupHistory, SysRestoreHistory, SysBackupDetail, and SysRestoreDetail were added to the system tables in the MSDB database; the information they contain is displayed in the Database Backup/Restore window of SQL Enterprise Manager. Each time a database is dumped or loaded, a new record is inserted into the appropriate table.

For more information on setting database options, see the SQL Server Books Online.

Additional query words:

Keywords : kberrmsg SSrvGen
Version : winnt:6.5
Platform : winnt
Issue type : kbprb