October 24, 2013 at 6:19 am
Hi SQL Masters,
Greetings!
I would like to ask regarding the best practice in handling TEMPDB issue without restarting the SQL Service.
Do you have any idea?
Thank you and Best Regards,
Tsinelas
October 24, 2013 at 7:13 am
Could you be more specific? Handling what about TempDB?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 24, 2013 at 8:10 am
I'm with Gail. I can't think of anything that would lead me to regularly restart the instance because of something within TEMPDB.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 24, 2013 at 8:15 am
Bar a few odd memory leaks I've seen in the past, there's not much that would have me regularly restarting SQL at all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 25, 2013 at 9:01 am
Tsinelas (10/24/2013)
Hi SQL Masters,Greetings!
I would like to ask regarding the best practice in handling TEMPDB issue without restarting the SQL Service.
Do you have any idea?
One of my least favorite issues with SQL Server is that if your tempdb is too large and you need to shrink it, you have to make sure your tempdb is not in use before you can resize it or else you stand a chance of getting errors even leading to a corrupted tempdb (with that resulting impact on your server operations). Given how much its used, is this really that practical? Isn't it pretty much better just to restart your server, because heck, it can't be used anyways right? Seems like in this case, restarting is the safe operation, then after the restart, try to hunt down the operation thats causing it to balloon into a problem in the first place.
I don't know if 2008+ has fixed this because I can't find any mention of such at the moment. One upside is that dramatic increases in tempdb size indicate some unusual operation, so maybe we could limit its size and let that unusual operation fail? Unfortunately, I don't really know if the unusual operation would be the one that fails, or maybe it would just prevent one of the more routine operations to fail and that doesn't sound like much fun either.
Obviously I would want a big enough tempdb to allow for all the various operations that make use of it, and given the nature of its use as a workspace, that means I would allow it plenty of disk space, and I would make it such that an increase in size just doesn't result in problems, and maybe that would mean giving tempdb its own disk.
October 25, 2013 at 9:19 am
You can add more files to tempdb without restarting
Why is it that people who can't take advice always insist on giving it? - James Bond, Casino Royale
October 25, 2013 at 9:36 am
There is a lot of information available regarding TEMPDB. One of the best sources outside of SSC is snooping around TECHNET or MSDN. I found this article that may be quite helpful.
http://support.microsoft.com/kb/2154845
I have a tendency to over provision TEMPDB but I can honestly say that I have never had any issues. There have been several boxes that had issues prior to my arrival in my present job. The problems were attributed to how TEMPDB was configured. First, by default (and I have no idea why Microsoft does this) TEMPDB was found on the C: system drive. Second, the initial size of TEMPDB is no where optimal for performance.
By following some basic best practices TEMPDB no longer becomes an issue on the box.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply