TempDB Database:-
1. TempDB database is also an important & system database. It is used to store temporary data like temporary tables, temporary stored procedures, internal objects and other temporary objects.
2. When we restart the SQL service, system automatically re-creates TempDB database.
3. Recovery Model of TempDB is also Simple.
4. TempDB database id of TempDB is 2.
5. In TempDB, not possible to add filegroup.
6. We can’t take TempDB backup or restore.
7. We can’t change database owner of TempDB. It should be sa always.
8. We can’t create snapshot of TempDB database.
9. We can’t drop TempDB database.
10. We can’t set TempDB database as OFFLINE.
Components of the Tempdb Database:
As we specified above, TempDB database is also system database which contains lots of components like system tables, system views, and system stored procedures. Sometime, database engine creates some Internal objects like temporary large object (LOB), Work files for hash operations, to store Intermediate sort results and Version stores etc.
Few essential components of master database are given:
System Tables: This database also contains lot of system tables which stores details of temporary objects like temp tables, table variables, and cursor tables etc.
System Views: This database contains system views which provide easy access of system tables.
System Stored Procedures: The Tempdb database also has system stored procedures which help DBAs to perform their tasks like sp_help, sp_rename, and sp_helpindex etc.
Functions of the Tempdb Database:
TempDB is a critical database which helps to manage temporary objects in SQL Server.
Few critical functions of the Tempdb database are given below:
1. Storing Temporary Objects
2. Supporting Snapshot Isolation
3. Managing Transactions
4. Supporting Online Index Operations
5. Supporting Sorting and Hashing
Conclusion:
As we aware, Tempdb is a very important database in SQL Server, so always configure TempDB properly to get good performance.
FAQs
Q. What is tempdb in SQL Server?
A. TempDB database is also an important & system database.
Q. Why tempdb is used in SQL Server?
A. TempDB is used to store temporary data like temporary tables, temporary stored procedures, internal objects and other temporary objects.
Q. When is TempDB used in SQL Server?
A. System uses TempDB to store temporary data like temporary tables, temporary stored procedures, internal objects and other temporary objects.
Q. Can we take a backup of tempdb in SQL Server?
A. We can’t take TempDB backup or restore.
Q. How can you increase the size of tempdb in SQL Server?
A. Right click on the TempDB, Select Properties, go to Files & change the size (MS) as per requirement.
We can use below command also to increase size of TempDB.
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE(NAME=N'tempdev',SIZE = 10240KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE(NAME = N'templog', SIZE = 10240KB )
GO
Q. How do I restore tempdb in an SQL Server?
A. We can’t take TempDB backup or restore TempDB database.
Q. What is the use of tempdb in an SQL Server?
A. System needs some space to store data/result during the execution of user query / operation. Generally system store temporary data like temp. tables, temp. SP, internal objects and other temp. objects etc.
Q. How do you delete tempdb files in SQL Server?
A. If the SQL service is running, system will not allow to delete any file of TempDB database. To delete any TempDB file, need to stop SQL service & change system configuration for TempDB.
Q. How do you free up tempdb space in SQL Server? / Tempdb drive is full in SQL Server? / If tempdb is full what to do in SQL Server? / How to clear tempdb in SQL Server? / How to shrink tempdb in SQL Server? / What to do when tempdb is full in SQL Server?
A. To Free up the TempDB space, follow below steps(Before running on Prod environment, check at Test or Dev environment or discuss with Sr. DBA):-
1. Check whether any bulky query is running or not. If running, check with application team to stop or restart that process.
2. Still the issue is available:-If not, then connect with Windows Team & try to get more free space on the drive which has TempDB data & log file.
3. Still the issue is available:-If not possible to add more space, try to shrink TempDB after running CHECKPOINT in TempDB.
CHECKPOINT
go
DBCC FREEPROCCACHE & then
go
USE [tempdb]
GO
--To Shrink Data File
DBCC SHRINKFILE (N'tempdev' , 0, TRUNCATEONLY)
GO
--To Shrink Log File
DBCC SHRINKFILE (N'templog' , 0, TRUNCATEONLY)
GO
GO
4. Still the issue is available, take approval from application owner/server owner to reboot the server. Schedule the change. Perform Pre-health check, take help of Windows Team to reboot the server, and then Post-health check
Q. What is the purpose of tempdb in SQL Server?
A. TempDB provides the space to store temporary data like temporary tables, temporary stored procedures, internal objects and other temporary objects.
Q. What should be the initial size of tempdb in SQL Server?
A. The number of tempdb files & the size depend on the multiple factors like server configuration, load from application etc.
Q. Where to find tempdb in SQL Server? / Where is tempdb located in SQL Server?
A. To find TempDB, Just follow below steps:-
1. Connect with your SQL Server Instance
2. Expand Instance
3. Expand Database
4. Expand System Databases
5. Here we'll get TempDB
To get physical location of TempDB files, use below query to get more details
Use Master
go
SELECT * FROM sys.master_files WHERE database_id=2
Q. How to shrink tempdb in SQL Server without restarting the server?
A. Just refer above question which has all steps. For this, refer only step 1, 2 and 3.
Q. How to change initial size of tempdb in SQL Server?
A. To change Initial size of TempDB, we have two options:
1. Specify at the SQL server installation
2. Specify in the property of TempDB (If SQL server is already installed).
Q. How to rebuild tempdb in SQL Server?
A. Just reboot the server. System will automatically rebuild/recreate the TempDB database. But before restarting the SQL service, check with your Sr. DBA & application owner.
Q. How to check size of tempdb in SQL Server?
A. Use below command to check size of TempDB or any other database
Use TempDB
go
sp_spaceused
Q. How to restore tempdb in SQL Server?
A. Restore TempDB is not allowed.
Q. When tempdb is full in SQL Server? / Why tempdb size increases in SQL Server?
A. TempDB generally full when bulk query is running or Index maintenance job is running etc.
Q. Can we shrink tempdb in SQL Server?
A. Yes, we can shrink tempdb.
Q. Can we take backup of tempdb in SQL Server?
A. No, We can't.
Q. Can we shrink tempdb data file in SQL Server?
A. Yes, we can shrink tempdb.
Q. How can I tell what is consuming tempdb space in SQL Server? /
How to check what is consuming tempdb space in SQL Server?
A. Use below query to get the details of TempDB usage:
SELECT @@ServerName as ServerName, DB_Name() AS DBName,
(SUM(fsu.unallocated_extent_page_count)*1.0/128) AS [AvailableFreeSpace(MB)]
,(SUM(fsu.version_store_reserved_page_count)*1.0/128) AS [UsedByVersionStore(MB)]
,(SUM(fsu.internal_object_reserved_page_count)*1.0/128) AS [UsedByInternalObjects(MB)]
,(SUM(fsu.user_object_reserved_page_count)*1.0/128) AS [UsedUserObjects(MB)]
FROM tempdb.sys.dm_db_file_space_usage fsu;
Q. Why TempDB is growing in SQL Server?
A. TempDB is used to store temporary objects & query results.
TempDB is the shared resource which is heavily used by all user databases.
Below are few reasons of tempdb growth in SQL Server:-
1. Version Store: In Snapshot or Read Committed Snapshot Isolation level, system uses the TempDB to store older versions of rows. In case of long-running transactions, TempDB grows very fastly.
2. Multiple Active Transactions: In case of multiple active transactions, system uses tempdb which also grows the size rapidly.
3. Sorting and Grouping: If the query is fired with ORDER BY (means result in sorted order) or GROUP BY (to get aggregated result), one of the cause to grow TempDB size.
4. Temporary Tables: Temporary tables are stored in TempDB. If we use lot of temporary tables in our queries, It also helps to grow the size of TempDB.
5. Index Operations: At the time of Index creation or rebuild online, SQL Server uses TempDB. It will grow the size of tempdb.
6. Query Spills: In case of large datasets or complex calculations, system uses TempDB & this will also increase the size of tempdb.
Q. How to resize tempdb in SQL Server?
A. To resize TempDB, use below command
ALTER DATABASE TempDB MODIFY FILE(NAME = tempdev, SIZE = 50000MB);
Q. What is tempdb contention in SQL Server?
A. Tempdb contention is a performance issue which occurs when multiple sessions try to access same tempdb resources simultaneously. It creates blocking which returns the result slowly & creates slow query performance, timeouts, deadlocks, and other issues.
Q. What should be the tempdb size in SQL Server?
A. TempDB size in any environment depends on multiple factors like current resource, load on the server, facing TempDB contention issue etc etc.
To get better performance:-
keep data & log files on separate drives.
Keep same size of all TempDB files.
The number of data files in TempDB should not be more than the number of logical processors. It means, if we have 8 logical processors, we can create 8 data files on TempDB.
Q. How to check if TempDB is full in SQL Server?
A. Use below command to check the free space on TempDB
USE tempdb
EXEC sp_spaceused
