Disable tempdb?

[scotward]

I was told that it may be possible to run a query without using tempdb. Is this true? If so how is it done.

 

[tlbroadbent]

Why would you want to do that? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[scotward]

That's what I asked.

There are some long running queries that are using all of the available disk space (yeilding an error: log file for temdb is full). I wanted to point temmplog.ldf to a larger disk (it only has 5GB available on the current disk), but our dba insists that it would be better to disable tempdb for these queries.

Thanks.

 

[tlbroadbent]

SQL uses tempDB for many reasons. I've never seen any way to "disable" its use. I've never known a reason to do so.

You can mitigate the problem by using permanent tables rather than temporary tables where the temp tables are explicitly created. Of course, this would just increase the size of another data and log file and you could have the same problem.

Are the large queries select or updates? Have the queries been optimized to reduce the required resources? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[scotward]

Thanks, Terry. I agree completely. The queries are just selects, but not all of the joined fields are indexed. I'm sure that has a significant impact. Considering that the purpose of the queries is to validate data in a new table (they won't likely be used again), I didn't want to invest the time and disk space to indexing all of these fields. My guess is that since there is only 5gb available for the current log file, perfomance enhancements wouldn't be enough.