What happens when tempdb can't grow any more?

Question

I have run into a problem where a tempdb database set to have unrestricted growth has used up all the available disk space (10x its norm). I am considering setting a maximum size limit, something big but just not all the space.

If a query pushes tempdb to the limit, I'm hoping the query that's using up the space will be killed off and free the space allowing everything else to work correctly. Is this likely to happen or will SQL Server stop completely?

Thanks for any answers.

Solution

There's a good article here on this topic (about 3/4 the way down the page). Here's an excerpt:

In this example, the query runs for 3 minutes before we hit the 200MB file size limit, as shown in Figure 25, and get an error that the filegroup is full.

At this point the query fails, obviously, as will any other queries that need to use TempDB. SQL Server is still functioning properly, but as long as the temp table #HoldAll exists, TempDB will stay filled.

Your three options, as a DBA, are to:

• Restart SQL Server.
• Try to shrink the TempDB database.
• Find the errant query and eradicate it.