Restore Database is taking forever

Question

Restore Database is taking forever

sqlbee19

SSCommitted

Points: 1597
More actions
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......

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

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

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 1

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.

sqlbee19 SSCommitted Points: 1597 More actions · Answer 2

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,

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 3

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.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 4

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

sqlbee19 SSCommitted Points: 1597 More actions · Answer 5

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

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

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 6

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

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 7

here is the link Dohh!!

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