FIX: DocErr: Recovering Master DB and Expanding MSDB Devices

• Microsoft SQL Server, version 6.0

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.

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.