Question
From time to time, my tempdb database grows so large that it runs out of disk space, which causes the connection that is using the tempdb to die. The tempdb can get as large as almost 6 GB, and the server’s performance really suffers during this process, until, of course, when the connection is killed. What causes this and how can I prevent it?
Answer
I have seen this before, and it can almost always be traced back to a poorly designed query. The tempdb database can be used directly via Transact-SQL, for example, as when temp tables are used; or it can be used indirectly, like it when ORDER BYs or GROUP BYs are run as part of a query.
Whenever I see this problem, I first identify the query that is causing the problem and see what is going on. This can be easy if a user reports the problem and you know what was happening when the query failed. If the user is not helpful, then you can use Profiler to capture the data and analyze it.
When it comes down to it, the reason the tempdb fills up is because the query is returning way too much data, and you need to find out why and fix it. Often, it is because the query allows a user to specify one or more criteria (in the WHERE clause), and in this particular instance, the WHERE clause was not specific enough, and way too much data was returned.
In some cases, you may not be able to easily control the queries that hit your database. For example, perhaps you allow Excel or Access users to query SQL Server data. In this case, you can’t prevent users from writing bad queries. In this case, what I do is to limit size of the tempdb, instead of letting it grow automatically (which is the default setting for tempdb). This way, when a bad query runs, it will die much sooner (because enough space can’t be accessed, which will help to reduce the server’s stress when running such a bad query. Sure, this might make some users a little unhappy when their queries fail, but a bad query should not be allowed to run in the first place.
In fact, it is a good idea to pre-size the tempdb database anyway, so that when SQL Server is restarted, it will automatically be set at a specific size. This way, when the tempdb really does need to be used, time doesn’t have to be wasted as the tempdb has to autogrow.
Predicting the ideal size for the tempdb is not easy. Generally, I take a guess, and then watch it, seeing how good my guess is. And if I am wrong, then I will make the necessary adjustments.
]]>