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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Anthony PerkinsCommented:
So what is left on C: that it is eating up your space?
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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 answer was found.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pgreetisadaAuthor Commented:
No one was able to determine the cause of the behavior.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.