Microsoft KB Archive/816939

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

Article ID: 816939

Article Last Modified on 11/26/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



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.  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).  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 = and name = ' '

go

update sysaltfiles

set status = 1048642

where fileid = and name = ' '

go

checkpoint

go

sp_configure 'allow updates', 0

go

reconfigure with override

go where the  and  are the file ids that correspond to the data and the log files according to the sysaltfiles table.

The  and the  are the names (the name column value of the sysaltfiles table) of the data and the log files respectively.  Restart SQL Server.  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 </li></ol>

<div class="moreinformation_section">

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 = ' ', filegrowth = <x> %)

go

alter database tempdb

modify file (name = ' ', 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.

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.

<div class="references_section">