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

ID: Q174267

------------------------------------------------------------------------- The information in this article applies to: - 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. Note that if users are dumping the MSDB database and only restoring the database, this problem will not occur. 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.


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

Last Reviewed: September 30, 1997