Find TempDb Space Consumers in SQL Server
I often see various SQL Server DBA & professionals struggling with tempdb. As a DBA it is very common to see issues where some user processes cause tempdb data files to be 100% utilized.
In my organization, we have stability as the top priority. So, we are allowed to kill user processes causing tempdb issues whenever it happens.
To maintain this stability regarding tempdb usage, we have created a stored proc [usp_TempDbSaver] that is executed every minute. This stored procedure checks tempdb space usage, finds top sessions consuming space, and kills the top contributor session. Code for [usp_TempDbSaver] stored procedure can be obtained from my GitHub repository.
To kill top tempdb consumer session whenever tempdb usage rises beyond 80%, [usp_TempDbSaver] can be executed with the below parameter sets –
|
1 2 3 4 |
use [DBA]; GO EXEC [dbo].[usp_TempDbSaver] @data_used_pct_threshold = 80, @kill_spids = 1, @verbose = 0, @first_x_rows = 5; GI |
With 1 or 2 as @verbose parameter value, we can see the background logging working for us.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
(2023-12-25 14:19:01.077) Creating table variable @t_tempdb_consumers.. (2023-12-25 14:19:01.077) Populate table dbo.tempdb_space_usage.. (2023-12-25 14:19:01.077) Insert dbo.tempdb_space_usage.. (2023-12-25 14:19:01.080) Populate table @t_tempdb_consumers.. (2023-12-25 14:19:01.083) Validate @_thresholds_validated, and take action.. (2023-12-25 14:19:01.083) Found @_thresholds_validated to be true. (2023-12-25 14:19:01.083) Version Store Issue. (2023-12-25 14:19:01.083) version_store_mb >= 30% of data_used_mb (2023-12-25 14:19:01.083) Pick top spid (@_sql_kill) order by 'ORDER BY is_snapshot DESC, transaction_begin_time ASC' (2023-12-25 14:19:01.083) Top tempdb consumer spid (@_sql_kill) = 67 (2023-12-25 14:19:01.083) Update @t_tempdb_consumers with action_taken 'Notified DBA'. (2023-12-25 14:19:01.083) Populate table dbo.tempdb_space_consumers with top 10 session details. (2023-12-25 14:19:01.083) Purge dbo.tempdb_space_consumers with @retention_days = 15 (2023-12-25 14:19:01.087) Purge dbo.tempdb_space_usage with @retention_days = 15 |
With @verbose value 2, we get intermediate table results as well.
The procedure populates following two tables with each execution –
dbo.tempdb_space_usage– This table shows space utilization within tempdb data and log file at specific moments.dbo.tempdb_space_consumers– This table returns tempdb space consumer sessions collected for a moment if the space utilization crosses the mentioned thresholds.
Below is the session recording where I explained the inner working of the stored procedure along with a demo of all the 3 tempdb space consumption use cases. The below video was created on older version of tempdb saver, but is still valid for understanding the workings.
I hope this would be helpful to SQL Server DBAs out there like me.
Happy coding 🙂
Related Posts
About The Author
Ajay Dwivedi
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.