AlexMcCo (9/25/2013)
So with your 200 GB tempdb .. of course it depends what your general mdf volume.. but you definitely having problem with your code.
I'm glad you can come to such a conclusion without knowing anything about the system....
Re that 50MB installation size, I don't recall a single time I've installed SQL in the last 4 or more years that I've set TempDB that small (well, once, a SQL Express instance). 250-300 MB is usually what I'll set and that only if I have no idea at all what is going on that server. If I do, then the initial size will be based on what I know about what's going onto that instance.
Bear in mind that TempDB is used heavily by CheckDB (it stores the facts there), used by the row version store (snapshot isolation, read committed snapshot isolation, online index rebuilds, triggers), used by index rebuilds when sort_in_TempDB is specified, plus work tables and spills. That's over and above the temp table/table variable usage.
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