Outline of the Article:
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, the system automatically re-creates the TempDB database.
3. The Recovery Model of TempDB is also Simple.
4. TempDB database id of TempDB is 2.
5. In TempDB, not possible to add a filegroup.
6. We can’t take TempDB backup or restore.
7. We can’t change the database owner of TempDB. It should be sa always.
8. We can’t create a snapshot of the TempDB database.
9. We can’t drop the TempDB database.
10. We can’t set the TempDB database as OFFLINE.
Components of the
Tempdb Database:
As we specified above, the TempDB database is also a system
database that contains lots of components like system tables, system views,
and system stored procedures. Sometimes, the database engine creates some Internal objects like temporary
large objects (LOB) and work files for hash operations, to store Intermediate
sort results and Version stores, etc.
A few essential components of the master database are given:
System Tables: This
database also contains a lot of system tables that store details of temporary
objects like temp tables, table variables, cursor tables, etc.
System Views:
This database contains system views that provide easy access to system tables.
System Stored
Procedures: The Tempdb database also has system stored procedures that help
DBAs perform their tasks like sp_help, sp_rename, sp_helpindex, etc.
Functions of the
Tempdb Database:
TempDB is a critical database that helps to manage temporary objects in SQL Server.
A 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 are 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?
Ans. TempDB database is also an important & system database.
Q. Why tempdb is used in SQL Server?
Ans. 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?
Ans. The 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?
Ans. We can’t take TempDB backup or restore.
Q. How can you increase the size of tempdb in SQL Server?
Ans. Right-click on the TempDB, Select Properties, go to Files & change the size (MS) as per requirement.
We can use the below command also to increase the 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?
Ans. We can’t take TempDB backup or restore the TempDB database.
Q. What is the use of TempDB in an SQL Server?
Ans. The system needs some space to store data/results during the execution of user query/operation. Generally, the system stores temporary data like temp. tables, temp. SP, internal objects, and other temp. objects etc.
Q. How do you delete TempDB files in SQL Server?
Ans. If the SQL service is running, the system will not allow deleting any file of the TempDB database. To delete any TempDB file, need to stop SQL service & change the 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?
Ans. To Free up the TempDB space, follow the below steps(Before running on the Prod environment, check at the Test or Dev environment or discuss with Sr. DBA):-
1. Check whether any bulky query is running or not. If running, check with the application team to stop or restart that process.
2. Still the issue is available: If not, then connect with the Windows Team & try to get more free space on the drive which has TempDB data & log files.
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 still available, get approval from the application owner/server owner to reboot the server. Schedule the change. Perform a Pre-health check, take the help of the 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 multiple factors like server configuration, load from the application, etc.
Q. Where to find TempDB in SQL Server? / Where is TempDB located in SQL Server?
A. To find TempDB, Just follow the 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 the physical location of TempDB files, use the 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 the steps. For this, refer only to steps 1, 2, and 3.
Q. How to change the initial size of TempDB in SQL Server?
A. To change the Initial size of TempDB, we have two options:
1. Specify at the SQL server installation
2. Specify in the property of TempDB (If the SQL server is already installed).
Q. How to rebuild tempdb in SQL Server?
A. Just reboot the server. The 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 the size of tempdb in SQL Server?
A. Use the below command to check the 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 does tempdb size increase in SQL Server?
A. TempDB is generally full when the bulk query is running or the Index maintenance job is running etc.
Q. Can we shrink tempdb in SQL Server?
A. Yes, we can shrink tempdb.
Q. Can we take a backup of tempdb in SQL Server?
A. No, We can't.
Q. Can we shrink the 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 the SQL Server?
A. Use the 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 that is heavily used by all user databases.
Below are a few reasons for tempdb growth in SQL Server:-
1. Version Store: In Snapshot or Read Committed Snapshot Isolation level, the system uses TempDB to store older versions of rows. In the case of long-running transactions, TempDB grows very fast.
2. Multiple Active Transactions: In case of multiple active transactions, the 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 causes is to grow TempDB size.
4. Temporary Tables: Temporary tables are stored in TempDB. If we use a 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, the system uses TempDB & this will also increase the size of tempdb.
Q. How to resize tempdb in SQL Server?
A. To resize TempDB, use the below command
ALTER DATABASE TempDB MODIFY FILE(NAME = tempted, SIZE = 50000MB);
Q. What is tempdb contention in SQL Server?
A. Tempdb contention is a performance issue that occurs when multiple sessions try to access the 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 resources, load on the server, facing TempDB contention issues, etc etc.
To get better performance:-
keep data & log files on separate drives.
Keep the same size as 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 the below command to check the free space on TempDB
USE tempdb
EXEC sp_spaceused
