You can use this query to get the sessions using tempdb:

Select 
tsu.session_id, 
(tsu.internal_objects_alloc_page_count + tsu.user_objects_alloc_page_count) * 8 AS TempDBSpaceKB 
from 
sys.dm_db_task_space_usage tsu 
WHERE 
(tsu.internal_objects_alloc_page_count + tsu.user_objects_alloc_page_count) >...

This isn't code, but you could also put a max size on tempdb so you don't need to have as much use to run out of space if you are...

I use sp_BlitzIndex which is part of Brent Ozar's Frist Responder Toolkit or you can use Glenn Berry's Missing Indexes by Index Advantage script.  Both rely on the information in...

According to Paul White (and what I heard early on when they first started meta-caching of Temp Tables), dropping the tables does NOT prevent caching of Temp Table meta-data.

https://sqlperformance.com/2017/05/sql-performance/sql-server-temporary-object-caching...

Not dropping temp tables at the end of stored procedures is one of the pre-requisites to take advantage of the tempdb improvements made to limit metadata contention...

Heh... if I were really smart, I'd just...

Not dropping temp tables at the end of stored procedures is one of the pre-requisites to take advantage of the tempdb improvements made to limit metadata contention in tempdb,...

Not dropping temp tables at the end of stored procedures is one of the pre-requisites to take advantage of the tempdb improvements made to limit metadata contention in tempdb,

I think this is a good use case for XE.  If you are on SQL 2016+ Query Store may be already doing what you want though, so you could look...

The only way to resolve this issue is to take a log backup.  The log cannot be shrunk until a backup takes place.    Once you do a log backup...

If you are migrating to an EC2 VM and you have  VPN between your on-prem network and AWS you can do log-shipping and skip the S3 bucket.

How you do it...

Can you post the table definition?

Your original post list the columns you want included in your clustered primary key and that looks like a very wide clustering key and best...

Haven't been active in quite a bit here, but hope everyone is well and stayed healthy through 2020.

Glad to see the tone of THE THREAD hasn't changed.

There are several uses:

1. If tempdb grows you can identify queries that use the most tempdb
2. You can find your most resource consuming queries based on:

   1. duration
   2. tempdb use
   3. memory...

We had a person at my old job.  She was ALWAYS on the phone.  I got curious as to why and so I discreetly "hung around"...