June 1, 2011 at 12:34 am
Apologies for using the forum to avoid deep research ...
This might be interesting for some:
We have this 120GB database on a SAN (ESX 3.5). It's in "simple" recovery mode and we do a FULL backup every evening at 19:00 via a maintenance plan that does a few other things including : one task to back up this DB (with overwrite), a separate task to back up the system dbs, another task to do DBCC etc.
Doing a check of how long it takes to back up, I noticed that the bkp_start_date extracted from MSDB is identical to the task startup time-stamp in the maint plan log, but the backup_finish_date in MSDB is always earlier than the task finish time in the log. While in the past, the time difference was reasonable (cca 1 h), it has recently blown out to triple and quadruple the actual MSDB recorded time.
backup_start_datebackup_finish_dateElapsedMMElapsedHHBkpSizeBKPsizeMbbs_typeFileSizeFilesizeMb
2011-05-26 19:00:10.0002011-05-26 20:03:58.000631:03:48114077857280108793.12D137118482432130766.38
Task start: 2011-05-26T19:00:10.
Task end: 2011-05-26T23:02:47.
Can an "internals" expert explain with some degree of certitude what is actually happening while the full backup takes place? I used to think the backup file was being written directly to disk ... but is there some buffering somewhere to explain why MSDB records the backup of 100GB finished in 1 hour whereas the maintenance plan waits an extra 3 hours before it considers it done and moves on to do the DBCC task and the rest?
(Obviously we have other issues with ETL slowdowns, which is why I looked closer and discovered this anomaly ...)
TIA
June 1, 2011 at 1:12 am
Generally it depends on the server and how much processes & activities are running on it.
Check if backup verification option is on? Internally when backup is done than backup plan will start verifying the backup file. This may take some time. Here it's 3 hrs. Which is I guess too long but again check what other things are running symultaniously?
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
June 1, 2011 at 11:03 am
Yes,
check the maint plan task backing up, it sometimes has a "verify back up on completion" checked on,
which might increase the time after the actual backup..
It also depends on the otehr processes running at the same time...during backup...
You can check it up on ACTIVITY monitor.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
June 1, 2011 at 5:15 pm
free_mascot (6/1/2011)
Generally it depends on the server and how much processes & activities are running on it.Check if backup verification option is on? Internally when backup is done than backup plan will start verifying the backup file. This may take some time. Here it's 3 hrs. Which is I guess too long but again check what other things are running symultaniously?
HTH
Thanks, I am sure it does a backup / verify.
To verify, does it do a "read back" from disk?
That would imply the 100GB *.BAK file would be entirely hardened to disk - in my case, it goes to another virtual server probably on the same SAN ...
June 22, 2011 at 6:22 pm
Topic discussed in "Backup" - have posted additional stats and SAN defrag positive effects there - see Topic: "Backup Duration".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply