March 13, 2012 at 11:44 pm
Hi,
I was checking which DB is taking huge space and to my suprise the TempDB is taking ~203GB of space. Can anyone suggest the issue or is it normal as it does not have any user tables as such.
I am unable to shrink the same as well.
Regards,
Ankit
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
March 14, 2012 at 12:22 am
TempDB can grow to such levels but shouldn't remain at this level. All temo tables are dropped at the end of the session that created it
If your temp table is not having any tables in it then chances are the db file size settings are incorrect , check for SIze , MAX SIZE and esp FILEGROWTH
March 14, 2012 at 12:26 am
Thanks Jayant. I have restarted the service and would look into the settings.
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
March 14, 2012 at 12:58 am
SQL_By_Chance (3/14/2012)
.. I have restarted the service and would look into the settings.
Why did you restart the service? Why did you try to shrink the file? Was there any disk space issue?
If you don't have disk space issue, DON'T try to shrink or restart the service.
Investigate what is causing the file growth and preventing your from shrinking.
Shrinking or restarting the service are last resort.
Please read Troubleshooting tempdb
March 14, 2012 at 9:52 am
Taken from a post by Lowell not to long ago...http://www.sqlservercentral.com/Forums/Topic1265103-391-1.aspx
tempdb is recreated every time the server is stopped and started.
the only way to reclaim the space (as far as i know) is to stop and start the service again.
something made tempdb grow because the space was needed...it might be a developer doing select * from MillionBillionRowTable into #tmp, it might be rebuilding indexes with SORT_IN_TEMPDB, it might be just normal for your server because it needs to use temp to build up intermediate results for queries.
if it grew once to a large amount, there is a high probability it will grow to the same size again.
Please don't attempt to shrink tempdb as this can cause corruption.
What you need to do is pinpoint what was running/is running during your excessive tempdb growth times (perhaps set up a few alerts to email you when it's happening so you'll be reminded to go look), otherwise, it may grow out of control again...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 14, 2012 at 10:12 pm
Thanks all for your valuable inputs. As lowell pointed out yeah in past we did create #tmptable with million-billion rows. And one of the servers with (1TB- TempDB) had bad queries like these added to that the Services were never restarted for past 2 years. Do you think that was the reason of such a huge TempDB than configuration settings of the TempDB.
Regards,
Ankit
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
March 14, 2012 at 11:22 pm
SQL_By_Chance (3/14/2012)
Thanks all for your valuable inputs. As lowell pointed out yeah in past we did create #tmptable with million-billion rows. And one of the servers with (1TB- TempDB) had bad queries like these added to that the Services were never restarted for past 2 years. Do you think that was the reason of such a huge TempDB than configuration settings of the TempDB.Regards,
Ankit
Those two reasons are typically the leading causes of explosive growth of TempDB.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 15, 2012 at 1:00 am
Please don't attempt to shrink tempdb as this can cause corruption.
Is there a way to shrink the tempdb without having the downtime?
March 15, 2012 at 2:47 am
baabhu (3/15/2012)
Is there a way to shrink the tempdb without having the downtime?
No.
March 15, 2012 at 4:59 am
This was removed by the editor as SPAM
March 15, 2012 at 8:00 am
The safest way to resize tempdb is to schedule a maintenance window, restart the mssql service, resize (increase) the tempdb files to a reasonable size (adjusting the auto growth settings accordingly), then flog anyone who creates a temp table for storage of records over a 100,000 rows! Tables that large really merit using a physical table (that can be dropped after it's used)...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply