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

New(ish) VLF Status: 4

Paul Randal points out a new VLF status which can appear if you’re using an Availability Group: At least since I started working on the SQL Server team (just after 7.0 shipped) and since then there have only been two VLF status codes: 0 = the VLF is not active (i.e. it can be (re)activated […]

Read More

When Secondary Nodes Affect Primaries

Dmitri Korotkevitch shows that a readable secondary in an Availability Group can negatively impact the primary: A long time ago in a galaxy far, far away, I had to troubleshoot interesting performance issue in SQL Server. Suddenly, the CPU load on the server started to climb up. Nothing changed in terms of workload. The system […]

Read More

Categories

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