| Author |
Topic |
|
jung1975
Aged Yak Warrior
503 Posts |
 Posted - 2006-08-23 : 14:42:41
|
| Is the size of tempdb can affect the DB performace ?My DB is getting slow and I just notice that the size of tempdb is 13GB... Do I have to shrink it? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-23 : 14:47:35
|
| The size does not impact performance. The fact that your queries require so much tempdb space could matter.Tara Kizer |
 |
|
|
jung1975
Aged Yak Warrior
503 Posts |
Posted - 2006-08-23 : 15:03:53
|
| so, there will be no performance gain after shrinking the tempdb size? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-23 : 15:09:03
|
| That is correct. In fact, if you shrink it, it might have a negative impact. That's because if a query needs to expand the tempdb database due to its small size, the query will suffer as it has to wait for the expansion. This is why shrinking should never be done in production unless you know for sure that you don't need the space anymore or you are running extremely low on disk space and the shrink is temporary.Tara Kizer |
|
|
|
jung1975
Aged Yak Warrior
503 Posts |
Posted - 2006-08-23 : 15:34:43
|
| thanks tara..I have one more question.. Can you set up the eamil alert when the log file size hit the maximum allocated size in the SQL job?I would like to write a scrip that will shrink the logfile when the size of log file hit the maximum size...What is the best way to do this? do you have some example to show me?thanks again. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-23 : 15:42:17
|
| I've never done that through SQL Server. We've got third party tools watching the disk space for us. I would not do a shrink though when the log file size reached a certain size. I'd page the DBA and have him/her decide what to do.Tara Kizer |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-01 : 18:38:59
|
| "My DB is getting slow and I just notice that the size of tempdb is 13GB... Do I have to shrink it?"Shrinking TEMPDB requires that you use some very special methods, otherwise you will corrupt the database.Stop/Start SQL server is the easiest method to use - if that does NOT shrink TEMPDB them please ask back here.Generally SHRINK TEMPDB requires setting the Server / Database to single user and a bunch of very invasive techniques of that ilk, which are not appropriate for quick-fix solutions, and thus the problem is usually solved using a different solution!Kristen |
|
|
|
pbansal
Starting Member
1 Post |
Posted - 2007-07-30 : 07:06:42
|
| I am also facing a problem of huge size of tempDB. Earlier it use to get shrinked on re-start of sql services but off late it has started not responding to that even. The current size of the temDB is set around 6 GB now. What to do to shrink that?Is this really importanmt for me to shrink this size to get better performance?the use of high tempDB is generally once every month at the time of monthly processing for one of my database [DB size around 40GB] and otherwise for the rest of the time it has no use....Thanks.. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-30 : 08:09:08
|
| "What to do to shrink that?"Stop SQL Service and manually delete the files"Is this really importanmt for me to shrink this size to get better performance?"Probably not - unless you are tight for disk space. Extending TempDB every time there is a big transaction is more effort than just reusing the file if it is already there - and if you need it once a month then I don't see a problem.If it is continuously growing, or has reached a large size because of a one-time-only massive transaction, THEN I would shrink it, but otherwise I would leave it alone.Kristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-30 : 23:04:57
|
| You should check apps as Tara said. We had tempdb growing issues, all caused by bad queries. |
|
|
|
|
tempdb size and performance