Different Ways to Know Count of TempDB Data Files in SQL Server

The TempDB database is one of the most significant SQL Server system databases, and it’s used to store temporary user objects like user-defined temporary tables, temporary stored procedures, table variables, and indexes, as well as transient stored procedures, table variables, and indexes.

To allow transactions to be rolled back, operations in tempdb are logged as little as possible. Every time SQL Server is started, tempdb is recreated, ensuring that the system always starts with a fresh copy of the database. When the system is shut down, all temporary tables and stored procedures are automatically discarded, and no connections are active. Nothing is ever stored from one SQL Server session to the next in tempdb. On tempdb, backup and restore operations are not permitted.

Limitations:

On the tempdb database, the following operations are not permitted.
Filegroups cannot be added.
The database cannot be backed up or restored.
Collation cannot be changed. The server collation is used by default.
Changing the owner of the database is not possible.
Taking a backup of the database is not possible.
The database cannot be deleted.
Cannot taking part in database mirroring
The primary filegroup, principal data file, or log file cannot be removed.
Changing the name of the database or the principal filegroup is not possible.
Keeping the database offline is not possible.

Access:

In tempdb, any user can create temporary items. Unless they have been granted additional permissions, users can only access their own items. To prevent a user from accessing tempdb, the connect permission to tempdb can be revoked.

Ways to Know Count of TempDB Data Files in SQL Server:

Method 1: SQL Server Management Studio (SSMS)

Step 1: Go to Object Explorer in SQL Server Management Studio (SSMS).

Step 2: Expand TempDB under databases (System Databases).

Step 3: Right-click on it to look at its Properties.

It will take us to the next screen, where we can see the number of database files.

Method 2: sys.database_files

Another common approach is to look up the number of tempDB files in TempDB’s sys. database files.

Queries:

use tempdb
SELECT COUNT(*) TempDBFilesCount
FROM sys.database_files
GO

Output:

Another common approach is to look up the details of tempDB files in TempDB’s sys. database files.

use tempdb
SELECT file_id, type_desc, name, physical_name, state_desc
FROM sys.database_files
GO

Output:

Method 3: Error Log to Find TempDB Data Files

The number of TempDB files can also be found in the error log, but this is a less common approach.

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

Output:

Note: Above query will show the number of datafiles only.

Article Tags :

SQL

SQL-Server