Managing storage space in SQL Server is important because of the performance problems which can arise as you get closer to max capacity.

This is even more significant in the context of TempDB, since if the limit is reached here you could encounter issues across every database and app that is managed within a given infrastructure.

 

As such, keeping tables on the available space in TempDB is essential, and here is a brief overview of how this is achieved.

Image Source: Pixabay

Scripted solutions

If you are happy to make use of fully-fledged queries to find out about space usage at the current time, this is entirely possible, although it is best to read more about TempDB in an SQL Server context before you attempt any of the more advanced interactions.

A quick overview of the scale of the entire database can be achieved using the following script, although be aware that this will not provide you with any in-depth information and is simply a good starting point:

SELECT SUM(size)/128 AS [Total database size (MB)]

FROM tempdb.sys.database_files

For a more detailed look at the stats surrounding TempDB, specifically with regards to space usage and how much storage is allocated to data and log files, this script will do the trick:

SELECT instance_name AS ‘Database’,

[Data File(s) Size (KB)]/1024 AS [Data file (MB)],

[Log File(s) Size (KB)]/1024 AS [Log file (MB)],

[Log File(s) Used Size (KB)]/1024 AS [Log file space used (MB)]

FROM (SELECT * FROM sys.dm_os_performance_counters

WHERE counter_name IN

(‘Data File(s) Size (KB)’,

‘Log File(s) Size (KB)’,

‘Log File(s) Used Size (KB)’)

AND instance_name = ‘tempdb’) AS A

PIVOT

(MAX(cntr_value) FOR counter_name IN

([Data File(s) Size (KB)],

[LOG File(s) Size (KB)],

[Log File(s) Used Size (KB)])) AS B

GO

You may also want to see how much space is being monopolized by internal objects, user objects and version store within TempDB, in which case the following script will pull this up for you pronto:

SELECT

(SUM(unallocated_extent_page_count)/128) AS [Free space (MB)],

SUM(internal_object_reserved_page_count)*8 AS [Internal objects (KB)],

SUM(user_object_reserved_page_count)*8 AS [User objects (KB)],

SUM(version_store_reserved_page_count)*8 AS [Version store (KB)]

FROM sys.dm_db_file_space_usage

–database_id ‘2’ represents tempdb

WHERE database_id = 2

Temporary tables may also be responsible for hogging space in TempDB, and this upcoming script will pinpoint how much space is being reserved and used by these to indicate if any issues exist:

USE <database_name>

SELECT tb.name AS [Temporary table name],

stt.row_count AS [Number of rows],

stt.used_page_count * 8 AS [Used space (KB)],

stt.reserved_page_count * 8 AS [Reserved space (KB)] FROM tempdb.sys.partitions AS prt

INNER JOIN tempdb.sys.dm_db_partition_stats AS stt

ON prt.partition_id = stt.partition_id

AND prt.partition_number = stt.partition_number

INNER JOIN tempdb.sys.tables AS tb

ON stt.object_id = tb.object_id

ORDER BY tb.name

Automated alternatives

People who have managed to master the intricacies of administering over an SQL Server setup will likely be happy to take a more manual approach to checking up on space usage in TempDB, although in reality this is not the most efficient approach.

Luckily with the help of modern monitoring software designed specifically for this platform, you can take a more automated approach to scrutinizing how storage and other server resources are being utilized over time.

Monitoring software is particularly useful for those who are less technically experienced and skilled or just starting their DBA career, yet still want to manage an SQL Server database successfully. There is still a learning curve to overcome, but your efforts will be rewarded with better performance and a lower likelihood of space allocations being saturated.