SQL SERVER – 3 Ways to Know Count of TempDB Data Files

Just other days I had an interesting performance tuning scenario while helping a client with Comprehensive Database Performance Health Check, and it was about the tempDB. Let us learn today 3 Ways to Know Count of TempDB Data Files.

TempDB Data Files

During SQL Server Performance Tuning Health Check we figured out that the performance of the server is not up to the mark because the server is struggling with the TempDB performance. This means we needed to check how TempDB is doing and how many files it has. While we were investigating we had a unique situation that every time when we clicked on the property of the database, it was crashing this is when we came up with different ways to know the count of the TempDB files.

Method 1: SQL Server Management Studio (SSMS)

Open SQL Server Management Studio (SSMS) and go to Object Explorer. Go to databases (System Databases) and expand TempDB. Next, right-click on it and check the properties for it. It will bring up the following screen where you can find the number of the database files.

Method 2: sys.database_files

This is another very popular method where we can go to the TempDB’s sys.database_files and can find the number of the tempDB files.

SELECT COUNT(*) TempDBFiles
FROM sys.database_files
WHERE type = 0
GO

Method 3: Error Log to Find TempDB Data Files

While not so popular method but you can also find the number of TempDB files from the error log as well.

EXEC sys.xp_readerrorlog 0, 1, N'The tempdb database has';

I like this method because it looks cool, every single time when I ran this in front of my client they think I know some secrets about the SQL Server. Well, honestly the real reason, I like this trick is that it does not go to any of the database objects to get the necessary data. When I run diagnosis queries, I want to make sure that I create the least amount of the load on my client’s server.

Well, that’s it for today. If you know any other trick to find TempDB Data Files, do let us know by posting a comment.

Reference: Pinal Dave (https://blog.sqlauthority.com)