June 16, 2009 at 8:36 am
Is there a way to reduce the size of temp DB without restarting the SQL Server?
June 16, 2009 at 8:40 am
tempdb files can be shrunk with dbcc shrinkfile like any other. However this will have an impact on performance of the instance and should not be done unless you have severe space problems, the files will only likely have to grow again.
Also explicitly size your tempdb files to their expected required size so that they are created this size on startup and dont have to grow whilst the instance is in use.
---------------------------------------------------------------------
June 16, 2009 at 8:46 am
Thank you for your reply.
Will shrinking the file have any affect on the scheduled jobs that i've got set up.
June 16, 2009 at 9:01 am
As previously said, if you system is a highly transactional system, shrinking it will have no affect on filesize and in return might have significant performance impact as there might be connections to tempdb.
Re-Starting SQL server is the best practise.
but if this is not a highly transactional system, you can find a timeslice and put TEMPDB in a single user mode then shrink it and back to multi_user mode. This is risky too.. if any other transaction takes over the single user mode, it would be a cat a mouse game to get back/connect to tempdb.. and there also will be a performance impact with all the other transactions..
I am having a similar issue with my tempdb grown to 34 GB , but am searching for a spot where i can restart the sql server and mange the temdb size for future resolutions...(sql 2000 sp4 and windows 2003 sp2)
The jobs might be impacted, if there is too much of temp table creations and or your base query uses cursors joins/sorts etc.
check sp_spaceused to see how much is used and how much is unallocated in tempdb
Maninder
www.dbanation.com
June 16, 2009 at 9:03 am
potential performance impact, but it won't break them.
If you have to shrink tempdb try to do it when system is quite and don't shrink it right down to 8mb again, choose a sensible size for your system.
---------------------------------------------------------------------
June 16, 2009 at 9:03 am
If you search "shrink tempdb" you will find some good threads on this topic.
June 16, 2009 at 9:11 am
Mani Singh (6/16/2009)
As previously said, if you system is a highly transactional system, shrinking it will have no affect on filesize and in return might have significant performance impact as there might be connections to tempdb.Re-Starting SQL server is the best practise.
Only if you can afford an outage.
Sarvesh, whats the problem with having tempdb at 11GB? If you have not run out of space don't worry about it.
---------------------------------------------------------------------
June 16, 2009 at 9:16 am
I agree with George..
Maninder
www.dbanation.com
June 16, 2009 at 10:04 am
Thank you guys for your responses;
the mdf is 11GB and the log file is 2 GB. So it's using 13GB of the Drive space. Total drive space is 19 GB. It's only matter of time before the drive is full.
It's a 24 hour system where it's receiving transactions every minute. It's just hard to find a planned downtime to restart SQL Server.
I have a weekly Back up Job which performs DBCC CheckDB,, Could this be leading to the Temp DB file growth?
June 16, 2009 at 10:19 am
continue to monitor the sizes of the files, just because they have grown to their present size does not mean they will continue to grow.
for checkdb run it with the estimateonly option (see BOL) to get an idea of how much space it is using.
reindexing could also cause the growth if you are sorting in tempdb.
---------------------------------------------------------------------
June 16, 2009 at 11:19 am
sarvesh singh (6/16/2009)
...I have a weekly Back up Job...
and then you state
...It's a 24 hour system where it's receiving transactions every minute...
I hope you:
1) have the database in Full Recovery Mode
2) are doing frequent LOG Backups or Incremental backups
Now, on your question about the TempDB growth, you have a lot of DML's, large queries?
June 16, 2009 at 12:14 pm
Is the drive a dedicated drive for tempdb? If it is, then you really just need to identify why tempdb grew to that size and monitor for additional growth.
If the drive is shared, then you need to consider getting more disk space and moving tempdb.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 16, 2009 at 12:50 pm
Larger queries may also try and resolve themselves in tempdb. The reality is, what's running on the server? And then, whay are your resources so limited? You should have enough disk space readily available to limit system constraints. - IMO
-- You can't be late until you show up.
June 16, 2009 at 5:58 pm
Mani Singh (6/16/2009)
Re-Starting SQL server is the best practise.
Ummmmmm.... Who made that rule? Do you happen to have an MS document that actually recommends such a practice?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 17, 2009 at 4:56 am
Thanks for ur responses again.
I have full backup running every week, incremntal back up every night and then transaction log back up every 10 minutes.
But also every week it does dbcc checkdb..
I just did a test on our testing environment: Did a full back up and then did dbcc checkdb.. the tempdb file grew..
Took dbcc checkdb out,, the tempdb file did not grow.
Why checkdb causing the temp db file to grow.
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply