BUG: File growth value for the tempdb database is not persistent when changed from fixed increments to percentage (816939)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
SQL Server 8.0 450066

SYMPTOMS

When you change the file growth value for the tempdb database file from fixed increments to percentage and you restart SQL Server, the file growth value still appears in fixed increments (for example 80 KB).

CAUSE

This behavior may occur if the status column in the sysaltfiles table of the master database is not correctly updated to reflect the file growth percent for the tempdb database file.

WORKAROUND

To work around the problem, update the status column in the sysaltfiles table of the master database. To do so, follow these steps:

Warning: The following steps contain information about modifying system tables. SQL Server may not be stable if the system tables are updated incorrectly. Microsoft cannot guarantee the resolution of problems that occur because system tables were updated incorrectly.
  1. Make sure that the growth column in the tempdb..sysfiles and the master..sysaltfiles tables has the percent value you specified (for example x for x percent).
  2. Run the following code in Query Analyzer:
    use master
    go
    sp_configure 'allow updates', 1
    go
    reconfigure with override
    go
    update sysaltfiles
    set status = 1048578
    where fileid = <fileid1> and name = '<data filename>'
    go
    update sysaltfiles
    set status = 1048642
    where fileid = <fileid2> and name =  '<log filename>'
    go
    checkpoint
    go
    sp_configure 'allow updates', 0
    go
    reconfigure with override
    go
    where the <fileid1> and <fileid2> are the file ids that correspond to the data and the log files according to the sysaltfiles table.

    The <data file name> and the <log filename> are the names (the name column value of the sysaltfiles table) of the data and the log files respectively.
  3. Restart SQL Server.
  4. Verify the file growth value for the tempdb database files. To verify the growth value, run the following code in Query Analyzer:
    exec sp_helpdb tempdb

MORE INFORMATION

You can change the file growth value either by using Enterprise Manager or by using Query Analyzer.

Enterprise Manager

To change the file growth value in Enterprise Manager, use these steps:
  1. Open Enterprise Manager, and then expand Microsoft SQL Server.
  2. Expand the SQL Server Group where your SQL Server is registered, and then expand the name of your SQL Server.
  3. Expand Databases, right-click tempdb, and then click Properties.
  4. Click the Data Files tab.
  5. Select the data file that you must change the file growth option for.
  6. Under the File properties section, click to select the Automatically grow file check box.
  7. Click to select the By percent option, and then type the value for the file growth percentage. Click OK.
  8. To change the file growth value for the transaction log file, perform steps 1 through 3. In step 4, click the Transaction Log tab, and then perform steps 5 through 7.

Query Analyzer

To change the file growth value in Query Analyzer, run the following code:
alter database tempdb 
modify file (name = '<data filename>', filegrowth = <x> %)
go
alter database tempdb 
modify file (name = '<log filename>', filegrowth = <y> %)
go
where <data filename> and <log filename> are the names of the data and log files respectively and <x>, <y> are the percentage values that you want to specify for the file growth.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

REFERENCES

For more information about the ALTER DATABASE command, visit the following MSDN Web site:

ALTER DATABASE

Modification Type:MajorLast Reviewed:11/17/2005
Keywords:kbSysAdmin kbBug kbcode kbprb KB816939 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.