Post reply

SQL Server 7.0 temp database log filled

  • March 17, 2004 at 9:35 am

    #84642

    Hello everyone,

    I just had a very interesting experience yesterday with SQL Server 7.0. I had a user running an extremely long query and because the query ran for so long (3 days to be exact) my tempdb log filled up. I couldn't truncate or it backup because a backup is a logged operation and since the log had no space I was unable to do anything about it. The only solution was to restart the server.

    My questions are:

    1) If this happens again, is there any way I can recover from this error without having to re-start my server ?

    2) Is there any way I can prevent this from happening again ? I was thinking of creating a job that runs every half hour to check the size of the log space and warn me if it gets bigger than 90 percent full. Alerts only seem to warn you when the tempdb is full but by that time you're screwed anyways.

    3) Does anyone have a sample scripts to monitor long running processes ? I going to run jobs now that will monitor long running processes so that I can prevent this from happening.

    Any help would be appreciated.

    Cheers

  • 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.

  • 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

  • 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.

  • 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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply