Read-Only Replicas With Filled TempDB

David Fowler explains what could cause a read-only secondary replica in an Availability Group to have its tempdb fill up:

When I have an issue with tempdb filling up the first thing that I usually do is try to figure out exactly what the space has been allocated to.

You can quickly figure out what process has the most space allocated by using a quick query against dm_db_session_space_usage.

SELECT session_id, database_id, user_objects_alloc_page_count + internal_objects_dealloc_page_count AS TotalAllocatedPages
FROM sys.dm_db_session_space_usage
ORDER BY TotalAllocatedPages DESC

But what if you can see that there aren’t any pages allocated to sessions?  What could be taking up all the space?  Well let’s have a little look and see exactly where those pages are allocated.

Click through to see David’s results and explanation.

Related Posts

When Synchronous AG Secondaries Are Out Of Sync

David Fowler explains that just because an Availability Group is set up as synchronous, doesn’t mean you can never experience data loss on failover: The primary replica is constantly monitoring the state of it’s secondaries. With the use of a continuous ping, the primary node always knows if the secondaries are up or down. It’s when SQL detects that one of it’s […]

Read More

When A Database In An AG Has Different Query Store Settings

Erin Stellato explains how we can have a discrepancy in Query Store settings between the primary and a secondary in an Availability Group: Last week there was a question on #sqlhelp on Twitter about the status of Query Store for a database in an Availability Group. I’ve written about Query Store and Availability Groups before so if […]

Read More

Categories

March 2018
MTWTFSS
« Feb Apr »
 1234
567891011
12131415161718
19202122232425
262728293031