SQL Server 2008 R2 Changing defaul location for query results

Whenever I run a query with a large result the C drive on my server fills up.  I have changed the location of query results under tools, options, query results, sql server, general to the desired location and restarted the database services.  New queries are still creating their temp files in the old location.  How do I enact this change?
pgreetisadaAsked:
Who is Participating?
 
pgreetisadaConnect With a Mentor Author Commented:
No answer was found.
0
 
lcohanDatabase AnalystCommented:
"Whenever I run a query with a large result the C drive on my server fills up."

what "query" you run and is the SQL Server running on your computer as well? Or you run the query directly ON the SQL Server box?

Did you checked tempdb and any other DB files location and size? Maybe tempdb or one of the transaction logs that is growing due to the "query" you run and you will need to move that file(s) as well.
0
 
pgreetisadaAuthor Commented:
A sample query is something that returns all of the records like select * from ...  It is run directly on the server from Microsoft SQL Mgmt. Studio.  The tempdb has been moved to the E drive, which is where the transaction logs are stored as well.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Anthony PerkinsCommented:
So what is left on C: that it is eating up your space?
0
 
pgreetisadaAuthor Commented:
The query is creating a .tmp file in C:documents and settings/administrator/local settings/temp/4/tmp26.tmp   It grows until the C drive is filled and then errors out.
0
 
Anthony PerkinsCommented:
So let me see if I understand, you are stating that:
When you run a query from SSMS, the query create a .tmp file?  Is that correct?  If that is the case, I have to state I have never heard of that.
0
 
pgreetisadaAuthor Commented:
I attached a file with 2 screen shots of the folder which fills up the c drive.  It will keep going if the results are big enough.
tmpsqlscreenshot.jpg
0
 
Anthony PerkinsCommented:
I am afraid I have never seen this.  Hopefully someone can step up to the plate.
0
 
pgreetisadaAuthor Commented:
No one was able to determine the cause of the behavior.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.