C:\ filling up "SELECT *"

Question

Post reply

C:\ filling up "SELECT *"

gk-411903

Hall of Fame

Points: 3801
More actions
October 29, 2009 at 12:30 pm

#227560

Hello Experts
When "select * from table" is issued in MgmtStudio , the c:\ drive seems to be filling up.
How do make the query use swap / temp space in some drive other than C:? The temp db is not having its files on C: drive.
Is there way i can restrict a particular Windows user account from not occupying more than certain amout of memory?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Are the results of the query being written to a file? That could be the issue.

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 2

the resultset of you statement is being written to your grid of text results work area.

That may indeed fill up you c drive it it is a very large set of data.

I don't think you can actually restrict it, unless you configure it in SSMS.

I don't have SSMS right now, check the options tab for the query results.

I don't know it by heart, but maybe there is a restriction option for the number of rows returned.

Off course if you use the "SET ROWCOUNT 10 " or use a top clause to restrict the number of rows returned this can be avoided.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 3

SQL Server Management Studio needs to put the results of your query somewhere, so it can be displayed. Performing a SELECT everything type of query on a large table is going to fill up the temporary file that SSMS is using to hold the results.

That temporary file is created in your temp folder. You can modify this behavior by modifying the TMP and TEMP variables for the user running SSMS. Change that to a different drive and it will fill up that drive.

Now, the real question is what are you going to do with all of that data in SSMS?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Jeffrey Williams-493691 (10/29/2009)
SQL Server Management Studio needs to put the results of your query somewhere, so it can be displayed. Performing a SELECT everything type of query on a large table is going to fill up the temporary file that SSMS is using to hold the results.
That temporary file is created in your temp folder. You can modify this behavior by modifying the TMP and TEMP variables for the user running SSMS. Change that to a different drive and it will fill up that drive.
Now, the real question is what are you going to do with all of that data in SSMS?

Additional info - the TMP and TEMP variables are Operating System environment variables - not SQL.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 5

Additional info - the TMP and TEMP variables are Operating System environment variables - not SQL.

Jason,

Thanks - sometimes I forget that you have to be very specific and miss small details like that. 😛