SQL Server 7.0 temp database log filled

• Steve Jones - SSC Editor

  SSC Guru

  Points: 739542

  More actions

  March 17, 2004 at 1:32 pm

  #499196

  Probably some scripts on the site here.

  Could you not run a backup transaciton with no_log? Can't remember if that works in 7. If not, killing the spid should have started a rollback. Sometimes this takes longer than rebooting a server, but you have to check.

  You can't really prevent this. There will always be some stupid query that can fill up tempdb. In all systems. Setting alerts can help, but you want to be above your "normal" high water mark so you don't get nailed constantly. I might look to increase my space, set an alert that should never be reached without an extraordinary event and then go from there.

  This is why ALL queries need to be tested before being run on a live system.

• Manny

  SSC Veteran

  Points: 290

  More actions

  March 17, 2004 at 1:41 pm

  #499200

  Steve,

  You are right there is no_log option in the Backup statement, so I could have done that too. I think I chose to re-boot because there was a few other things wrong with the server and I chose to re-boot to get a fresh start.

  Thanks for all your help

• Dan Swan

  Newbie

  Points: 1

  More actions

  March 18, 2004 at 7:14 am

  #499317

  If you go into the properties of database in question and click on "automatically grow file" under the "transaction log" tab, this should allow you to do a database dump.  Of course, it will also increase your pre-set size of the log, but you should then be able to shrink it, then set it down to the original size.  I am not sure if that works in SQL 7, but I have done it before in 2000.

• Manny

  SSC Veteran

  Points: 290

  More actions

  March 18, 2004 at 4:33 pm

  #499431

  Dan,

  Thanks for the info next time my log file fills up, I will do that.

  Cheers