Resizing the tempdb file

First, Experiment: Verify the size of tempdb

1. Default size of tempdb

1.1 Setting up the model database

Set the initial size of the data file for the model database to Model_size.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/57/AB/wKioL1SiEPPwWdq1AAJgiI3Uf1c141.jpg "title=" Model.png "alt=" Wkiol1sieppwwdq1aajgii3uf1c141.jpg "/>

1.2 Restart SQL Server, check tempdb

Restart SQL Server,tempdb is rebuilt. Check the initial size of the data file for the current tempdb tempdb_size.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/57/AD/wKiom1SiEHuhWRgYAAJr7JH_Ono942.jpg "title=" Tempdb.png "alt=" Wkiom1siehuhwrgyaajr7jh_ono942.jpg "/>

conclusion : After SQL Server, tempdb_size = model_size.

2. Manually adjust tempdb

2.1 Modifying tempdb

Change the "Initial size" of the data file for tempdb to a larger numeric tempdb_large.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/57/AD/wKiom1SiEU2Q6yq2AAGQvs4iIUE493.jpg "title=" tempdb Size 1.png "alt=" Wkiom1sieu2q6yq2aagqvs4iiue493.jpg "/>

2.2 Restart SQL Server, check tempdb

The result is the same.

Conclusion : If you manually adjust tempdb, set the initial size of the data file to Tempdb_large, and after each restart of SQL Server, tempdb_size = Tempdb_large.

Scenario : In practice, if tempdb is manually set to a large "initial size", the "initial size" will not change even after you restart SQL Server.

Ii. contraction of tempdb

Manually adjust tempdb to modify the initial size of the data file to a smaller value of Tempdb_small. (TEMPDB_SAMLL less than model_size)

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/57/AB/wKioL1SiE4TCQJW0AAGAMg56nn8291.jpg "title=" tempdb Size 2.png "alt=" Wkiol1sie4tcqjw0aagamg56nn8291.jpg "/>

After restarting SQL server, the tempdb_size did decrease, but it did not decrease to tempdb_small. But tempdb_size = model_size.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/57/AD/wKiom1SiE0nTFourAAGGFjYlfn8959.jpg "title=" tempdb Size 3.png "alt=" Wkiom1sie0ntfouraaggfjylfn8959.jpg "/>

Conclusion : If you manually change the value of Tempdb_size to TEMPDB_SAMLL, after restarting SQL Server, Tempdb_size takes the maximum value of both model_size and Tempdb_small.

This article is from the "We have chased the MSSQL" blog, please be sure to keep this source http://jimshu.blog.51cto.com/3171847/1597639

Resizing the tempdb file