Backup time increasing - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Backup time increasing

Author

Topic

Ankit Mathur
Starting Member

24 Posts

Posted - 2007-10-29 : 02:32:49

Hi,

I've been noticing that while taking a snapshot backup in MSSQL 2000 the time taken to backup the database completes in around 10-12 minutes for a approx. 16GB DB but still time notified in comepleting the job in job history is around 40 minutes.

This is happening not daily but once or, twice a week while normal reported time taken to complete the job is around 18-20 minutes.

I reckon that could be due to excessive time taken while verifying the backup.

What I want to know is whether I am right in my opinion of the cause or, there could be something else also lurking & escaping from my attention?

What else could be possible reasons & ways to check & resolve them?

How can I reduce the overall backup time including verifying backup?

Would appreciate if somebody could help me identify the problem.

Thanks
Ankit Mathur

Kristen
Test

22859 Posts

Posted - 2007-10-29 : 05:55:41

What do you mean by "snapshot backup in MSSQL 2000" please?

Ankit Mathur
Starting Member

24 Posts

Posted - 2007-10-29 : 09:04:52

Well actually nothing unusual but a full backup which I believe is a snapshot of the current status of DB when it starts.

One can rightly term it as Full Backup also.

Kristen
Test

22859 Posts

Posted - 2007-10-29 : 09:54:24

OK, so its just a Full Backup. The reason I asked is there is a specific "Snapshot Backup" in MSSQL 2005, which is not available in SQL 2000)

Is this perhaps part of a maintenance plan that does other things too - such as Reindex the tables?

Kristen

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-29 : 23:13:54

Maybe network related if backup to remote location.

Ankit Mathur
Starting Member

24 Posts

Posted - 2007-11-01 : 00:59:47

Hi Kristen & rmiao,

This is a standalone job being executed every 10AM & 5PM daily.

With this may I reiterate the problem.

The problem is Backup time taken & reported time that varies in job History for 10AM Backup. While for 5 PM backup its doing perfectly fine.
In 10 AM Backup while the time taken for backup is around 10-15 mins only (approx.) but sometimes the reported time goes thrice as much to about 30-45 mins.

So I wanted to know the root cause of such a deviation.

As for rmiao question: The backup is being done first on local disk itself & then after about an hour its copied to another remote location.

Any idea what all settings should I watch out for or, perhaps focus on some unusual activity that's causing this.

I've been trying to pin-point it to some issue but nothing concrete I could forge upon.

Thanks all who are replying to my question.

Ankit Mathur

Kristen
Test

22859 Posts

Posted - 2007-11-01 : 07:58:16

"The problem is Backup time taken & reported time that varies in job History for 10AM Backup"

What figure are you using for "time taken" & "reported time"?

If its, say, the Time Stamp on the BAK file that may mean that the job had other things to do before it started.

It might just be that the system is very busy sometimes at 10AM ... or something else is blocking the backup (usually another backup IME)

Kristen

Ankit Mathur
Starting Member

24 Posts

Posted - 2007-11-01 : 08:15:49

I'm taking the reported time from the JOB HISTORY as its present with respect to the specified job in Enterprise Manager -> Jobs

As far as system being busy is concerned that's what I want to know. Why sometimes its busy & sometimes not. There's no other job scheduled even around the same time except Transaction log backup which I take every 5 minutes.

So I reckon the root cause would somewhere lie in Verification of Backup for the job to take so much to mark itself as successfully completed or, if it happens to be another cause it should be in my notice.

Somehow everytime I decided to execute the job in my presence it has completed in just 10-15 minutes. So I am unable to pin-point the reason or, usage of server resources in my presence.

Hence, the question that what other measures I can undertake.

Kristen
Test

22859 Posts

Posted - 2007-11-01 : 08:20:21

"except Transaction log backup which I take every 5 minutes"

Might be worth checking the timestamp on the TLog backup just before a "slow" Full backup. Maybe there was an overly-long Tlog backup that blocked the Full backup ?

Kristen

Ankit Mathur
Starting Member

24 Posts

Posted - 2007-11-01 : 08:37:05

We did not get any TLogs during that duration.

Perhaps they all got blocked all this time & the job never got executed.

Kristen
Test

22859 Posts

Posted - 2007-11-01 : 10:19:37

Was there a TLog backup 5 minutes BEFORE the Full Backup should have started?

I would expect the FULL Backup to block the subsequent TLog backups, so if there is no TLog backup between 10AM and 40-ish minutes later it does indeed sound like the FULL Backup was running all that time.

Kristen

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-01 : 23:27:18

Check network traffic if backup to remote location, check disk i/o if backup to local disk.

Ankit Mathur
Starting Member

24 Posts

Posted - 2007-11-02 : 02:37:40

Hi Kristen & rmiao,

Here are the timestamps of Transaction Logs during the long backup period.

tlog_200710290952.TRN
tlog_200710290957.TRN
tlog_200710291040.TRN
tlog_200710291042.TRN

Rmiao, how do I checkup disk I/O ?? Can you help me with that. I have no idea how to go about it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-02 : 02:42:52

quote:
Originally posted by Kristen

I would expect the FULL Backup to block the subsequent TLog backups

Transaction log backups do not get blocked by full backups. Our full backups kick off at 00 and our tlog backups kick off at 00,15,30,45. All 4 tlog backups are successful and have data in them.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Ankit Mathur
Starting Member

24 Posts

Posted - 2007-11-02 : 02:50:24

Hi tkizer,

Can you throw some light on what could be the reason for long full backups of mine ?

I'm looking forward to help from all fellow members.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-02 : 02:59:44

The verify backup portion is useless. It does not guarantee that the backup is good. I don't ever use this option as a result. See if removing the option fixes your problem.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2007-11-02 : 04:29:25

...
tlog_200710291042.TRN

No Tlog backup at 10:47? That would be before the 10:00 full backup should have kicked off? perhaps something was running that prevented that starting as well as the 11

"Transaction log backups do not get blocked by full backups. Our full backups kick off at 00 and our tlog backups kick off at 00,15,30,45. All 4 tlog backups are successful and have data in them."

Assuming your Full backup starts first (given that both are at 00:00) the TLog backup will not start until the Full backup has finished - so the timestamp on the Tlog backup will be a fair bit after 00:00.

If the Full backup does not finish until after 00:15 then the 00:00 Tlog backup will run next, its finish time will be after 00:15, then Tlog backup scheduled for 00:15 won't run and the next Tlog backup will be triggered at 00:30

At least, that is how it seems to behave for me on SQL 2000.

Kristen

Kristen
Test

22859 Posts

Posted - 2007-11-02 : 04:31:57

By the by, I do perceive this as being an issue for a backup Sproc that "walks round" each database making TLog backups. If one of those backups is doing a lengthy Full backup then that will cause the Tlog backups of all subsequent databases to be delayed

I live with that at the moment, but I think that my Sproc ought to schedule one-time-jobs so that all the TLog backups are run without being dependent on each of them being able to proceed, nor take very long!

Kristen

Ankit Mathur
Starting Member

24 Posts

Posted - 2007-11-02 : 04:43:17

Afterwards TLogs are on regular schedule with no issues

tlog_200710291047.TRN
tlog_200710291052.TRN
tlog_200710291057.TRN
tlog_200710291102.TRN
tlog_200710291107.TRN

Problem is during that duration only.

Anyhow, now let's head back to the main issue of What Could be causing such a delay in Full Backup.

Any suggestions w.r.t which parameters or, something else I should be watching.

Thanks
Ankit

Kristen
Test

22859 Posts

Posted - 2007-11-02 : 05:21:43

Here's the sum total of what I can think of:

Server busy / disks slow / etc. Backup takes longer than normal. Clearly it is CAPABLE of running faster, because it does so at other times.

Something is blocking the backup (I don't know what can, probably nothing I would expect, but maybe a big ROLLBACK or COMMIT could?)

Is the tlog_200710291047.TRN (i.e. the one after the lengthy backup) especially large? (I mean larger than just because it didn't run for 40 minutes). That might imply some massive transactions were going on ...

That's it, but I'd be interested if folk think there could be other reasons.

I also don't know how to diagnose it. If you have something that records a DIR to a file every minute, say, would that show the rate of size growth of the BAK file, and perhaps give a clue whether the file is writing continuously, but slowly, for 40 minutes, or whether it all happens in the last couple of minutes? (But not sure what that will tell you - maybe it is blocked, maybe it is just doing "preparation")

Kristen

Haywood
Posting Yak Master

221 Posts

Posted - 2007-11-02 : 10:07:20

quote:
Originally posted by Kristen

By the by, I do perceive this as being an issue for a backup Sproc that "walks round" each database making TLog backups. If one of those backups is doing a lengthy Full backup then that will cause the Tlog backups of all subsequent databases to be delayed

I live with that at the moment, but I think that my Sproc ought to schedule one-time-jobs so that all the TLog backups are run without being dependent on each of them being able to proceed, nor take very long!

Kristen

You can setup individual alerts on the databases to issue backup log commands when X % full...

I have one t-log backup procedure that walks around the databases, but it is also able to be called for an individual database by an alert or ad-hoc.

In the end, I still only have one procedure to maintain and one 'main' backup t-log job along with individual jobs per database that are assocciated with individual alerts. Creation of the alerts is fairly easy to automate too. It takes about two seconds for me to setup the whole thing on any server.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources