Microsoft KB Archive/102077

{|
 * width="100%"|

INF: Recovering a Full Master Database

 * }

Q102077

-

The information in this article applies to:


 * Microsoft SQL Server for OS/2, version 4.2

-

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
 * 1) Run MASTER.SQL and print the resulting MASTER.RPT:

isql /Usa /P /e /imaster.sql /omaster.rpt copy master.rpt lpt1
 * 1) Log in as SA, dump transaction master with no_log. Shut down SQL Server.
 * 2) Bring up SQL Server in single user mode:

sqlservr /d c:\sql\data\master.dat /e c:\sql\log\errorlog /m
 * 1) 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.
 * 2) 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.

 Back up the existing system. At a minimum, back up MASTER.DAT and the SQL Server executable directories. Bring up SQL Server in bypass recovery mode:

sqlservr.exe /d c:\sql\data\master.dat /e c:\sql\log\errorlog -T3607  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 =  Run MASTER.SQL and print resulting MASTER.RPT:

isql /Usa /P /e /imaster.sql /omaster.rpt copy master.rpt lpt1 Shut down SQL Server. Rename the c:\sql\data\master.dat file (this should be deleted only after a successful completion of this process has been ensured). Rebuild MASTER.DAT using the following steps:

<ol style="list-style-type: lower-alpha;"> 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.</li> Once Setup is complete, you will have a new MASTER.DAT.</li> Restart SQL Server in single user mode:

sqlservr.exe /d c:\sql\data\master.dat /e c:\sql\log\errorlog /m</li></ol> </li> 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.)</li> 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.</li> Redefine any remote logins or remote servers that were defined in the old system.</li> 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.</li> 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.</li> 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.</li> 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.</li> 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.</li></ol>

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:

<pre class="CODESAMP">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
<pre class="FIXEDTEXT">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

Issue type :

Technology : kbSQLServSearch kbAudDeveloper kbZNotKeyword3 kbSQLServ420OS2