Eric Rouach, Madeira Data Solutions - September 2023
Sometimes you encounter errors that seem as stubborn as a mule, like the infamous “Page could not be moved because it is a work table page” when attempting to shrink a tempdb file in SQL Server.
Understanding the Error:
This "knives fight" arises when SQL Server decides to be over-protective about its internal workspace - the work tables. These tables are like SQL Server’s personal kitchen, where it prepares the delightful dishes, or rather, processes queries. Attempting to move pages from these tables is like trying to take away a chef's sous-chef in the middle of a dish preparation!
The Exhausted tempdb Drive Scenario:
This situation commonly unfolds when the tempdb drive is seemingly running out of space. It’s like having a kitchen overflowing with dishes to prepare, but running out of counter space. Naturally, the first instinct is to create some room! But alas, SQL Server has its peculiar way of hoarding its workspace, giving rise to this culinary confrontation.
Practical Wisdom to Handle the Situation:
1. Avoid the Culinary Combat:
Avoid shrinking tempdb unless the situation is more desperate than a saltless soup. Shrinking can lead to fragmented, less-than-optimal performance, so it’s usually best to leave it be.
In case of emergency and, if possible, try to locate the tempdb consumer sessions (this script from our Madeira Toolbox might be helpful: https://github.com/MadeiraData/MadeiraToolbox/blob/master/Monitoring%20Scripts/TempDB%20Space%20Utilization%20Check.sql) and if permission is given, kill it/them.
Try shrinking the tempdb again. Executing DBCC FREEPROCCACHE might (indirectly) also help shrinking a tempdb file without restarting SQL Server.
2. Master the Art of Sizing:
Learn the art of sizing the tempdb right from the beginning! Pre-sizing tempdb can avert unplanned autogrowth and help avoid this standoff. It's like having just the right amount of ingredients before starting to cook!
3. Be a Query Chef:
Excessive tempdb utilization is often a byproduct of not-so-efficient queries. Optimizing queries is like finely chopping your veggies—it makes the whole cooking process smoother and avoids cluttering the kitchen.
4. Monitor, Monitor, Monitor:
Keep a watchful eye on tempdb usage. Use monitoring tools to identify and address issues before tempdb gets too bloated. It’s like keeping an eye on the soup pot; otherwise, you might end up with an overflowed mess!
Comments