SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


No comments.

Leave a Comment

Please register or log in to leave a comment.