| Author |
Topic |
|
Sarat
Constraint Violating Yak Guru
265 Posts |
 Posted - 2004-02-24 : 16:17:09
|
| Hi,I realized that from last 3 weeks, the nightly backups (9 PM) are taking over an hour to complete. It typically takes 20 mins or so. There is almost no one connected to the database after 7 PM. I am also doing 15 mins log backups which appear to be running fine. Nothing was really changed at the db server side. I will run perfmon tonight but do you have any ideas what could trigger this. The only other thing I noticed is that in last 3 weeks. I got 'Scheduler appears to be hung, UMS error twice!' I went throught the material in the web site but there isn't really a remedy to it! Seems like they are connected but what do I do next? Is there a way (SQL or some SP) which will tell if anything changed wrt server/hardware in few weeks?[url][/url]http://support.microsoft.com/default.aspx?scid=kb en-us 319892We have SQl Server 2000 SP3a on Windows 2000Thanks,Sarat.**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-24 : 16:22:41
|
| Are you using maintenance plans for your backups? If so, that's where the bottleneck most likely exists.Tara |
 |
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2004-02-24 : 16:34:40
|
| Yes, As a matter of fact I am! But I have always used the same. Why is it a problem now? I do have a script to do a backup job which I use seldom for other purposes, I will test that tomorrow.-Sarat**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-24 : 16:51:39
|
| The maintenance plan does other things than just a backup. If you told the maintenance plan to delete the files if they are older than a specific data, then it does that work after the backup completes. There are also other things that the maintenance plan does. Such as: Does the plan verify that the backup is complete? Does it check the integrity of the database prior to running? All of these things can add to the time.I do not know why it is a problem now nor do I 100% know that the plan is the problem. What can be done to verify though is run the backup command in Query Analyzer to see how long it should take now and compare it to the plan.Tara |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2004-02-24 : 16:54:18
|
| Check this website: Similar to what Jasper posted before: Makes sense!http://support.microsoft.com/default.aspx?scid=kb;en-us;810885Cause:ALTER DATABASE ADD FILE.CREATE DATABASE.AUTOGROW of files (explicit or implicit).Can a file mean LDF also? This changes now and then based on indexing/defragmentation. I haven't really altered/created a db. There is also no change in the MDF file for last 2 months.-Sarat**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-24 : 16:58:16
|
| ADD FILE means NDF (secondary MDF in other words). ADD LOG FILE means LDF.So are you receiving this error? If not, that article doesn't apply.Tara |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2004-02-24 : 18:39:35
|
| Thank You! I received this error in last 4 weeks. The slowness in backup jobs is being seen from last 2 weeks._Sarat**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2004-03-05 : 12:11:47
|
| OK, Finally we figured why it almost snowed (hail storm) in Phoenix, Scottsdale, Tempe etc (AZ) yesterday!Just joking - we figured out the long running backup jobs issue - our server admin had changed the backup disks (some ATA thing) to accomodate more backup files and these disks are huge but slow.Thanks,Sarat.**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
|
|
Backup jobs taking too long to finish.