SQL Server 2008 R2 Changing defaul location for query results

Posted on 2012-04-11
Last Modified: 2012-07-16
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?
Question by:pgreetisada
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
LVL 40

Expert Comment

ID: 37834132
"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.

Author Comment

ID: 37834977
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.
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37840857
So what is left on C: that it is eating up your space?
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.


Author Comment

ID: 37853097
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.
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37853947
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.

Author Comment

ID: 37857603
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.
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37858784
I am afraid I have never seen this.  Hopefully someone can step up to the plate.

Accepted Solution

pgreetisada earned 0 total points
ID: 38175855
No answer was found.

Author Closing Comment

ID: 38189177
No one was able to determine the cause of the behavior.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql query help 15 63
Can a Trigger trigger a Trigger? 4 45
Need a mirrored QA test site 2 86
HIghlights of SSIS? 3 40
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In a recent question ( here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question