Microsoft KB Archive/64679

INF: Reserve More Disk Space for Master Database Syslogs File ID Number: Q64679

1.10 1.11 4.20 OS/2

Summary:

This article discusses the procedure for extending the transaction log for the master database.

You can reserve more space for the master database syslogs file by executing the following commands:

  alter database master on master =    sp_logdevice master, master (This command will allocate the amount of disk space that was allocated in the “alter database” command for the syslogs file.)   sp_helpdb master (The execution of this command will show that the data and log device fragments have been separated, and that the additional disk space has been allocated to the syslogs file.) NOTE: Be sure to remember that the master database and/or the syslogs file cannot be increased to a size any larger than the allocated disk space for the device where these files are located. 

More Information:

Example
Increase the syslogs file by 5 MB for the master database. First, execute the SP_HELPDB MASTER command to see what the state of the master database is before the disk space is increased, so this information can be used as a check point. In this case, the following table shows how the master database looks after the initial installation of the SQL server:

Device_fragments Size Usage —————- —- —–

Master 3.0 MB data and log

Next, execute the following commands to direct the 5 MB allocated for the master database to be allocated for the syslogs file:

alter database master on master = 5

sp_logdevice master, master

Then, to verify that the 5 MB was allocated for the master database syslogs file, execute the following command:

sp_helpdb master

The output from this command is as follows:

Device_fragments Size Usage —————- —- —–

Master 5.0 MB Log Only Master 3.0 MB Data Only

Additional reference words: Dumping and Loading, Optimization and tuning