| Author |
Topic |
|
SQLCode
Posting Yak Master
143 Posts |
 Posted - 2006-04-25 : 15:05:15
|
| Hello,We have SQL 2000 Enterprise on a cluster. We are experiencing strange things on the server.It is taking 7-8 times more time than what it used to take, for a back up. Would you you know why this may be happening?It is taking that long for all the dbs not only on that server or cluster node but on both nodes, all dbs of the cluster. This is being going on for a month now.Pls. help, it our prod environment. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2006-04-25 : 15:34:59
|
| Backing it to disk, nothing changed as far as I know or the infrastructure team claims, DBmaintenance plan like always.So? what next?The dbs are between 40-80G |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-04-25 : 15:49:22
|
| How long is it taking to back them up? How long does it take to backup a databas to disk using BACKUP DATABASE?Tara Kizeraka tduggan |
|
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2006-04-25 : 15:51:43
|
| used to take 30 min was <1 hr at the most, suddenly jumped to 6,7 even 12hrs sometimes. Mostly 7 hours now i guess |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-04-25 : 15:56:46
|
| I'd suggest backing up each of the databases using BACKUP DATABASE in Query Analyzer. Compare the times to your maintenance plan.For those sizes of databases, I'd highly recommend using SQL Litespeed instead. It'll save you 75% disk space and backup time.Tara Kizeraka tduggan |
|
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2006-04-25 : 16:04:59
|
| How would I sell it to my management? What might be causing it? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-04-25 : 16:11:55
|
| You'll have to sell it to your management if you are interested in the product.We don't know what's causing your problem, which is why I've asked a lot of questions and for you to run backup database in QA.Tara Kizeraka tduggan |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2006-04-26 : 00:55:22
|
| You didn't answer any questions. You just said I guess a lot. What is the disk IO and disk queue lengths on the server during the backup window? Specifically, what is the disk write queue length? Are there any other counters messed up during this time? Do you have any large batches that occur at the same time? How big are the database backups? What kind of disks are they backing up to? Are there any errors on the disks, array, or controllers? Basic troubleshooting will help all of us a lot, especially your team.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2006-04-28 : 09:37:31
|
| Still trying to find answers to all your questions. Will let you know after our meeting we have here. One thing though is that we had a bad sector in one of the drives of the cluster, when you mentioned the errors on disks or arrays. But that was replaced. point is can/had it spread to other drives? Doesn't look like it should have.At least there are no such errors. As far as queue length during backup is concerned, I still have to find out.It is running all night (10 -12 hrs for only), the sizes are 40- 100 G. There are about 10-12 dbs on the cluster.Thanks for all the help and guidance.Thanks for the help and advice. |
|
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2006-04-28 : 12:19:04
|
| An update on the situation is that the maintenence plan of backing up the db used to take .5 hrs and hence the db optimization plan was set within 1 hour of the backup.The trans logs happen every hr. They are all running into one another as the db size grew and was not finishing before the optimization job and this noth finishing before the t log job.they are all finishing at the same time (?)Could that be the reason for all the delays as they are all waiting for each other? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-28 : 13:18:32
|
| "Could that be the reason for all the delays as they are all waiting for each other"Definitely worth delaying the optimisation plan (or even disabling it for a night to see what difference that makes to backup duration)On DBs that big you might want to consider using something more crafted to your needs, than the Optimization Step in Maintenance Wizard [if I've understood correctly that that is what you are currently using].For example, its probably doing an INDEX REBUILD - which will completely copy the indexes to a different part of the disk, and jack up your Log Space requirements hugely - and might actually be carrying the best part of the size of whatever is your largest table as "slack" in the MDF data file -which in turn will jack up the length of your Recovery time [during a restore] to accommodate creation and initialisation of the slack space.Enterprise Manager : RightCLick the Database : All Tasks : ShrinkWhat size is the "Allocated" and "Space free"?Do NOT press [OK] - press CANCEL instead please!!The default settings for the Optimise will also change the fill factor to 90% - that's pretty much a disaster for incremental PKs - the rebuild is going to shuffle all the 100% fill stuff around to get it down to 90%, never have any new keys to fill up the remaining 10%, and the index lookups are going to take longer because the keys are more sparse than they need to be.And hopefully you haven't got "Make minor repairs without asking anyone" turned on ... that could be submarine-ing some serious problems!Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-28 : 15:25:25
|
"Why are you even running the optimizations job? "Hehehe ... I'm getting soft in my old age, aren't I?  |
|
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2006-05-01 : 11:22:32
|
| It has been set up since the inception of the program. It was not done by a DBA. Now when we have grown from few MB to ~100 GB, it is breaking things. We did disable the opt. jobs, will write our own scripts for that. But this did not help the back ups significantly.It still took a long time, like 6-7 hours.Looks like there is a bad sector on hard drive. But the infrastucture team does not think it can cause the delays as the OS will reject that sector as soon as it finds it. |
|
|
|
|
Backup delays