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.
| Author |
Topic |
|
franco
Constraint Violating Yak Guru
255 Posts |
 Posted - 2011-08-30 : 04:23:20
|
| My environment:SQL Server 2008 standard Edition 32bit on Windows Server 2008 Standard Edition 32bit.The server is hosted by one ESXi node.Backup strategy:Full backup every day at 09.00 PMDifferential backup every day every 2 hours between 05.10 AM until 09.10 PMLog backup every day every 15 minutes between 05.05 AM until 08.05 PMTheses backup files are then backed up on Tape during the night.I know that the VMWare's administrator has implemented VEEAM Backup in order to take snapshots of this server but I have noticed something strange:I have noticed a new messages in SQL Server errorlog exactly at the moment when Veeam takes the backup:I/O is frozen on database model. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.I/O is frozen on database msdb. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.I/O is frozen on database master. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.I/O is frozen on database xxxxxx. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.and thenI/O was resumed on database master. No user action is required.......So there are no errors but at least something is strange to me.I also noticed that the backup performed with Veeam writes information in msdb database.i.e.The table msdb.dbo.backupset contain records with this kind of informations:is_snapshot = 1is_copy_only = 1The table msdb.dbo.backupmediafamily contain strange information in some fileds:- for sql native backup I have a situation like this:physical_device_name = C:\Folder\subfoder\db_xx_xxxx_FULL_20110809_213000.bakdevice_type = 2physical_block_size = 512- for Veeam backup I have this one:physical_device_name = {9D96FB8D-87FA-4B5B-9FC3-CEA7158FE10B}4device_type = 7physical_block_size = 1024What is your suggestions for this kind of a situation?My concern is about the fact that Veeam writes strange information in msdb database.Could you please give me a feedback?Thank you very much.Best regards. |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-08-30 : 04:34:03
|
| Can't help you with the Veeam backup I'm afraid, but:"Log backup every day every 15 minutes between 05.05 AM until 08.05 PM"Do you not have any processing, in particular Housekeeping, that runs outside those hours?Index rebuild (for example) is likely to generate significant amounts of LOG transactions, and will unecessarily extend your LOG file if there are no log backups running at the time.Differential backup every 2 hours seems overkill to me - given that you can recover from the Log backups, but that may be to do with your recovery time for some SLA requirement?If not (e.g. its there because "someone sometime ago put it in place") I would consider ditching that, or replacing it with a single "mid day" Differential backup.How big are the differential backups during the day? e.g. comparing the one at 07:10 AM with the last one of the day.And how big are the transaction backups?Unless the transaction backups are massive I would not expect their recovery time to be significantly different to:Restore full backupRestore differential backupRestore last few quarter-hourly Tlog backups |
 |
|
|
franco
Constraint Violating Yak Guru
255 Posts |
Posted - 2011-08-30 : 09:56:11
|
quote:
Do you not have any processing, in particular Housekeeping, that runs outside those hours?Index rebuild (for example) is likely to generate significant amounts of LOG transactions, and will unecessarily extend your LOG file if there are no log backups running at the time.
Yes we have index rebuild and other stuff but the db is only 80MB so there is no great impact.quote:
Differential backup every 2 hours seems overkill to me - given that you can recover from the Log backups, but that may be to do with your recovery time for some SLA requirement?
This is just an experiment, maybe I'll change it in the future.quote:
How big are the differential backups during the day? e.g. comparing the one at 07:10 AM with the last one of the day.
Dimension is 6MB at 7AM and 1MB at 9PM, (full backup is taking place at 8PM).quote:
And how big are the transaction backups?
150KB - 300KB MaximumFranco |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-08-30 : 13:07:36
|
I don't think the Differential backups are gaining you anything. 300KB log backups will restore very quickly - even if you hsve having to restore 4-per-hour for an 8-hour-period."Yes we have index rebuild and other stuff but the db is only 80MB so there is no great impact."Check how big the [first] TLog backup after your index rebuild is - in particular after an Index Rebuild that has had some work to do (lots of database changes in the previous period) in case it is using reorganise rather than re-index. If that is significantly more than your normal Tlog backups I would recommend that you leave Tlog backups running during index rebuild.FWIW we run our Tlog backups 24/7. The risk of something going wrong with the extra complexity of only doing them during office hours outweighs them "just running all the time". But its horses-for-courses of course  Hopefully someone can answer your main question. |
|
|
|
franco
Constraint Violating Yak Guru
255 Posts |
Posted - 2011-08-31 : 02:11:43
|
Kristen, thank you very much for your suggestions.I also hope that someone can answer to my main question  Have a nice day.Franco |
|
|
|
|
|
|
|
|
Veeam and SQL Server backup integration