1

I want to run most of my queries in a way that they return with an error message if not enough tempdb available.

Is there a way to do this?

The default behaviour of waiting for tempdb to have more space. But this is useless because if other processes are consuming tempdb simultaneously they deadlock each other.

Patrick Fromberg
  • 1,313
  • 11
  • 37
  • What you describe as "default behaviour" isn't IME. I've certainly encountered errors that tempdb is full and can't auto grow before. What specifically are your queries doing to consume space in tempdb? – Martin Smith Aug 29 '16 at 20:31
  • @MartinSmith, very different queries occasionally cause the problem. The query that bothered me recently had explicit temporary tables. I introduced them because I wanted to create statistics on the temporary data which I can not do with cte's for e.g. or with table valued parameters. These statistics dramatically increased query speed. – Patrick Fromberg Aug 29 '16 at 20:42
  • Is your tempdb auto growing during this time? Do you have instant file initialisation set up? – Martin Smith Aug 29 '16 at 20:46
  • No, it is not growing. I am not a DBA and do not know what instant file initialisation configuration is about. This is why I do not want to solve the underlying problem (like I usually do) but step back instead and cowardly wait for smarter people than me to solve the problem. Note that I know that the tempdb is big enough for me most of the time. My query succeeds most of the time. – Patrick Fromberg Aug 29 '16 at 20:53
  • You need to look at the wait statistics when the problem arises to see what the actual issue is. – Martin Smith Aug 29 '16 at 21:05

0 Answers0