sql backup files: priority

We have 200 databases (totalling about 20GB), and counting. I use
maintenance plans for backup--seems very convenient, and I don't have to
change anything when we add another DB.
Every night we do disk-to-disk backup at 9:30 (which runs screamingly
fast--only 10 minutes--because we backup to another disk), and then a
separate disk-to-tape backup occurs.
The problem: With the increased workload leading up to Christmas, the users
are saying the system slows down too much during those 10 minutes. They are
trying to use the system during live phone calls, and the caller can get
impatient--so they end up writing things on paper, which has to be entered
into the DB later. (Lots of extra work because somebody else has to replay
the tape of the call to do this.)
My main question is: Is there a way I can tell the backup to run at a lower
CPU priority?
I don't think we can backup directly to tape (thus reducing the load),
because other backup software needs to use the same tape after the SQL
backup is done. I suppose I could backup to the SAME disk, which would
almost certainly get rid of the CPU contention, but then there would be disk
contention instead, which may not be any improvement (it could even be
worse). And I'd rather not move away from using the maintenance plans
because of all the automatic stuff they do for me, like logging, deletion of
old files on my desired schedule, etc. Moving the backup later into the
night is problematic because (1) other backups need to happen afterward, and
some of them run most of the night--and sometimes the verify phase continues
well into the next day; and (2) nightly maintenance jobs and DB loads also
run later in the night.I presume this backup that is causing slowdown is a FULL backup ?
Can you do it out of hours?
Do you use LOG backups or DIFFERENTIAL backups?
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Warren Odom" <no_spam@.no_spam.com> wrote in message
news:uvP%23vlOwDHA.3436@.tk2msftngp13.phx.gbl...
> We have 200 databases (totalling about 20GB), and counting. I use
> maintenance plans for backup--seems very convenient, and I don't have to
> change anything when we add another DB.
> Every night we do disk-to-disk backup at 9:30 (which runs screamingly
> fast--only 10 minutes--because we backup to another disk), and then a
> separate disk-to-tape backup occurs.
> The problem: With the increased workload leading up to Christmas, the
users
> are saying the system slows down too much during those 10 minutes. They
are
> trying to use the system during live phone calls, and the caller can get
> impatient--so they end up writing things on paper, which has to be entered
> into the DB later. (Lots of extra work because somebody else has to replay
> the tape of the call to do this.)
> My main question is: Is there a way I can tell the backup to run at a
lower
> CPU priority?
> I don't think we can backup directly to tape (thus reducing the load),
> because other backup software needs to use the same tape after the SQL
> backup is done. I suppose I could backup to the SAME disk, which would
> almost certainly get rid of the CPU contention, but then there would be
disk
> contention instead, which may not be any improvement (it could even be
> worse). And I'd rather not move away from using the maintenance plans
> because of all the automatic stuff they do for me, like logging, deletion
of
> old files on my desired schedule, etc. Moving the backup later into the
> night is problematic because (1) other backups need to happen afterward,
and
> some of them run most of the night--and sometimes the verify phase
continues
> well into the next day; and (2) nightly maintenance jobs and DB loads also
> run later in the night.
>
>|||"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:eim467OwDHA.2000@.TK2MSFTNGP11.phx.gbl...
> I presume this backup that is causing slowdown is a FULL backup ?
> Can you do it out of hours?
> Do you use LOG backups or DIFFERENTIAL backups?
Yes it's a full backup. We do log backups every half hour throughout the
day.
And my original post gave a lot of details why it would be a problem to
schedule the backup any later in the evening. (Although I did neglect to
mention this server is used 24/7.)
So can it be run at a lower priority or not?
-- Warren|||Backup isn't so much CPU intensive as it is disk I/O. So
if you're doing your disk backups to disks on the same
disk plane, you will see I/O issues, which affect SQL
Server. If you're backing up all 200 at once, you may
want to stagger them a bit ... that would certainly cause
contention.
Backing up to the same disk would be worse for I/O.
How are your disks set up?|||You have been offered some good suggestions already, I only want to answer
your concrete question:
> So can it be run at a lower priority or not?
No.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Warren Odom" <no_spam@.no_spam.com> wrote in message
news:uQfcAbPwDHA.3196@.TK2MSFTNGP11.phx.gbl...
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:eim467OwDHA.2000@.TK2MSFTNGP11.phx.gbl...
> > I presume this backup that is causing slowdown is a FULL backup ?
> > Can you do it out of hours?
> > Do you use LOG backups or DIFFERENTIAL backups?
> Yes it's a full backup. We do log backups every half hour throughout the
> day.
> And my original post gave a lot of details why it would be a problem to
> schedule the backup any later in the evening. (Although I did neglect to
> mention this server is used 24/7.)
> So can it be run at a lower priority or not?
> -- Warren
>

I've searched enough to know that this ability probably doesn't exist
in our current setup, thus the reason I'm coming to the community now.
When doing a database backup in Enterprise Manager (SQL Server 2000
sp4), is there a priority level setting so that I can put less load on
the CPU? Our small office uses one server for everything and when I do
a 12:00 pm full backup of our live DB, the server slows to a crawl for
about 4 or 5 minutes. I'd like to see the backup take longer but put
less load on the server so we can to use it. If this setting doesn't
exist, I may need to explore another backup method...but that's another
topic for another day.

Thanks,

Gabegabe101 (gabe101@.gmail.com) writes:

Quote:

Originally Posted by

No, there is no such setting. I don't really know when 12.00 pm is, but
I guess it's at noon, since it causes problems.

It may be a better to idea to schedule the full backup for off-hours,
and only take log backups during the day.

Then again, a full backup slowing the server to a crawl, does not seem
normal to me. Could be that your hardware is a bit thin. Do you take
the backup to local disk or a network drive?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

>
No, there is no such setting. I don't really know when 12.00 pm is, but
I guess it's at noon, since it causes problems.

Thank you and yes, you are correct...it's noon.

Quote:

Originally Posted by

It may be a better to idea to schedule the full backup for off-hours,
and only take log backups during the day.

We currently do a full back up every night and no log back-ups. We're
on 'Simple' recovery model and plan to just restore to the most recent
full backup during a crisis. We can afford this "luxury" because we're
a small office with few transactions during the work day. This is why
I chose to do one more live backup of our company database at the noon
hour...cutting potential losses by 50% (the small losses that they
would be).

Quote:

Originally Posted by

Then again, a full backup slowing the server to a crawl, does not seem
normal to me. Could be that your hardware is a bit thin. Do you take
the backup to local disk or a network drive?

We have a good server but it's our only server (Dual Xeon 3.0, 4GB RAM
and 4, 10K RPM RAID-5 drives). Originally, it was just SQL Server and
Symantec AV...now it's also Exchange. The backup is being stored to
the Data partition on that same server's RAID drives.

Thanks Erland.|||gabe101 (gabe101@.gmail.com) writes:

Quote:

Originally Posted by

One alternative you may want to investigate is to do a differntial
backup. That may tax your server less.

Quote:

Originally Posted by

Erland Sommarskog wrote:
>
We have a good server but it's our only server (Dual Xeon 3.0, 4GB RAM
and 4, 10K RPM RAID-5 drives). Originally, it was just SQL Server and
Symantec AV...now it's also Exchange. The backup is being stored to
the Data partition on that same server's RAID drives.

Argh, you're really stressing that machine.

Try backing up over the wire to a UNC share on another machine. You are
almost definitely stressing the disk I/O system on this box.

A backup on the same machine won't do you much good if say the RAID
controller fails.

Also, you may still want to look at transaction log backups, they'll
generally be much smaller and you can do more often w/o impacting
performance nearly as much.

Quote:

Originally Posted by

>
Thanks Erland.
>

|||Greg D. Moore (Strider) wrote:

Quote:

Originally Posted by

Argh, you're really stressing that machine.

Yeah, I agree. Unfortunately, it's not in the budget for us to expand
at this time. I will say this though, to me (and my mere two years
experience) it seems to be running very stable. Since we only run with
10 users, I've set SQL Server's memory allocation to 600MB and set
SBSMONITORING to 100MB, Exchange is being kind and staying between 575
- 600 MB and Symantec is occupying another 400 - 500. All together
I've still got about 800 - 1,000 MB of free RAM. My server report
shows an average of 18% busy disk time and both CPU's (shown as 4)
averaging about 8-10% use. Again, I only assume this is good because
I'm new to IT. I just got my degree two years ago. Would you say
our server is doing 'Good', 'Satisfactory' or 'Poor'?

Quote:

Originally Posted by

Try backing up over the wire to a UNC share on another machine. You are
almost definitely stressing the disk I/O system on this box.
>
A backup on the same machine won't do you much good if say the RAID
controller fails.

I sheepishly have to admit my inexperience again and say that this
makes perfect sense. We are currently saving the noon backup on the
same system and I'll save the BAK file to another box starting now.
Thanks.

Quote:

Originally Posted by

Also, you may still want to look at transaction log backups, they'll
generally be much smaller and you can do more often w/o impacting
performance nearly as much.

My only concern here comes back to my inexperience with transaction
logs. Doing a restore through the Enterprise Manager is incredibly
simple with a full .BAK file. I've not done a transaction log restore
and have been told it's much more complex. I agree with you and Erland
here, but given our current situation, a restore from a full backup
file and then manually entering the day's transactions may ultimately
be just as simple to accomplish.

Thank you both for your comments and if you have anything more to say,
I'm still listening (and learning).|||Yes, this is a response to my own response...

;-)

gabe101 wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Try backing up over the wire to a UNC share on another machine. You are
almost definitely stressing the disk I/O system on this box.

A backup on the same machine won't do you much good if say the RAID
controller fails.

>
I sheepishly have to admit my inexperience again and say that this
makes perfect sense. We are currently saving the noon backup on the
same system and I'll save the BAK file to another box starting now.
Thanks.

I suddenly have a reason for concern...

I transferred our existing 'Noon' backup file accross the network to
the share on another box. It took 8 minutes and 17 seconds and the
backup write process only takes between 3 and 4 minutes. Is this going
to cause a problem when the server is doing the backup and transferring
at the same time? Is it actually going to write to temporary location
on the server before making the transfer anyway? If so, that would
defeat the purpose for transferring to the other box. Again, any input
is appreciated.|||gabe101 wrote:

Quote:

Originally Posted by

Yes, this is a response to my own response...
>
;-)
>
gabe101 wrote:
>

Quote:

Originally Posted by

Quote:

Originally Posted by

>
I suddenly have a reason for concern...
>
I transferred our existing 'Noon' backup file accross the network to
the share on another box. It took 8 minutes and 17 seconds and the
backup write process only takes between 3 and 4 minutes. Is this going
to cause a problem when the server is doing the backup and transferring
at the same time? Is it actually going to write to temporary location
on the server before making the transfer anyway? If so, that would
defeat the purpose for transferring to the other box. Again, any input
is appreciated.

Depends on your network of course but it doesn't surprise me that
sending a large file over a network would take longer than the local
backup, especially during the day. Have you considered getting a tape
drive to do your backups - they're not all that expensive, certainly a
lot cheaper than losing your data would be :)

Also, as someone else mentioned, consider doing a differential backup
during the day rather than the full backup. It's just one more step if
you need to restore, and it should be much smaller and faster than your
full backup.|||gabe101 (gabe101@.gmail.com) writes:

Quote:

Originally Posted by

Note that what I suggested was *not* a transaction log dump, but a
differential backup. I will have to admit that I've never taken a
differential backup, nor restored from one, but I can't imagine
that it's that tricky. You would need to play with it on a test
box, so that you are confident with the process when the disaster
strikes.

A differential backup only includs the changed pages since the last
full backup, so it would be a lot smaller and thus tax the system
less.

Quote:

Originally Posted by

gabe101 (gabe101@.gmail.com) writes:

Quote:

Originally Posted by

>
Note that what I suggested was *not* a transaction log dump, but a
differential backup. I will have to admit that I've never taken a
differential backup, nor restored from one, but I can't imagine
that it's that tricky.

Not tricky at all. Restore the full backup, then restore the diff.

Sunday, March 25, 2012

Backup slowing down systemcan it run at lower priority?

Sunday, March 11, 2012

Backup Priority level

sql backup files

Blog Archive

About Me