Microsoft KB Archive/134464

{|
 * width="100%"|

FIX: DocErr: Recovering Master DB and Expanding MSDB Devices

 * }

Q134464

-

The information in this article applies to:


 * Microsoft SQL Server version 6.0

-

BUG# NT: 11024 (6.00)

SYMPTOMS
The intention of this article is to correct and expand on the Microsoft SQL Server version 6.0 documentation in the "Administrator's Companion," Part 5 Data Management, Chapter 12 Backing Up and Restoring, "Restoring the master Database." One of the early steps outlined in the procedure is to rebuild the master Database. You should note that the master database rebuild option found in the SQL Server Setup program will automatically recreate the MSDB database with the MSDBData (MSDB.DAT) and the MSDBLog (MSDBLOG.DAT) devices set at the default size of 2 MB each. This is different from the sizes stated in the documentation, which says that MSDBDATA will be 2 MB and MSDBLOG will be 1 MB.

The procedure for restoring the master database outlined in the documentation details how the master database can be restored by loading the database from the most recent backup. The master database backup contains the definition of the size and location for the MSDBData and MSDBLog database devices used by MSDB. You should expand MSDBData and/or MSDBLog to the size as defined in the master database backup prior to loading the backup. Failure to do so will result in SQL Server being unable to open MSDBData and/or MSDBLog, which will subsequently result in the failure to recover the MSDB database.

Another problem related to the failure of expanding these devices prior to the load of a master database dump is a failure to load a previous dump of the MSDB database. If the load of a previous backup of the MSDB database is not attempted, an error is displayed when you try to add events for SQL Executive. The specific error reported is that the MSDB database has not been recovered yet. Also, the MSDB database appears in the Server Manager window with the Database folder unavailable (greyed out).

WORKAROUND
The easiest way to resolve the problem is to repeat the steps to rebuild the Master database/MASTER.DAT. After you recreate MASTER.DAT, MSDBDATA.DAT, and MSDBLOG.DAT, start the SQL Enterprise Manager and alter the respective MSDB database devices to the appropriate sizes, then shut down SQL Server and restart it in 'single user' mode to load the Master Database dump. If this is not an option or you have made changes to the MSDB database that are not similar to the above scenarios, contact your primary support provider for assistance.

STATUS
Microsoft has confirmed this to be a problem in the documentation for Microsoft SQL Server version 6.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.0. For more information, contact your primary support provider.

MORE INFORMATION
The following errors were generated after the MSDBData device and msdb database system/default segments were expanded by 1Mb. The first error occurs as SQL Server attempts to open the MSDB.dat device:

95/07/25 10:09:10.59 kernel  udopen: File 'C:\SQL60\DATA\MSDB.DAT' is incorrect size (1024 bytes, should be 1536) 95/07/25 10:09:10.62 kernel  udactivate (primary): failed to open device C:\SQL60\DATA\MSDB.DAT for vdn 127 95/07/25 10:09:14.05 kernel  udread: Operating system error 6(The                        handle is invalid.) on device 'C:\SQL60\DATA\MSDB.DAT' (virtpage 0x7f000018).

95/07/25 10:09:14.09 spid11  Error : 840, Severity: 17, State: 2 95/07/25 10:09:14.09 spid11  Device 'MSDBData' (with physical name

'C:\SQL60\DATA\MSDB.DAT', and virtual device number 127) is not available. Please contact                        System Administrator for assistance. 95/07/25 10:09:14.10 spid11   Buffer a91dd8 from database 'msdb' has                        page number 0 in the page header and page number                        24 in the buffer header 95/07/25 10:09:14.17 spid11   Unable to proceed with the recovery of                        dbid <5> because of previous errors.  Continuing                        with the next database.

If both the MSDBLog device and the log segment are expanded, the following errors will occur:

95/07/30 07:14:07.26 kernel  udopen: File 'C:\SQL60\DATA\MSDBLOG.DAT' is incorrect size (1024 bytes, should be 1536) 95/07/30 07:14:07.26 kernel  udactivate (primary): failed to open device C:\SQL60\DATA\MSDBLOG.DAT for vdn 126

95/07/30 07:14:11.07 spid11  Recovering database 'msdb' 95/07/30 07:14:11.07 kernel  udread: Operating system error 6(The                        handle is invalid.) on device 'C:\SQL60\DATA\MSDBLOG.DAT' (virtpage                       0x7e000107). 95/07/30 07:14:11.10 spid11  Error : 840, Severity: 17, State: 2 95/07/30 07:14:11.10 spid11  Device 'MSDBLog' (with physical name                        'C:\SQL60\DATA\MSDBLOG.DAT', and virtual device                        number 126) is not available. Please contact System Administrator for assistance. 95/07/30 07:14:11.12 spid10  Recovery dbid 4 ckpt (888,22) 95/07/30 07:14:11.10 spid11  Buffer a92058 from database 'msdb' has page number 0 in the page header and page number 1287 in the buffer header 95/07/30 07:14:11.17 spid11  Error : 3414, Severity: 21, State: 1 95/07/30 07:14:11.17 spid11  Database 'msdb' (dbid 5): Recovery failed. Please contact Technical Support for further instructions. Additional query words: sql6 windows nt recovery

Keywords : kbtool

Issue type : kbbug

Technology : kbSQLServSearch kbAudDeveloper kbSQLServ600