tempdb size increased !
-
Dear All,
my tempdb's data file increased to around from 200 MB to 60GB,so i m running out of space on my disks.
what can be done in this scenario?
i m using only one #temp table in tempdb.
Is sql server service restart is the only one option available to gain the space on OS ?
-
Run dbcc sqlperf(logspace) to check what is the space being used by the log file of tempdb
then
backup log tempdb with truncate_only
go
dbcc shrinkfile(2,100)
Also check if any process is running that heavily uses tempdb like using temp tables, cursors, etc.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Thnks Sugesh..
but my tempdb log file is not used heavily it's only 600 MB(checked through sqlperf(logspace),but the tempdb data file grows more rapidly..
one procedure creates a temp table nut it id also having only 2 columns..
-
Other possibility is that you have jobs for DBCC defrag/reindex that are executed on adhoc basis on a SQL DB that has considerable data (abt 30 GB or more). If this is true then you might want to execute DBCC on a regular basis may be some sort of job in quite period....
Or you have sql queries returning significent data are using order by clause....
-
any bcp/bulk imports, also check the number of rows in the temp table too many rows with indexes can occupy a good number of disk space in the server.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
If the batch size of BCP/BULK INSERT is relatively small (less than a million rows, say), then those are likely not the problem. Rumor has it that the extensive use of large amounts of XML will cause similar "problems" with TempDB... Large cursors, large Table Variables, and large Temp Table useage can also be the source of the problem. In some cases (explicit temp object or not), the size of TempDB may have simply been a mistake when someone (accidently or otherwise) did a cross-join or a correlated sub-query with a lot of triangular joins. 'course, I could be wrong.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Also, almost forgot... you can shrink TempDB (by file) as with any other DB without bouncing the server (possibly as Sugesh suggested). It could cause a fairly long wait time, though... might be quicker to bounce the server in this case but not 100% sure.
I really think that 200mb is too small (a gig sounds better, we use 9-12 gig on the servers at work).
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
As jeff says it better to bounce the service to clear tempdb database.
Jeff he is speaking of 60GB tempdb file which has got increased from 200MB.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Thnks all,
so i can conclude there is only one option of "SQL Server service Restart" as Shrinking of tempdb datafile will take lots of time...
also my question is => Is there any chance of data lost ? (b'coz the tempdb may contain some data)
thnks again...
-
Data loss in not possible since tempdb has only hash tables.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
There is no danger of data loss from TEMPDB if you just regular way stop and start SQL Server. Tempdb contains only temporary objects, so if all processes stop, there will be nothing that you could lose from tempdb.
I have no idea what caused the huge growth - except things already mentioned by other people. Just for your info, at 100GB of production database, our TEMPDB was around 15GB; now that the production DB is over 200GB, tempdb is around 35GB. It fills (and sometime grows) mainly during INDEXDEFRAG job and during certain calculations (monthly reports) in bookkeeping. Otherwise, it is most of the time almost empty.
-
OK , Good
-
Is "shrinkfile with no_truncate " a good option to resolve this issue.?
-
Heh... not unless some fool thought it would be a good place to store permanent data
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Well... that depends... do you want to shrink the file or not? The NOTRUNCATE causes the "freed file space to be retained in the files". If you want to actually shrink the file, I'd have to say "No", not a good option.
Lookup DBCC SHRINKFILE in Books Online for more information.
Also, keep in mind that something caused TempDB to grow in the first place. If it was a one-off job that did it, ok, might be worth shrinking TempDB or bouncing the server to recover some disk space. If it was just normal daily activities that cause it to grow, shrinking TempDB is not going to get you out of the woods... you'll need to make the minor investment of more disk space. Considering that you can buy a 300 gig harddrive for very little (even price of SCSI's have gone down alot, dunno about SAN's), I'd be on my way to the store. Look at Vladan's and my post about the size of our TempDB's if you don't think it's worth the invenstment... a properly sized TempDB makes for a happy server and "properly sized" can sinmetimes mean "BIG".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply