How to avoid fast growth of Transaction Logs

• F. Dwarf

  SSCommitted

  Points: 1598

  More actions

  April 10, 2007 at 9:10 am

  #699573

  Hi Francis.

  I'm a little concerned about your tempdb issue. Perhaps you should re-check your proceses and figure out which is (are) the one that is consuming the resources in tempdb and take a deeper look at it. Altough restarting the sql server service helps you recreate tempdb you must be aware that it doesn't solve the root of the problem (of course, in case there is any)

  Regards!

• Greg Wilson-404807

  SSC Eights!

  Points: 906

  More actions

  April 10, 2007 at 12:49 pm

  #699624

  Is Snapshot Isolation turn on in your database?  If so, are you actually using it, or did you just turn it on because it is a new feature?

  Using Snapshot isolation can make a huge impact on the size of TempDb because that is where all the snapshots are held.  If Temp DB is getting too big too fast, then I'd suggest switching Snapshot off unless you were sure it was neccessary.

  Otherwise, I'd start inspecting the jobs that are run on the server for temp tables that might be getting stored longer than neccessary.  In fact lots of temp tables can be gotten rid of entirely by re-writing the SQL. If the goal is to lower the size of TempDb, getting rid of temp tabels will help the situation.

  Warm Regards,Greg Wilsonsolidrockstable.com

• Francis Formalejo

  SSC-Addicted

  Points: 411

  More actions

  April 10, 2007 at 6:25 pm

  #699665

  Hi Greg,

  I believe that I do not have this Snapshot Isolation in my SQL Server 2000 yet my databases are not replicated to other machines as well. The problem I'm encountering is that, every time the TempDb is FULL (in a certain point of time) the resolution to fix this is to restart/reboot the server which compromises my production system. With this, I'm having a downtime for at least 5 Minutes or less.

  I will have to look for the queries that giving giving such problem.

  Many thanks!

• Francis Formalejo

  SSC-Addicted

  Points: 411

  More actions

  April 10, 2007 at 6:29 pm

  #699667

  Yes, I'm just wondering if there is a way not to restart the server just to rebuild the TempDb... Anyways, well it would be better if there is a script that will cleanup the garbage data in TempDb

  In my development server, I'm always experiencing this problem which is fine because developer always need to test their scripts.

  Thanks Aaron!