Solved

SQL server - Tempdb drive filled

Posted on 2012-04-05
7
641 Views
Last Modified: 2012-05-25
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.
0
Comment
Question by:niaidsdt
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 1

Assisted Solution

by:DBengineer
DBengineer earned 100 total points
ID: 37813919
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
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 100 total points
ID: 37813927
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37814861
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:niaidsdt
ID: 37815790
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 100 total points
ID: 37816999
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
 

Author Comment

by:niaidsdt
ID: 37899370
Anyone ever use materialized views to solve this issue?
0
 
LVL 39

Expert Comment

by:lcohan
ID: 37899455
"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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Syntax 17 43
Separate 2 comma delimited columns into separate rows 2 41
Sql query with where clause 2 34
SQL - format decimal in a string 5 39
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

856 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