pgreetisada
asked on
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?
ASKER
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.
So what is left on C: that it is eating up your space?
ASKER
The query is creating a .tmp file in C:documents and settings/administrator/loc
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.
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.
ASKER
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
tmpsqlscreenshot.jpg
I am afraid I have never seen this. Hopefully someone can step up to the plate.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No one was able to determine the cause of the behavior.
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.