database and tlog backups - MSSQL 2000 & MSSQL 2005

hi all, as we have big problems with sql timeouts and deadlocks during
the backup of the databases and transaction log, i am searching for a
possibiliy to give the backup procedure a lower priority, so that the
sql requests falling into that period don't fail anymore. is there a
way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
another/better solution to avoid such problems! thx in advance!You can't assign priorities in any sql server version prior to 2008.
What you should do is attempt to find out WHY you are getting timeouts and
address that issue. Without further information, my first guess would be
I/O delays because you are backing up to the same drive system as the data
resides on. If this is the case you could try backing up to another disk
subsystem. NAS devices or iSCSI SANs are very effective for this and quite
inexpensive these days. Heck, for smallish dbs even an external USB 2 drive
enclosure could be beneficial.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:01e39c0a-4f3e-4741-97dc-823957f4abfb@.x69g2000hsx.googlegroups.com...
> hi all, as we have big problems with sql timeouts and deadlocks during
> the backup of the databases and transaction log, i am searching for a
> possibiliy to give the backup procedure a lower priority, so that the
> sql requests falling into that period don't fail anymore. is there a
> way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
> another/better solution to avoid such problems! thx in advance!|||I agree with Kevin in that it sounds like you have I/O issues to begin with.
You must also already have deadlock issues and the backups just make it
worse. I also agree with Kevin in that it sounds like you are backing up to
the same physical drive as the data or log files are on and this should be
changed immediately. A well configured system should only see a few % in
overhead when backing up. This depends on many factors but the process
itself is not that intensive that it should cause issues like you report
unless there are issues already.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:01e39c0a-4f3e-4741-97dc-823957f4abfb@.x69g2000hsx.googlegroups.com...
> hi all, as we have big problems with sql timeouts and deadlocks during
> the backup of the databases and transaction log, i am searching for a
> possibiliy to give the backup procedure a lower priority, so that the
> sql requests falling into that period don't fail anymore. is there a
> way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
> another/better solution to avoid such problems! thx in advance!|||I should have mentioned that you can evaluate I/O performance using
performance monitor (various physical disk counters including avg disk queue
length, avg sec/read avg sec/write). You can also evaluate wait stats using
dbcc sqlperf(waitstats) in 2000 or the sys.dm_os_wait_stats DMV in 2005.
Microsoft has very good documentation on what the waits mean. See BOL and
search for "sql server wait stats" or get this doc for 2005:
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13kdt7a10ahahec@.corp.supernews.com...
> You can't assign priorities in any sql server version prior to 2008.
> What you should do is attempt to find out WHY you are getting timeouts and
> address that issue. Without further information, my first guess would be
> I/O delays because you are backing up to the same drive system as the data
> resides on. If this is the case you could try backing up to another disk
> subsystem. NAS devices or iSCSI SANs are very effective for this and
> quite inexpensive these days. Heck, for smallish dbs even an external USB
> 2 drive enclosure could be beneficial.
>
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "morebeer" <stefan.beer@.webergy.de> wrote in message
> news:01e39c0a-4f3e-4741-97dc-823957f4abfb@.x69g2000hsx.googlegroups.com...
>> hi all, as we have big problems with sql timeouts and deadlocks during
>> the backup of the databases and transaction log, i am searching for a
>> possibiliy to give the backup procedure a lower priority, so that the
>> sql requests falling into that period don't fail anymore. is there a
>> way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
>> another/better solution to avoid such problems! thx in advance!
>|||Dear Kelly,
The strange thing is, that the opposite is the case. We are running 2
backups each day, a nightly one to the same hard drive and a daily one
to a different drive. The backup on the same drive causing much less
problems - however this backup also contains less data than the one
during the day. We already spent a lot of time in query optimization
and are aware that the database I/O sometimes run on the physical
limits, so we don't expect significant improvements by investing more
time on improving the queries and the indexes. That's why we hoped for
somekind of "on idle" backup option to start the backup on low prio.
Maybe you can give some advice on the sequence of the backup itself?
The sequence we use is to check db integrity first, then backup
database and finally save tlog. I wonder if the correct sequence in
sense of a full db backup must be to first save the tlog and then the
database? (In regards to performance / Timeouts this probably doesn't
matter at all I guess?)|||See in-line:
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:039df945-a971-4257-9eb8-64d119c0bc97@.r60g2000hsc.googlegroups.com...
> Dear Kelly,
> The strange thing is, that the opposite is the case. We are running 2
> backups each day, a nightly one to the same hard drive and a daily one
> to a different drive. The backup on the same drive causing much less
> problems - however this backup also contains less data than the one
> during the day.
What are these backups? Are they both FULL backups? Why would one have less
data than the other one? Why would you backup to the same disk as the data
in the first place? If the drive fails you loose the data and the backups.
Backing up across the network can be slower than backing up to disk even if
it is on the same drive.
> We already spent a lot of time in query optimization
> and are aware that the database I/O sometimes run on the physical
> limits, so we don't expect significant improvements by investing more
> time on improving the queries and the indexes.
If the database and queries are alreay highly tuned and you still run into
I/O issues you simply don't have the proper configuration to do the job.
There is no getting around that and there are no magical cures. You need to
upgrade the hardware.
>That's why we hoped for
> somekind of "on idle" backup option to start the backup on low prio.
> Maybe you can give some advice on the sequence of the backup itself?
> The sequence we use is to check db integrity first, then backup
> database and finally save tlog. I wonder if the correct sequence in
> sense of a full db backup must be to first save the tlog and then the
> database? (In regards to performance / Timeouts this probably doesn't
> matter at all I guess?)
I bet it is more the Integrity checks that do the damage more than the
backups. Do you only backup the log once a day? Log backups should be
independent of the FULL backups and done frequently. On average they are run
every five to fifteen minutes. Doing a log backup directly before or after a
FULL backup is pretty much useless.|||Dear Andrew,
The nighly backup contains only around 50% of the data because
recurrung clean-up jobs are running just before the backup. While the
daily backup is just a local one, the nightly one is a remote one.
However, I'll pick up your recommendation to backup the tlog more
frequently and to apply the integrity check only for the nightly
backup. Hope that helps. Thanks so far!|||Well that explains some of it. If you delete half the rows in the db the
transaction log backup will be huge. You might want to see if you can run
the cleanup jobs more often to spread the load over the entire day and keep
the logs to a smaller size.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:4c78e828-4c57-4c70-9262-486e1622ae5d@.e1g2000hsh.googlegroups.com...
> Dear Andrew,
> The nighly backup contains only around 50% of the data because
> recurrung clean-up jobs are running just before the backup. While the
> daily backup is just a local one, the nightly one is a remote one.
> However, I'll pick up your recommendation to backup the tlog more
> frequently and to apply the integrity check only for the nightly
> backup. Hope that helps. Thanks so far!|||Or possibly interleave tlog backups within the cleanup job itself. Delete
some fixed number of rows, backup tran, repeat until no rows to delete.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ummehUQMIHA.748@.TK2MSFTNGP04.phx.gbl...
> Well that explains some of it. If you delete half the rows in the db the
> transaction log backup will be huge. You might want to see if you can run
> the cleanup jobs more often to spread the load over the entire day and
> keep the logs to a smaller size.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "morebeer" <stefan.beer@.webergy.de> wrote in message
> news:4c78e828-4c57-4c70-9262-486e1622ae5d@.e1g2000hsh.googlegroups.com...
>> Dear Andrew,
>> The nighly backup contains only around 50% of the data because
>> recurrung clean-up jobs are running just before the backup. While the
>> daily backup is just a local one, the nightly one is a remote one.
>> However, I'll pick up your recommendation to backup the tlog more
>> frequently and to apply the integrity check only for the nightly
>> backup. Hope that helps. Thanks so far!
>