| Downloads |
|---|
| 39157.zip |
How can I find the size of the files in tempdb when SQL Server was last started? I know that each time I stop and start SQL Server, it drops, then recreates tempdb. So, SQL Server resets the size of the tempdb files to their original size as of the last SQL Server restart or to the size the files were last manually set to through an ALTER command. I know that performance can decrease if my tempdb files need to grow substantially during normal processing. I'd like to see how much tempdb files have grown so that I can determine whether to manually set them to a larger size.
Related: Avoid Auto-Growing Tempdb When SQL Server Starts
There isn't an obvious way to determine how much tempdb files have grown since the last time SQL Server was restarted. As much as Microsoft chastises us for directly accessing system tables, the answer to this question is an example of when you need to read system tables to get the information you seek.
I don't have space to fully discuss the sysaltfiles table located in the master database or the sysfiles table located in each database. I can say that a row exists in the master..sysaltfiles table for each file in tempdb, and a corresponding row exists in the sysfiles table within tempdb. The size column in sysaltfiles will help you determine the size of the files within tempdb when SQL Server was last started. The size column in tempdb..sysfiles shows the current size of the files. These sizes will be different if SQL Server has auto-grown the files. The size column in each table tracks the number of 8K pages assigned to the files. Therefore, the query that Listing 1 shows returns the original and current size of all files in the tempdb database.
Related: Is Tempdb Affecting Your Day-to-Day SQL Server Performance?
