| Author |
Topic |
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
 Posted - 2010-05-28 : 19:29:26
|
I have a discussion with regard to keeping the TEMPDB file sizes to a minimum, then it is instead of Database practises - like shrinking and against to it there will a method like rebooting the server, through which the next restart the TEMPDB will release its all its unwanted spaces. So it is simple restart - However, I have got this explanation from the Think Tank.Now May I ask the Experts and Heavy weights to intervene and explain this idea below. quote:
The Microsoft guide on Best Practices for TEMPDB setup\monitoring.It appears that there are methods for viewing which queries or requests are using up most of the space within tempdb..Like said, there is no simple way of shrinking this database as it can cause corruption and needs to be done in single user mode anyway (Which means no one can write while the shrink is being done). Microsoft suggests that we monitor the growth of the tempdb and when we find its ‘highest sustained peak’ we will have identified how big we should expect it to be, they then mention that once you know the peak you should allow 20% more (for future growth).Interesting read.. http://technet.microsoft.com/en-au/library/cc966545.aspx
Many thanks for your Ideas and participation. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-29 : 01:40:41
|
Why do you want to shrink it? It has grown to a size that was necessary to accommodate the biggest "overspill" of data that occurred.If it is growing uncontrollably then there is something wrong, and that needs finding & fixing.When you next restart SQL Server TEMPDB will reset back to the set "initial size" (which ought to be the same as your normal working size to avoid it having to grow piece-meal.If TEMPDB does not reset to configured initial size on restart again something is wrong and needs finding and fixing.We do get people asking for help here for both those scenarios - so I'm not saying it doens't happen! but the answer isn't to use SHRINK, its to fix the underlying problem  |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-05-29 : 12:31:24
|
| We use a minimum size of 1GB for each tempdb data file. We use one data file per CPU as per the recommendation, so on some systems I've got 16GB of tempdb data files. dbalearner, you should consider setting the initial size to the most common maximum size of the data file to avoid the performance hit of extending the file. The goal is not to have tempdb be the smallest possible, but rather the goal is to start it at a size that will not require it to grow very often.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
Posted - 2010-05-29 : 18:23:59
|
| I knew these things will occur but idea is not SHRINK.What I have setup for the TEMPDB is when grown instead of SHRINKING I would rather dynamically leave the system to set it properly, which is the default settings for the TEMPDB.However, I have not done with abrupt Shrink but I would ask the basic ethics of what I have QUOTED and the URL based - it is not necessary to REBOOT the SQL Server for ever.Based on the above statement, I would like to draw the conclusions that - what are the advantages of REBOOT?Is it really needed to REBOOT at any given timeframe or never?While reboot TEMPDB gets automatically to its parameter setup, what are the other advantages associated with Reboot?Thanks for your expertise. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-30 : 02:18:05
|
The theory is:You set TEMPDB initial size to what is required "in normal working"."in normal working" TEMPDB will not grow bigger than that size anyway.So only abnormal activity will increase the size of TEMPDB.Note that you don't have to reboot server, just stop/start SQL Service but, as Tara says, installing Service Packs periodically will do the job for you  |
|
|
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
Posted - 2010-05-30 : 22:06:03
|
| Wonderful Tara & Kristen.It was on the expected lines and I knew but the article which say about was on what lines the TEMPDB can be controlled as Kristen quoted only if only in abnormal working scenarios as some one executes a very long SP which took sizable amount of time consumes the TEMPDB to grow but this is something extraordinary.Now exact point - When TEMPDB grows then EASIEST method is to REBOOT and as simple as that rather that DBA working of shrinking which is time taking and side effects.So How often even if there is no such ABNORMAL activities yet TEMPDB grows - is it correct to do reboot or till what point of time I should wait to get it trimmed.Thanks all. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
Posted - 2010-06-02 : 01:05:50
|
| Thanks All for giving the needed information.We do have some downtime which can be allowed as may be weekends or Early hours of morning where we can do the reboot.is it correct - on reboot the SLEEPING PROCESSES gets ousted from the activity? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-06-02 : 12:27:15
|
| Everything gets kicked out during a reboot as the service is stopped. At startup, SQL Server will go through a crash recovery process where processes are rolled back or committed, depending upon their state when the service went down. Crash recovery can be lengthy, so ensure that the service is stopped when a long running transaction isn't running (such as ALTER INDEX REBUILD) and that you don't have thousands of VLFs.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
TEMPDB Saga