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 [sp__tempdb_saver] 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 [sp__tempdb_saver] stored procedure can be obtained from my GitHub repository.
In order to kill top tempdb consumer session whenever tempdb usage rises beyond 80%, [sp__tempdb_saver] can be executed with the below parameter sets –
|
1 |
EXEC [dbo].[sp__tempdb_saver] @data_used_pct_threshold = 80, @kill_spids = 1, @verbose = 0, @first_x_rows = 5; |
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 |
(2021-06-19 08:27:14.430) Creating table variableS @tempdbspace & @tempdbusage.. (2021-06-19 08:27:14.430) Populate table @tempdbspace.. (2021-06-19 08:27:14.437) select * from @tempdbspace.. (2021-06-19 08:27:14.437) Populate table @tempdbusage. (2021-06-19 08:27:14.537) select * from @tempdbusage.. (2021-06-19 08:27:14.540) Compare @tempdbspace.[data_used_pct] with @data_used_pct_threshold (80).. (2021-06-19 08:27:14.543) Found @tempdbspace.[data_used_pct] > 80 % (2021-06-19 08:27:14.543) Not Version Store Issue. (2021-06-19 08:27:14.543) version_store_mb < 30% of data_used_mb (2021-06-19 08:27:14.543) Pick top spid (@sql_kill) order by '(ISNULL(size_bytes,0)+ISNULL(log_bytes,0)+ISNULL(log_rsvd,0)) DESC' (2021-06-19 08:27:14.547) Top tempdb consumer spid (@sql_kill) = 54 (2021-06-19 08:27:14.550) Update @tempdbusage with action_taken 'No Action'. (2021-06-19 08:27:14.550) Populate table dbo.tempdb_saver_history with top 10 session details. (2021-06-19 08:27:14.553) Purge dbo.tempdb_saver_history with @retention_days = 15 |
With @verbose value 2, we get intermediate table results as well.
At any point in time, we can verify the history of sessions found/terminated by a query table [dbo].[tempdb_saver_history].
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.
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.