Post reply

Server Freezes When Running CHECKDB

  • November 23, 2007 at 8:02 am

    #179408

    SQL 2005 standard SP2 on Windows 2003 Standard SP2

    The server freezes when running CHECKDB with no parameters on just one particular database. has anyone come accross this before?

    I had previously run CHECKDB on this database but cancelled it, could this have any affect on it?

    The server has just been built and the database taken from another SQL2005 server.

    Thanks

  • November 23, 2007 at 9:02 am

    #753656

    How big is your DB? I recently ran that against my 30 gig DB, and it took some time...

    -- Cory

  • November 23, 2007 at 9:10 am

    #753660

    65Gb, but it completely freezes everything out, it runs at 3am and when you get in in the morning the server is inaccessible, you have to switch it off, checkdb shouldn't freeze the whole server after 5 minutes, when you reboot the server is reports that there was an unexpected shutdown at the time the server froze even though it didn't reboot.

    I did a test and it is definitly the CHECKDB job that does it.

  • November 23, 2007 at 9:14 am

    #753663

    How often does anyone else run CHECKDB? I try to run it nightly but once a week if I can't, the same with defragging indexes and index rebuilds, is running maintenance tasks daily too OTT?

  • December 3, 2007 at 1:18 pm

    #756651

    Most places I have come across run reindexing/checks on a weekly basis.

    As far as reindexing goes, it depends on how much data gets put in and what the performance is like when retrieving it.

    For running checks, it depends on how paranoid you are. :ermm:


    Scott Duncan

    MARCUS. Why dost thou laugh? It fits not with this hour.
    TITUS. Why, I have not another tear to shed;
    --Titus Andronicus, William Shakespeare

  • December 4, 2007 at 3:04 am

    #756825

    Have you checked both the system event log (looking for hardware issues) and the SQL errorlog?

  • December 4, 2007 at 8:59 am

    #757000

    Are you using Maintance Tasks to do this or SQL Agent. Personally, I have run into more odd issues / failures when using Tasks compared to executing the code in a SQL Agent Job.

  • December 4, 2007 at 9:55 am

    #757040

    I don't use maintenance tasks, I don't think you have as much flexibility with them but I've not really investigated them enough in 2005, I tend to use the same jobs I've always run on 2000 with some slight updates.

    I try to run defrag and checkdb daily and then reindex once a week, I'm coming to the conclusion though on some of my servers with a lot of databases that I should run them all just once a week but the server I posted about only has one database on so I'll do the daily routine for now.

    I have managed to fix it, it was my own stupid fault, but now I know what not to do, I split tempdb between three seperate drives hoping I would see a processing increase, one of the files was on the E drive which was a partition with C, I removed that file and now it works great, still takes two hours for 71Gb but I can afford that in the middle of the night, only takes 12 minutes for a defrag though.

    I did think though that it shouldn't screw up the server that much it just being on the same drive as the OS but maybe it's something to do with splitting tempdb but I don't have time to mess about with it too much to work it out, all I know is it works fastest with tempdb data file on one drive and the log on a seperate drive, I should have just stuck to the simple solution first!:)

    Thanks

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

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