Strange restore issue

Ok, my turn to ask a question...  We've been seeing, for the past year
or so, a strange problem with database restores.  The situation:

- production, standby, and DR environments, multiple servers/databases
in each
- log shipping from production to standby and DR environments
- nightly full backup of production, COPIED TO and RESTORED to standby
and DR environments (restore is NOT done across the wire)

This problem affects all databases, but I'll focus on just one for
simplicity.  This database is currently ~220GB.  The copy/restore steps
combined normally take 2.5-3 hours to complete.  Roughly once a month,
about every 4 weeks, this changes literally overnight, these steps will
take 7+ hours.  It will continue to take this long until we reboot that
standby server, at which point the time drops back down to < 3 hours.

The problem is with the restore - the copy itself doesn't take any
longer, it's the actual RESTORE.  Watching Perfmon, I can see that when
things are NORMAL, there is a steady, continuous burst of disk I/O while
the initial database is created, followed by a steady, continuous burst
on the SQL Server: Device Throughput Bytes/sec for this restore device.
When the "problem" occurs, there are short, sporadic bursts of disk
I/O, followed by longer, but not continuous, bursts on the restore
device.  CPU is negligible, during both normal and problem times.  There
is very little, if any, page swapping taking place.

I've run DiskKeeper (with SQL shut down) on the volumes containing the
database file, log file, and restore file, no effect.  Only a reboot
will put things back to normal.

I'm out of of ideas on what to look for.

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Tracy McKibben  wrote:
> Ok, my turn to ask a question...  We've been seeing, for the past year
> or so, a strange problem with database restores.  The situation:
>
> - production, standby, and DR environments, multiple servers/databases
> in each
> - log shipping from production to standby and DR environments
> - nightly full backup of production, COPIED TO and RESTORED to standby
> and DR environments (restore is NOT done across the wire)
>
> This problem affects all databases, but I'll focus on just one for
> simplicity.  This database is currently ~220GB.  The copy/restore steps
> combined normally take 2.5-3 hours to complete.  Roughly once a month,
> about every 4 weeks, this changes literally overnight, these steps will
> take 7+ hours.  It will continue to take this long until we reboot that
> standby server, at which point the time drops back down to < 3 hours.
>
> The problem is with the restore - the copy itself doesn't take any
> longer, it's the actual RESTORE.  Watching Perfmon, I can see that when
> things are NORMAL, there is a steady, continuous burst of disk I/O while
> the initial database is created, followed by a steady, continuous burst
> on the SQL Server: Device Throughput Bytes/sec for this restore device.
>  When the "problem" occurs, there are short, sporadic bursts of disk
> I/O, followed by longer, but not continuous, bursts on the restore
> device.  CPU is negligible, during both normal and problem times.  There
> is very little, if any, page swapping taking place.
>
> I've run DiskKeeper (with SQL shut down) on the volumes containing the
> database file, log file, and restore file, no effect.  Only a reboot
> will put things back to normal.
>
> I'm out of of ideas on what to look for.
>

Apologies for replying to myself, I left out some info...

This problem ONLY occurs on ONE standby server.  The obvious answer is
to swap out the server, right?  Well, we've done that - every six months
we "swap" our standby and production servers.  The problem occurred on
the "old" standby server before the switch, and continues on the "new"
standby server.


--
Tracy McKibben
MCDBA
http://www.realsqlguy.com