3 Ways to Find Your SQL Server tempdb Data Files

You’ve just inherited a new-to-you SQL Server. Maybe someone else set it up a couple of years ago? Maybe a vendor did the install for you? Or maybe you installed but you’ve slept since then? Or, as in my case, you’ve downloaded a SQL Server container and you’re not sure how it’s configured. Anyway, now you’d like to see if it’s configured properly. And one of the things you’d like to check is the number of SQL Server tempdb data files it has.

When Microsoft SQL Server is installed, by default one SQL Server tempdb file is defined. For many systems that may be enough. But, over time, as the database sizes grow, as the number of users increases, and type of queries grow in complexity, one tempdb file may not be able to handle the load. It may become a source for contention.

How to find your tempdb data files

Checking the number of tempdb data files is straightforward. Here are three easy ways: one graphical, one T-SQL, and one PowerShell, so you can use your tools of choice.

List tempdb data files using database properties

Perhaps the most frequently used way to check the number of tempdb data files for a SQL Server is to view the Database Properties. This can be done using either SQL Server Management Studio (SSMS) or Azure Data Studio (ADS). I’m using ADS in images below.

Drill down into the SQL Server instance, Databases, System Databases, and right click on the tempdb database. Select Properties in the pop-up menu.

The Database Properties window will open. As you can see in the image below, the SQL Server 2019 container I downloaded is configured with four tempdb data files. That’s not bad, although the size and auto growth settings are concerning.

Of course, while you’re in the Properties window you can adjust the number, sizes, etc, of the data files. Your SQL Server should be restarted afterward.

List tempdb data files using T-SQL

As with most everything you can do using point-and-click in SSMS or ADS, the same can be accomplished using Transact-SQL. Sometimes, it’s just nice to have a library of commonly used scripts on hand so you can quickly, easily, and consistently run them.

In this case, the query below can be used to check the number of tempdb data files currently defined.

SELECT  file_id, 
        type_desc, 
        name, 
        physical_name, 
        [size],
        state_desc
FROM    tempdb.sys.database_files 
WHERE   type = 0 ; 

Here are the results. Of course, you can add additional output columns to see other properties as well, such as max_size, growth, and is_percent_growth.

List tempdb data files using PowerShell and dbatools

Experienced SQL Server DBAs often make extensive use of the dbatools PowerShell module. You get all the flexibility and capabilities of PowerShell combined with the ability to administer most every aspect of your SQL Server.

In the code below, I’m using the Get-DbaDbFile command. The command is fully documented here. I’m connecting to the localhost using the -SqlInstance parameter and authenticating as sa using the -SqlCredential parameter. I will be prompted for the password each time. The -Database parameter allows me to find the datafiles for a specific database. I’m then piping the output to table format for readability.

Get-DbaDbFile -SqlInstance localhost -SqlCredential sa -Database tempdb |Format-Table

Here’s the output from the Get-DbaDbFile command.

Want to know more about your SQL Server configuration?

Here are few other posts for SQL Server configuration settings and tempdb that may be helpful.

• Is My SQL Server Configured Properly?
• How Many tempdb Data Files Should My SQL Server Have?
• Why 9 Out of 10 SQL Servers Aren’t Configured with Best Practices

[EDIT Oct 5, 2021] – Also see How to Configure SQL Server tempdb? for other tempdb configuration settings that can affect performance.

Want to work with The Sero Group?

Want to learn more about how SERO Group helps organizations take the guesswork out of managing their SQL Servers? It’s easy and there is no obligation. 

Schedule a call with us to get started.