January 23, 2009 at 6:45 am
tempdb full problem. I came accross these problem very often when my tempdb was in C drive and then later i moved temdb to another drive where it has some user db's which are not that big and that drive has 1.5tb of space. But surprisingly even that when out of disk space. Is there way i can handle it so that i never run into such issues again.
January 23, 2009 at 7:03 am
More than knowing how to stop your tempdb from filling the drive, you need to find out what's causing it to grow so large? Have a look at the following from BOL. It goes through what may be causing such space issues with your tempdb and how to correctly size it to handle such things...
http://msdn.microsoft.com/en-us/library/ms345368(SQL.90).aspx
-Luke.
January 23, 2009 at 7:06 am
EXEC sp_spaceused gives me this result
database_name= tempdb
database_size= 318779.19 MB
unallocated space= 317417.23 MB
reserved= 1006096 KB
data= 877584 KB
index_size= 127944 KB
unused= 568 KB
temdb is set to simple recovery mode and it has 1 log file.
January 23, 2009 at 7:24 am
Do you have any size metrics over time that you've been able to correlate with a particular process that's using all the tempdb it can get? Sorting some really huge table(s) perhaps?
IE. on Friday's at midnight as some batch or other is processed, the tempdb's allocated space is all of a sudden very close to the db_size?
The following has some code to perhaps help identify what's causing you're tempdb usage...
http://msdn.microsoft.com/en-us/library/ms176029(SQL.90).aspx
-Luke.
January 23, 2009 at 8:26 am
This is the query which is causing issue.
select distinct e.code,startdate,enddate
into #f
from exams e
inner join
statePA s
on e.code = s.idcode
where
(startdate = '1/1/2007')
and joindate between '1/1/2007' and '12/31/2007'
and joindate between startdate and enddate
January 23, 2009 at 10:31 am
this query just pulls 3 million records but it runs for more than 2 hrs and then gives an error " temdb full" though i have 800GB free space.
January 23, 2009 at 12:15 pm
I take it that your tempdb is set to autogrow...
Perhaps your bumping into the issue where it does the autogrow, but it doesn't grow enough in one shot that the tempdb is full and then cancels the execution of the query.
Is it set to autogrow by percent or by a static rate? Has this query ever successfully run and if so what size was the tempdb to accomplish that? You might try manually growing it and letting the query run.
You might also try to tune your problem query so that it doesn't need quite as many resources in the temp db. Select Distincts typically force a lot of sorting and such all of which will happen in tempdb if the server's memory can't contain it all. Plus you're then writing this fairly sizeable temp table to tempdb. It may be "just" pulling 3 million records but depending on the width of your source tables and if it's needing to do scans instead of seeks you may be having some issues...
The fact that it take 2 hours to run really seems a bit much, but depending on your data I suppose that could be a necessity. Perhaps you if gave us some DDL and sample data we might be able to help tune the query a bit? Also perhaps an execution plan might be helpful.
-Luke.
January 23, 2009 at 1:14 pm
Mike Levan (1/23/2009)
This is the query which is causing issue.select distinct e.code,startdate,enddate
into #f
from exams e
inner join
statePA s
on e.code = s.idcode
where
(startdate = '1/1/2007')
and joindate between '1/1/2007' and '12/31/2007'
and joindate between startdate and enddate
Do you have indexes on these columns?
- joindate (ideally a clustered index)
- startdate
- e.code
- s.idcode
If you do, check in the execution plan whether these indexes are being used.
Also - and I could be wrong here - it seems to me the last line of your query is tripping up, causing it to go into some sort of infinite loop. Try running the query without that last line and see if there is a significant difference in behavior:
select distinct e.code,startdate,enddate
into #f
from exams e
inner join
statePA s
on e.code = s.idcode
where
(startdate = '1/1/2007')
and joindate between '1/1/2007' and '12/31/2007'
It looks like your tempdb is exploding because of the enormous space required by the internal objects your query is generating.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply