Temp Database overview
As the name says it is a Temporary database of SQL Server, where data resides temporarily.
Good Practices:
1.Always Keep Temp data and log files on separate disks, that too away from User and system databases.
2.Looking for RAID then don't choose RAID 0 for TEMP DB.
3.If more than 1 processor is available, then for 'n' processors create 'n' data files of Temp DB. It will boost the performance of the SQL Server.
4.Do not restrict Temp Data or Log file growth.
Key Points:
1.Temp DB can't be backed up.
2.Don't create multiple log files for Temp DB.
Find the size of Temp DB data file and log file:
Use TempDB
GO
select [FileSizeMB] =convert(numeric(10,2),round(a.size/128.,2)), [UsedSpaceMB] =convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) , [UnusedSpaceMB] =convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) , [DBFileName] = a.name from sysfiles a
What if Temp DB size increases and it needs to be reduced without restarting the SQL Server:
Temp DB will be recreated when ever SQL Server is bounced and it is the known fact.
What if, we are in a situation where Temp DB needs to be cleared without a bounce.
The only option available is "DBCC SHRINKFILE" or "DBCC SHRINKDATABASE"
By using above commands we can reduce the size of TEMP Database.
Syntax: DBCC SHRINKFILE (fileid/filename,truncateonly/notruncate)
Truncateonly : What does this command do ?
"Truncateonly" command release the unused space to the DISK, hence size of the file is reduced
Notruncate: What does this command do ?
"Notruncate" command will push the data inside the file and defragment them in such a way that unused space comes to the end of the file.
If none of the options are provided as input, then SQL Server will execute with both the options in below sequence.
First with "Notruncate" option where space is made inside the file.
Following with "Truncateonly" option where space is freed up to the disk.
So, for Temp DB we can run.
dbcc shrinkfile(tempdev,truncateonly)
If the above is not returning any fruitful output then follow the next steps to find out the issue:
Find out the sessions running in TempDB and by whom and what they are executing :
SELECT * FROM sys.dm_exec_requests WHERE database_id = 2
Find out, whether there are any locks on TEMP DB.
select * from sys.dm_tran_locks where resource_database_id= 2
Find out session space usage in Temp DB
select * from sys.dm_db_session_space_usage where user_objects_alloc_page_count<> 0
Find out tables defined on Temp DB
SELECT * FROM tempdb..sys.all_objects where is_ms_shipped = 0
Issue Checkpoints explicitly in the database.
After analysing the above outputs, take a wise decision whether to kill any session or not for shrinking the Temp DB files.
==========================
Hope Temp DB information provided will be useful.
Please revert with your comments/views/suggestions.
Thanks
