Backup slows system down too much
-
quote:
Have you run Performance Monitor to track the resource's usage in CPUs, Disk I/O, Network etc? What are counter numebrs if you had?I ran the task manager, and it showed something like 90% CPU usage during the backup, which is to be expected since we're backing up to a different disk and therefore have essentially no disk contention.
I have not collected other statistics, partially because I know the load on the machine, disk, and network is low at that time (except for the backup), and also because I've always had trouble with the performance monitor--for instance, I'll set up a group of stats to monitor, and I'll tell it to save those settings for future use, and then when I come back those settings are gone. This gets very old very fast.
-
quote:
I have not collected other statistics, partially because I know the load on the machine, disk, and network is low at that time (except for the backup), and also because I've always had trouble with the performance monitor--for instance, I'll set up a group of stats to monitor, and I'll tell it to save those settings for future use, and then when I come back those settings are gone. This gets very old very fast.Are you using Counter Logs or just System Monitor? I've always found the logs to work better.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
quote:
Are you using Counter Logs or just System Monitor? I've always found the logs to work better.The only System Monitor I know of is a Windows 9x program. Since this is W2K, I run the Task Manager instead. I'm not familiar with Counter Logs--is that a Windows thing or a SQL Server thing?
-
How about apreading the 200 databases across more than one instance of SQL2K? You could still use the "backup every database" option in the maint plan but have multiple plans, one for each instance. If you have 4 instances with 50 databases each (assuming a uniform size) and stagger the backups for each instance you should only have a slowdown for 120-180 seconds each time a instance runs its maint plan. You don't have to buy another box or deal with asking for more money to implement this. As you add more databases add an instance for every few dozen databases so the maint plan will always fall into an acceptable range of "slowdown".
What do you think?
-Isaiah
Edited by - iadams on 12/23/2003 10:28:59 AM
-Isaiah -
OK, I just checked again, and it looks like W2K calls it System Monitor instead of Performance Monitor. (I guess it was Performance Monitor back in NT--the W2K shortcut now just says Performance so that doesn't help.)
And now, expanding a tree of options, I see an entry for Counter Logs, but no, I'm not familar with how to use that. As I mentioned in my earlier post, I've had some troubles trying to use Performance Monitor / System Monitor.
-
You use counter logs in much the same way the NT performance monitor logged counters to a file. The only difference is that you've got a few extra options.
To create a new Counter Log,
Double-click Performance Logs and Alerts, and then click Counter Logs.
Right-click a blank area of the details pane, and click New Log Settings.
In Name, type the name of the log, and then click OK.
On the General tab, click Add. Select the counters you want to log.
Change the default file name, type and size on the Log Files tab
Change the Start and Stop times on and the Schedule tab.
When you click 'OK' you'll have a new counter log which you can start by right-clicking on it.
To view the logged data. Click on 'System Monitor' and click the 'View Log File Data' button (It's the one that looks like a database). Choose the file you created above and add counters just like normal.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface -
quote:
How about apreading the 200 databases across more than one instance of SQL2K? You could still use the "backup every database" option in the maint plan but have multiple plans, one for each instance.A unique idea, that had never occurred to me. (I've never used multiple instances.) I thought about your idea, but I'd have to replicate all the maintenance jobs for every instance, and then whenever one is changed, update all the others. Also there's a limit to how far out I can spread the jobs and keep within all the other constraints I have.
(It turns out our DBs vary greatly in size; the largest one accounts for 1/3 of our space, and the next 2 or 3 account for another third--but I don't think that would affect the basic idea.)
For now at least, after analysis, I have found another time window (5 AM) for the disk-to-disk backup that is significantly less busy--and therefore preferable. I've thought through the failure scenarios enough to convince myself that I can still restore properly if something goes wrong, even though the disk-to-tape backup still runs at 10 PM.
I think this strategy will work for us.
-
quote:
You use counter logs in much the same way the NT performance monitor logged counters to a file. The only difference is that you've got a few extra options.To create a new Counter Log, ... [etc.]
Thanks for the info ... I'll keep it in mind, although as I tried to indicate earlier, I don't know what any further performance stats would buy me in this particular situation. It seems to be pretty simple, where I don't think I need more stats to know that CPU contention is high, and disk contention is relatively low.
-
I appreciate your problems with a couple of hundred databases, but the thought of trusting the backup of that lot to one maintenance plan gives me the screaming heebie jeebies.
One problem that I have found with maintenance plans is that if there is some sort of failure with the processes that the plan is supposed to run then the plan fails.
If it fails on database 10, then 190 databases don't get backed up.
How do you deal with such an occurrence?
========================
He was not wholly unware of the potential lack of insignificance.
-
No, it's not the case. As a plan it shown failed, but all databases with no problem will be backed up. Only the databases with problem will not have backups.
Robert
-
quote:
If it fails on database 10, then 190 databases don't get backed up.How do you deal with such an occurrence?
If problems occur with the "big" backup(whether or not your specific scenario is possible), I still have the twice-an-hour disk-to-disk transaction logs to fall back on, plus the most recent disk-to-tape backup--but I may not even need the tape, since the previous full disk-to-disk backup might still be on the disk at that point.
ANY automated procedure (whether a maintenance plan or not) that backs up all the databases would have to contend with the same possibilities.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply