INF: Recovering a Full Master Database

ID: Q102077

The information in this article applies to:

SUMMARY

Users making many updates to system tables may fill their master database. The most common symptom is to receive the following message (1105 error):

Can't allocate space for object syslogs in database master because the logsegment is full. If you ran out of space in syslogs, dump the transaction log. Otherwise use ALTER DATABASE or sp_extendsegment to increase the size of the segment.

This article outlines the steps users can follow to safely recover from this condition.


MORE INFORMATION

Users can reduce the chances of filling the master database in two ways. First, no user information should be stored in the master database, it should be reserved for system level information. Second, users with SQL Server version 4.2a patch K9 or later can set the database option truncate log on checkpoint in the master database: 


   sp_dboption master,'trunc.',true

SQL Server 4.2a patch K9 can be obtained through your reseller or your usual support provider.

If a user receives an 1105 error message referencing the table syslogs in master, the response depends on several factors.

If the user has not yet shut down the system, they can simply log on as the SA user and type the following:
dump transaction master with no_log

If the user has shut down the system, and they have SQL Server version 4.2a patch K7 or later, they can restart the system and use the above procedure to dump transaction master with no_log.

Use the following procedures if the system is shutdown, SQL Server version 4.2aK7 is not available, and the system is needed before a version 4.2aK7 or later patch can be received. The procedures depend on having the script MASTER.SQL found at the bottom of this article keyed in as a file on the user system. Note: The following steps assume that MASTER.DAT and the errorlog are in their default locations, if they are not, adjust the paths accordingly.

If the user has a valid, current dump of the master database, do the following:
  1. Back up the existing system. At a minimum, back up MASTER.DAT.


  2. Bring up SQL Server in bypass recovery mode:

    sqlservr /d c:\sql\data\master.dat /e c:\sql\log\errorlog -T3607


  3. Run MASTER.SQL and print the resulting MASTER.RPT:

    isql /Usa /P<sa password> /e /imaster.sql /omaster.rpt copy master.rpt lpt1


  4. Log in as SA, dump transaction master with no_log. Shut down SQL Server.


  5. Bring up SQL Server in single user mode:

    sqlservr /d c:\sql\data\master.dat /e c:\sql\log\errorlog /m


  6. Log in as SA and load the master database from the dump file. Remember that SQL Server will shut down automatically after completing a load of master.


  7. Restart SQL Server as usual. Rerun the query from step 3 and ensure the data in the system tables matches that in the old system.


If the user does not have a valid, current dump of the master database, they will have to rebuild the master device. This is a complex process and should be done under the supervision of the site's primary support provider.
  1. Back up the existing system. At a minimum, back up MASTER.DAT and the SQL Server executable directories.


  2. Bring up SQL Server in bypass recovery mode:

    sqlservr.exe /d c:\sql\data\master.dat /e c:\sql\log\errorlog -T3607


  3. Review existing errorlog to determine "character set" (either code page 850 or 437) and the "default sort order" (write down the ID = value).
    
       code page     =
       sort order ID =


  4. Run MASTER.SQL and print resulting MASTER.RPT:

    isql /Usa /P<sa password> /e /imaster.sql /omaster.rpt copy master.rpt lpt1


  5. Shut down SQL Server.


  6. Rename the c:\sql\data\master.dat file (this should be deleted only after a successful completion of this process has been ensured).


  7. Rebuild MASTER.DAT using the following steps:

    1. Run the SQL Server setup program from the Setup Disk 1. Make sure you choose the same code page and sort order determined in step 3. Refer to Sort Order chart below if you are unsure which option to choose. You must also specify the same size, in megabytes for the MASTER.DAT file.


    2. Once Setup is complete, you will have a new MASTER.DAT.


    3. Restart SQL Server in single user mode:

      sqlservr.exe /d c:\sql\data\master.dat /e c:\sql\log\errorlog /m




  8. Using the output of the MASTER.SQL from step 4, do sp_addumpdevice's for all dump devices which existed in the old system.

    Do DISK REINIT's for all user devices. To determine the vdevno to specify for each user device, divide that device's value from the low column in sysdevices by 16777216. The low value can be seen in the print of MASTER.RPT from step 4. Determine the size by dividing the number of bytes in the .DAT file by 2048. The number of bytes in the .DAT file can be determined from a dir command listing of the directory holding the .DAT file.

    Do a DISK REFIT to rebuild the sysusages and sysdatabases tables.

    (See the "Language Reference" and the "System Administrator's Guide" for details on how to run DISK REINIT and DISK REFIT.)


  9. Do sp_addlogin's for all logins that existed in the old system. Note that the logins have to be entered in exactly the same sequence in which they existed in the old system. Any 'gaps' in the sequence of said numbers must be filled in with a dummy login until all logins have been entered.


  10. Redefine any remote logins or remote servers that were defined in the old system.


  11. Using the output of the MASTER.SQL query from step 4, issue any needed sp_configure parameters to reset the values to those of the old system.


  12. Rerun the MASTER.SQL query as in step 4. Compare the output of the new MASTER.RPT against the output of the old MASTER.RPT, ensure that the new values are the same. If there are discrepancies, contact your primary support provider to determine how they should be addressed.


  13. Reload any other databases, other than tempdb, that also reside on the MASTER.DAT device. The other exception would be the model database if it contains no user data. If there was any user data on MASTER.DAT which was not covered by a valid dump, the user will have to recreate the data.


  14. Shut down, and then restart SQL Server. Run a dbcc checkdb and a dbcc checkalloc on all the databases in the system. If these checks are successful, back up the system before letting users back on. At this point, you can also delete the old master device that was renamed in step 5.


  15. If the site was running a SQL Server patch in addition to the version installed from the Setup disk, they should now reapply the patch following the README.TXT instructions on the patch disk.


MASTER.SQL

MASTER.SQL is a script to extract data from master database system tables. This information is used after MASTER.DAT has been rebuilt to recreate login id's, and so on. Type the following as a file MASTER.SQL: 

use master
go

select sd.dbid, dbname = convert(char(10), sd.name),
       su.segmap, su.lstart, su.size, su.vstart
from sysusages su, sysdatabases sd
where su.dbid = sd.dbid
go

select low, high, status,
       devname = convert(char(13), name),
       physname = convert(char(23), phyname)
from sysdevices
go

select name, dbid, suid, mode, status, version
from sysdatabases
go

select suid, name, password
from syslogins
go

select * from sysremotelogins
go

select srvid, srvstatus, srvrname = convert(char(16),
srvname),
       netname = convert(char(16), srvnetname)
from sysservers
go

select value, parameter = convert(char(60), comment)
from sysconfigures
go

Chart of Sort Orders


ID  Character Set   Sort Order
------------------------------------------------------
30  Code Page 437   Binary
31  Code Page 437   Dictionary with Case Insensitivity
32  Code Page 437   Case Insensitivity
33  Code Page 437   Dictionary with Case Insensitivity
                                         and Uppercase Preference
34  Code Page 437   Dictionary with Case Insensitivity
                                         and Accent Insensitivity
40  Code Page 850   Binary
41  Code Page 850   Dictionary with Case Insensitivity
42  Code Page 850   Case Insensitivity
43  Code Page 850   Dictionary with Case Insensitivity
                                         and Uppercase Preference
44  Code Page 850   Dictionary with Case Insensitivity
                                         and Accent Insensitivity
49  Code Page 850   Strict Compatibility with 1.1X Case
                                         Insensitive Servers
50  ISO 8859-1      Binary
51  ISO 8859-1      Dictionary with Case Insensitivity
52  ISO 8859-1      Case Insensitivity
53  ISO 8859-1      Dictionary with Case Insensitivity
                                  and Uppercase Preference
54  ISO 8859-1      Dictionary with Case Insensitivity
                                  and Accent Insensitivity
55  Code Page 850   Alternate Dictionary with Case
                                  Sensitivity
56  Code Page 850   Alternate Dictionary with Case
                                         Insensitivity; Uppercase
Preference
57  Code Page 850   Alternate Dictionary with Case
                                        Insensitivity: Accent Insensitivity
61  Code Page 850   Alternate Dictionary with Case
                                         Insensitivity

Additional query words: 4.20 Transact-SQL 


Keywords          : kbother SSrvAdmin 
Version           : 
Platform          : OS/2 
Issue type        :

Last Reviewed: March 17, 1999