Robert Davis (2/28/2013)
ScottPletcher (2/28/2013)
So if tempdb now does need to grow for whatever reason, and it causes a SQL issue because it can't, you've guaranteed that there's no easy way to recover, since the current physical files absolutely can't be expanded?
A) How does this differ from tempdb trying to autogrow past the size of the drive and failing because you've filled up the drive?
B) Yes, there's an easy way to recover. The transaction returns an error and rolls back. Which is exactly the same thing that would happen if you auto-grew the file to use the whole drive and it ran out of space, except in my case, it would happen quicker because it wouldn't have to attempt the auto-growth before failing.
What are you expecting to happen? Really, your arguments are not logical.
I have additional space to grow, you prevent it.
So if a task unexpectedly needs, say, 50MB more of tempdb space to complete, you abend it -- perhaps after it ran for hours, so you can look forward to twice that long in rollback time -- while I let it complete.
It's 100% illogical to assume that ANY expansion of tempdb will result in a runaway condition that will fill all tempdb drives.
[Yes, I had to manage dozens of servers spread across the country, and I didn't get to do daily watch over all of them all of the time, so I made the "second/third" tier servers as self-correcting as possible. But the above situation could apply to any server any time.]
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.