SQL server - Tempdb drive filled

I am by no means a seasoned DBA but am learning.  Yesterday, we had a query that ran wild and filled up the drive tempdb was on.  There has got to be a way to prevent this from happening / a way to limit how much resources a single query can have.
niaidsdtAsked:
Who is Participating?
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.

DBengineerCommented:
The tempDB utilization depends on the batch statement written by the developer.

I would recommend the following to prevent.

Optimize the SQL statement.
(or)
Give enough disk space to accomodate enough tempDB growth.

Other than this I dont think you can limit the SQL batch to limit TEMPDB resource usage regardless of the SQL statemetns internal call for sorting and merging.
0
lcohanDatabase AnalystCommented:
In SQL 2008 you have the Resource Governor available for that however you need some experience to use it (by any means I do not want to offend you as I understand that you are learning now) and please see link below for that.

http://msdn.microsoft.com/en-us/library/bb933866(v=sql.100).aspx


I suggest move tempdb on another location with sufficient drive space as this database is needed for mutliple reasons and you shouldn't (can't anyway) cap it. On another note tempdb is rebuilt everytime SQL Service restarts so is easy to move - just issue a ALTER DATABASE command and next restart will be created at new location.

http://support.microsoft.com/kb/187824
0
Anthony PerkinsCommented:
You can of course restrict the growth of tempdb, instead of allowing the default unrestrincted growth, but this will affect all queries and not just the one causing you grief.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

niaidsdtAuthor Commented:
So i think it's fair to say that tempdb needs to grow and grow and grow.  There's next to nothing that you can do about it.

lets look at it from another direction.  Can we restrict the amount of data returned?  For example, what if someone says give me all data from every table.  This would obvioulsy put a serious strain on the SQL server.  Is there a way to limit how much data can be returned with a single query / how much memory a single query can use / how much cpu a single query can use?
0
Anthony PerkinsCommented:
So i think it's fair to say that tempdb needs to grow and grow and grow.  There's next to nothing that you can do about it.
No, that is not entirely true.  You can restrict the growth but it will affect all queries.

Is there a way to limit how much data can be returned with a single query / how much memory a single query can use / how much cpu a single query can use?

Not to my knowledge.
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
niaidsdtAuthor Commented:
Anyone ever use materialized views to solve this issue?
0
lcohanDatabase AnalystCommented:
"Is there a way to limit how much data can be returned with a single query / how much memory a single query can use / how much cpu a single query can use?"

You can use SET ROWCOUNT to ".... stop processing the query after the specified number of rows are returned."

http://msdn.microsoft.com/en-us/library/ms188774.aspx

and you can use "Resource Governor enables you to specify limits on the amount of CPU and memory that incoming application requests can use."

http://msdn.microsoft.com/en-us/library/bb933866.aspx
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.

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.