Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.