It depends on below factors...
1. Depend on application, how it using tempDB.
2. Server Configuration like total server memory, installed
CPUs.
3. Databases Size.
4. Our DB maintenance plan.
Q - What about number of tempDB files?
There should be one TempDB data file for each thread/core/vCPU
on the instance with a maximum of 8.
Q - What about location of tempDB files?
The files can be found by
querying sys.sysfiles dmv or the file pane on the database properties window.
SELECT * FROM TempDB.sys.sysfiles;
The default logical
filenames are tempdev for data and templog for log. They can be found on disk
as tempdb.mdf and templog.ldf respectively. TempDB commonly has many data
files.
Q - Why tempDB is different from other databases?
Operations within tempdb are minimally logged so that transactions can be rolled back. tempdb is re-created every time SQL Server is
started so that the system always starts with a clean copy of the database.
Q - What is the use of tempDB?
TempDB is used for many operations, such as user-created temporary objects, internal temporary objects and version stores and
certain features like online re-indexing, multiple active record sets (MARS) and others. Since TempDB is shared across all databases
and all connections in SQL Server, it might become a point of contention if not configured correctly.
How to check space used in tempDB by different objects.
SELECT COALESCE(T1.session_id, T2.session_id) [session_id] , T1.request_id ,
COALESCE(T1.database_id, T2.database_id) [database_id],
COALESCE(T1.[Total Allocation User
Objects], 0)
+ T2.[Total Allocation User
Objects] [Total Allocation User Objects] ,
COALESCE(T1.[Net Allocation User
Objects], 0)
+ T2.[Net Allocation User
Objects] [Net Allocation User Objects] ,
COALESCE(T1.[Total Allocation
Internal Objects], 0)
+ T2.[Total Allocation
Internal Objects] [Total Allocation Internal Objects] ,
COALESCE(T1.[Net Allocation
Internal Objects], 0)
+ T2.[Net Allocation
Internal Objects] [Net Allocation Internal Objects] ,
COALESCE(T1.[Total Allocation], 0) + T2.[Total Allocation]
[Total Allocation] ,
COALESCE(T1.[Net Allocation], 0) + T2.[Net Allocation] [Net
Allocation] ,
COALESCE(T1.[Query Text], T2.[Query Text]) [Query Text]
FROM ( SELECT TS.session_id ,
TS.request_id ,
TS.database_id ,
CAST(TS.user_objects_alloc_page_count
/ 128 AS DECIMAL(15,
2)) [Total Allocation
User Objects] ,
CAST(( TS.user_objects_alloc_page_count
- TS.user_objects_dealloc_page_count
) / 128 AS DECIMAL(15,
2)) [Net Allocation User
Objects] ,
CAST(TS.internal_objects_alloc_page_count
/ 128 AS DECIMAL(15,
2)) [Total Allocation
Internal Objects] ,
CAST(( TS.internal_objects_alloc_page_count
- TS.internal_objects_dealloc_page_count
) / 128 AS DECIMAL(15,
2)) [Net Allocation
Internal Objects] ,
CAST(( TS.user_objects_alloc_page_count
+
internal_objects_alloc_page_count ) / 128 AS DECIMAL(15,
2)) [Total Allocation] ,
CAST(( TS.user_objects_alloc_page_count
+ TS.internal_objects_alloc_page_count
- TS.internal_objects_dealloc_page_count
- TS.user_objects_dealloc_page_count
) / 128 AS DECIMAL(15,
2)) [Net Allocation] ,
T.text [Query Text]
FROM sys.dm_db_task_space_usage TS
INNER JOIN sys.dm_exec_requests ER ON ER.request_id = TS.request_id
AND ER.session_id = TS.session_id
OUTER APPLY sys.dm_exec_sql_text(ER.sql_handle) T
) T1
RIGHT JOIN ( SELECT SS.session_id ,
SS.database_id ,
CAST(SS.user_objects_alloc_page_count
/ 128 AS DECIMAL(15,
2)) [Total Allocation
User Objects] ,
CAST(( SS.user_objects_alloc_page_count
- SS.user_objects_dealloc_page_count
)
/ 128 AS DECIMAL(15, 2)) [Net Allocation User Objects] ,
CAST(SS.internal_objects_alloc_page_count
/ 128 AS DECIMAL(15,
2)) [Total Allocation
Internal Objects] ,
CAST(( SS.internal_objects_alloc_page_count
- SS.internal_objects_dealloc_page_count
)
/ 128 AS DECIMAL(15, 2)) [Net Allocation
Internal Objects] ,
CAST(( SS.user_objects_alloc_page_count
+
internal_objects_alloc_page_count ) / 128 AS DECIMAL(15,
2)) [Total Allocation] ,
CAST(( SS.user_objects_alloc_page_count
+ SS.internal_objects_alloc_page_count
- SS.internal_objects_dealloc_page_count
- SS.user_objects_dealloc_page_count
)
/ 128 AS DECIMAL(15, 2)) [Net Allocation] ,
T.text [Query Text]
FROM sys.dm_db_session_space_usage SS
LEFT JOIN sys.dm_exec_connections CN ON CN.session_id = SS.session_id
OUTER APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) T
) T2 ON T1.session_id = T2.session_id
Q - Why we need to configure only 8 tempDB data files if we have
host on VM?
The following table lists the initial
configuration values of the tempdb data and log
files in SQL Server. The values are based on the defaults for the model database.
The sizes of these files might vary slightly for different editions of SQL
Server.
The number of secondary data files depends on
the number of (logical) processors on the machine. As a general rule, if the number
of logical processors is less than or equal to eight, use the same number of
data files as logical processors. If the number of logical processors is
greater than eight, use eight data files. Then if contention continues,
increase the number of data files by multiples of four until the contention
decreases to acceptable levels, or make changes to the workload/code.
Q - What to do if tempDB get full?
· Firstly, we have
to check the space of all data files by using below command: -
Tempdb Data File Full:
Use tempdb
Go
sp_spaceused
OR
SELECT SUM(unallocated_extent_page_count) AS [free pages], (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;
Then find out which transaction is occupying more space in Tempdb and
troubleshoot by using below commands.
Check active transactions: -
select * from sys.sysprocesses where dbid = 2
OR
select * from sys.dm_exec_requests where
session_id > 50
OR
Check open transactions through:-
DBCC OPENTRAN
If find any open transaction due to Tempdb getting full so contact
with the respective team and inform them about the transaction. Please don’t
kill any transaction without the approval of application team.
· Try to shrink the
Data File of Tempdb (if no OPEN transactions are in progress).
· Run below three
commands after approval. Because there is a risk involved with this, it’s
mention below with commands.
1.
DBCC FREESYSTEMCACHE('ALL'): -
This
command will clear all cached objects, including not only internal objects, but
also cached query plans.
2.
DBCC FREESYSTEMCACHE ('tempdb'): -
This
command will clear the cache for tempdb
3.
DBCC FREESYSTEMCACHE ('Temporary
Tables & Table Variables'):-
· This command will
clear all the temp table and variables Increase the Size of data file if it’s
restricted or Enable auto growth in MB’s.
· If space is not
available in the same drive, then add one more NDF file to another drive where
we have space. And when issue will resolve remove that NDF file. Because as per
Microsoft recommendation our Tempdb data files should be as per number of
Processor.
· The last Option
is restart the instance after approval. And it will reset the Tempdb size to
last stated value in sys.databases.
TempDB Log File Full
· Firstly verify
the current size of log file through:-
DBCC sqlperf(logspace)
· Perform Shrink
operation on the log file of tempdb for any free space.
· Verify if Log
File is awaiting any operation through: -
select name,log_reuse_wait_desc
from sys.databases
Then verify the LOG_REUSE_WAIT_DESC and check the reason
of the WAIT. Multiple states are possible and respective action should be taken
based on the state.
· Find the
transaction which is occupying maximum amount in Tempdb and kill that
transaction after approval.
Check active transactions: -
select * from sys.sysprocesses where dbid = 2
OR
select * from sys.dm_exec_requests where
session_id > 50
OR
Check open transactions through: -
DBCC OPENTRAN
Run below three commands after approval. Because there is a risk
involved with this, it’s mention below with commands.
1.
DBCC FREESYSTEMCACHE('ALL'): -
This command will clear all cached objects, including not only
internal objects, but also cached query plans.
2.
DBCC FREESYSTEMCACHE ('tempdb'): -
This command will clear the cache for tempdb
3.
DBCC FREESYSTEMCACHE ('Temporary
Tables & Table Variables'): -
This command will clear all the temp table and variables.
· After free the
cache then perform Shrink operation again.
· Strictly in SQL
Server 2005 and lower versions we can run below mention command for truncate
the log AFTER APROVAL.
· Add another log
file in same drive or different drive as per space availability. When issue
will resolve then remove that LDF file. Because as per Microsoft log file
should not be more than one.
backup log
Tempdb to disk=N’d:\Tempdb.trn' WITH
TRUNCATE_ONLY
Move
the Tempdb files from one location to another, but it requires instance restart
(Need Approval)
· The last Option
is restarting the instance after Approval.
