“Space Usage By A Tempdb shoots up drastically in a production environment”
This is quite a common scenario in a DBA activity.A simple and clear explanation of the basics behind temdb is explained here.
Tempdb is designed to hold database related dynamic objects in it. When under execution these objects stay put in tempdb.
Tempdb is mainly used for extending memory to the sort operations, and session specific temporary objects.
How doest it grow?
Tempdb is a shell which gets expanded as per memory need, once it gets expanded it never shrinks back even though the usage is minimal than the size.So the actual size of the .mdf file that you see using
“sp_helpdb tempdb”
WILL NOT give the actual usage . To get the actual usage of the tempdb the following script can be used,
*************************************************************
Use tempdb
SELECT
a.FILEID,
CONVERT(decimal(12,2),ROUND(a.size/128.000,2)) as [FILESIZEINMB] ,
CONVERT(decimal(12,2),ROUND(fileproperty(a.name,'SpaceUsed')/128.000,2)) as
[SPACEUSEDINMB],
CONVERT(decimal(12,2),ROUND((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2))
as [FREESPACEINMB],
a.name as [DATAFILENAME],
a.FILENAME as [FILENAME]
FROM dbo.sysfiles a
ORDER BY fileid
*************************************************************
Workaround:
If the temp db drive is filling up fast , the only option is to restart the service which will reset the size of it. But this option is quite a bold one considering the criticality of the server.
DBCC OPENTRAN() can be executed to see the long running threads in the server, killing them may release huge workloads on tempdb. Even this decision holds the value of the thread being executed.
Fix:
Tempdb growth can be limited by the following steps:
- Less critical db can be set in simple recovery mode
- Huge sort operation can be done by placing the databases in SIMPLE recovery mode , where they can be changed back to FULL once the activity is completed.
- Adding more .mdf files is an option for load balancing of the tempdb.
- Capacity planning for server should be perfect with regards to tempdb
