We have a site on SQL Server 2005 SP2 EE. They are using synchronous
database mirroring. Both the principal and mirror are on a fast LAN. When
they perform transaction log backups on the principal database, our
application that accesses the principal database freezes for 1 or 2 seconds.
The transaction log backups are being written to the same disk where the
database resides. They have tried reducing the frequency of the transaciton
log backups to every 10 minutes, and that reduced the length of the app
freeze, but did not eliminate it. Has anyone heard of a problem running
transaction log backups when the database is mirrored?
Thanks,
Heather
I would be more concerned as to the placement of the log backups. Why would
you write them to the same disk that hold the data files? This is definitely
a candidate for a performance hit not to mention a problem if the disk
itself crashes. You loose both the data and logs and are probably in line to
update the resume
. If you saturate the I/O the db can appear to freeze.Andrew J. Kelly SQL MVP
Solid Quality Mentors
"HeatherK" <HeatherK@.community.nospam> wrote in message
news:E6A9E23D-916B-4CEE-8C3F-8801CD3394EB@.microsoft.com...
> Hello,
> We have a site on SQL Server 2005 SP2 EE. They are using synchronous
> database mirroring. Both the principal and mirror are on a fast LAN. When
> they perform transaction log backups on the principal database, our
> application that accesses the principal database freezes for 1 or 2
> seconds.
> The transaction log backups are being written to the same disk where the
> database resides. They have tried reducing the frequency of the
> transaciton
> log backups to every 10 minutes, and that reduced the length of the app
> freeze, but did not eliminate it. Has anyone heard of a problem running
> transaction log backups when the database is mirrored?
> Thanks,
> Heather
|||Hi Andrew,
Thanks for your post.
Yes, we realize that the placement of the backup files is not optimal. They
are using RAID5 on the drive, so they should be protected from a disk crash.
We realize RAID 5 is not optimal for performance, but that is what they have.
We have not seen a problem with transaction log backups at our other sites
that are not using Database Mirroring. That is why we were wondering if
there were any known issues with taking transaction log backups when you are
using synchronous Database Mirroring.
We will gather more performance metrics to determine if this is strictly
related to I/O or if there is a connection with Database Mirroring.
Thanks,
Heather
"Andrew J. Kelly" wrote:
> I would be more concerned as to the placement of the log backups. Why would
> you write them to the same disk that hold the data files? This is definitely
> a candidate for a performance hit not to mention a problem if the disk
> itself crashes. You loose both the data and logs and are probably in line to
> update the resume
. If you saturate the I/O the db can appear to freeze.> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "HeatherK" <HeatherK@.community.nospam> wrote in message
> news:E6A9E23D-916B-4CEE-8C3F-8801CD3394EB@.microsoft.com...
>
|||Well mirroring does add overhead and potentially even a little more I/O on
the drive with the logs so I am sure it adds to the whole mess. Doing a log
backup also forces a checkpoint which is I/O intensive as well. What do the
perfmon counters and virtual file statsw say for that drive and the log
files? And by the way you should never count on the fact that it is Raid 5
to avert a crash. They would be better off copying the files to a network
share.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"HeatherK" <HeatherK@.community.nospam> wrote in message
news:C0FB6FB3-9661-4BAA-84D2-17AC2B7C1A6F@.microsoft.com...[vbcol=seagreen]
> Hi Andrew,
> Thanks for your post.
> Yes, we realize that the placement of the backup files is not optimal.
> They
> are using RAID5 on the drive, so they should be protected from a disk
> crash.
> We realize RAID 5 is not optimal for performance, but that is what they
> have.
> We have not seen a problem with transaction log backups at our other sites
> that are not using Database Mirroring. That is why we were wondering if
> there were any known issues with taking transaction log backups when you
> are
> using synchronous Database Mirroring.
> We will gather more performance metrics to determine if this is strictly
> related to I/O or if there is a connection with Database Mirroring.
> Thanks,
> Heather
> "Andrew J. Kelly" wrote:
