Wednesday, November 25, 2009

Cleaning out Tempdb

I saw a post recently about the tempdb being overly large and causing issues for a user. The person was having issues shrinking the files and wasn’t sure what to do. Someone else responded that snapshot isolation might be an issue and I found that interesting. I hadn’t thought about tempdb being a place where snapshot isolation drops things but it does. This entry in BOL says:

“The tempdb database must have enough space for the version store.”

What this seems to imply is that if your database uses row versioning it takes up space in tempdb, which might limit your ability to shrink tempdb. There’s another entry (Row Versioning Resource Usage) that seems to confirm this in that there are possible conditions where tempdb space might not be freed.

Not sure if this is something many people encounter, especially as I don’t see many people shrinking tempdb often, but it’s a good piece of trivia to keep in mind.

No comments: