Find TempDb Space Consumers in SQL Server

Spread the love

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 –

With 1 or 2 as @verbose parameter value, we can see the background logging working for us.

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 🙂

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.