| Author |
Topic |
|
nitin1353
Constraint Violating Yak Guru
381 Posts |
 Posted - 2008-10-17 : 06:03:01
|
| Gurus,We are using a third party tool for monitoring and i am getting this message"Database [tempdb] has transaction log files that are 86.86% full."Please Advice.ThanksNitin |
|
|
nitin1353
Constraint Violating Yak Guru
381 Posts |
Posted - 2008-10-17 : 06:40:12
|
| I have used DBCC shrinkfile and it is fine nw.Thanksnitin |
 |
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2008-10-17 : 10:48:03
|
| Never shrink your files, it's not considered a "best practice". tempdb is your work area to resolve queries, perform DB maintenace and so on. Size the database appropriately so that you DO NOT face a "full" situation as this will bring SQL to a halt until you clear it up (and 86% is not quite a critical situation). Restarting SQL will clean out tempdb but the bigger issue is what's causing it to fill up?? You need to get a better handle on the processes occurring on your server.-Edit: why not add additional disk, if possible? Also, is tempdb set to "autogrow"?Terry |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-17 : 11:01:57
|
| Also check your recovery model of TEMPDB. Set it to simple. |
|
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2008-10-17 : 11:05:58
|
quote:
Originally posted by sodeep
Also check your recovery model of TEMPDB. Set it to simple.
Which is the default upon install of SQL. Personally, I don't mess with the system DB settings, but that's just me.Terry |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-17 : 11:07:30
|
| Yes, it is. Sometimes have to make sure that it is not set to FULL. Thats why I said. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-17 : 11:11:28
|
| Here is good article for TEMPDBhttp://sqlblogcasts.com/blogs/thepremiers/archive/2007/10/17/tempdb-best-practices.aspx |
|
|
|
SimpleSQL
Yak Posting Veteran
85 Posts |
Posted - 2008-10-17 : 23:10:11
|
| Shrinking TempDB is not a good idea, if the tempdb is used extensively, it would be because of temp tables, table variables, large hash/sort operations, so if the appliction continues to perform these operations, TempDB would grow. Best would be to estimate what are your TempDB requirements and grow TempDB accordingly.With 86% used, you still have capacity left. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-18 : 02:02:55
|
| Remember to shrink TEMPDB ,It should be in single-user mode. so it is not good idea. |
|
|
|
gavis
Starting Member
39 Posts |
Posted - 2008-10-21 : 11:37:59
|
| sodeep, is it advisable for the tempdb to be in single user mode.what are the implication? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-21 : 11:39:56
|
| NO. |
|
|
|
|
Log file full