niaidsdt
asked on
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Anyone ever use materialized views to solve this issue?
"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
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