Post reply

Restore Database is taking forever

  • August 9, 2010 at 1:13 pm

    #97326

    Hi All,

    The restore database is taking for ever on sql server 2000 sp4 /windows server 2003 64 bit.

    when I see sp_who2 active I see Disk I/O's Column are less than 100 for long time. The only actitvity going on the server is restore command

    I checked taskmanager/Perfmon and database server activity for performance issues,Memory looks good , 10 utilization on CPU(which is absolutely nothing), no errors in event logs.

    The only thing I noticed is is avg disk queue lenth is 100 % most of the time but I dont see any disk activity from sql end or server end.

    Any help is really appreciated in troubleshooting this issue......

  • August 9, 2010 at 2:11 pm

    #1204387

    How big is your database? How long does it normally take? Do you have errors? It could simply be that it takes that long. I/O and counters don't go up on restores that I can see, it can just take a long time on VLDBs.

  • August 9, 2010 at 2:31 pm

    #1204401

    The database is 70 GB and it suppossed to finish in an hour when there is no activity. Its really strange as I dont see any logs in sqlserver.

    Thanks,

  • August 9, 2010 at 2:40 pm

    #1204405

    If sp_who2 shows it as running, it's running, and you have no errors -- it's running.

    If it is taking much longer than usual, first thing I can think of is to check the disks underneath. I know that when I have something on RAID 5 and I lose a disk, the array can run slower than normal. Also look to see if anything else is using the same spindle.

  • August 9, 2010 at 2:42 pm

    #1204407

    It sounds like it might be stuck. Perhaps the connection was lost for some reason, or there's some OS item that's waiting on a response. I'd kill it and try again

  • August 9, 2010 at 6:18 pm

    #1204462

    I have rebooted sqlserver(multiple times) but still nolock.

    we are just using local disks not using RAID at all.

  • August 17, 2010 at 3:44 pm

    #1208181

    Do you have a large log file with lots of vlf's as described here, if so this can cause long restore times, we ran into it and had a restore taking upwards of an hour, after remedying it it went down to 10 mins. Just a thought.

    Or maybe it is just stuck restoring, can you drop the restoring db and start again?

    Andrew

  • August 17, 2010 at 3:45 pm

    #1208185

    here is the link Dohh!!

    http://www.karaszi.com/SQLServer/info_dont_shrink.asp

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

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