Post reply

Rate my performance

  • October 24, 2007 at 3:36 pm

    #126302

    I got a call after hours because one of our databases had stopped responding. I logged in and found it was out of disk space. The log had ballooned to over 140 Gb!

    Here's what I did:

    First, I backed up the log with Truncate_Only

    Next I shrunk the database

    Then I immediately did a full backup

    Normally I would have done a full backup as my FIRST step as well, but I did not have the resources to accomplish this.

    Suggestions for improvement?

    Thanks!

  • October 24, 2007 at 3:45 pm

    #745136

    Figure out why it got to be that big?

    Is there a scheduled BACKUP LOG operation? Is it actually firing?

    Are your backup operations not truncating the logs?

    Are you having dead transactions that haven't committed?

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • October 24, 2007 at 4:15 pm

    #745140

    Actually, I was mainly wanting to see what I might have done differently after it got to that point. This was my first experience with a real-time out of space scenario. Sorry that wasn't clear, we had a runaway application that caused the mess. The application error is still under investigation.

    I'm pretty new to this employer still, and starting to get a good feel for our systems (as far as knowing what's kept where and who uses it). We do seem to have a need to review our administration. We have no performance-based alerts configured, so I've got my work cut out for me. The system maintenance in place was all set up using the maintenance plan wizard, and I haven't sifted through all of it yet to see what else needs adjusting.

    Thanks!

  • October 25, 2007 at 12:48 am

    #745235

    Did you shrink the data files, or just the log file? If the data files, you need to check the index fragmentation as the shrink probably shuffled the index pages all over the place.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 25, 2007 at 3:58 am

    #745287

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=584063&SiteID=1

    Here is a link that explains a bug with sql server. The transaction log file size ballons after a change to the DB . You might want to see if this is the case with your DB as well .

    I also tried a DBCC CHeck db

    good luck

    Jayanth Kurup[/url]

  • October 25, 2007 at 4:16 am

    #745294

    looks like you are running out of space in the drives... better move the unwanted files to some remote drives where the users cannot access the same.

    now reboot the server. You should c all the databases up & running.

  • October 25, 2007 at 1:33 pm

    #745475

    Thanks all for the suggestions. We have a weekly job that does a dbreindex (from the maintenance plan wizard), so the indexes will have been rebuilt by now - though I'll certainly remember that for future shrinkage. I will add the checkdb to my regular post-op routine as well.

Viewing 7 posts - 1 through 6 (of 6 total)

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