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 causing tempdb data files to be 100% utilized.
In my organization, we have stability as top priority. So, we are allowed to kill user processes causing tempdb issue 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 below parameter sets –
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 backgroup logging working for us.
(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 of time, we can verify history of sessions found/terminated by query table
Below is the session recording where I explained inner working of the stored procedure along with 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 🙂