Transaction Log backups while Full/Diff backups are running

SQL_ME_RICH

Hello -

I modified a backup schedule to exclude my TLog backups while my Full backup is running on my main PROD databases. The reason is that I noticed that the only TLogs that were being included (if I for instance needed to do a restore) didn't include anything prior to the full and differential backups completing (Full at 12am and Diff being done at 10am local time).

My question is this...Should I just allow TLogs to go during my Full backup (which takes just under 4 hours, so it starts at midnight local time, and ends shortly before 4am local time), or is it better to have them shut off during that window when the Full is running? I have them going when the Differentials run during the day and evening, but any insight into this would be appreciated.

Many thanks!

ChrisAVWood

How big are your databases that they take 4 hours to backup and do you include the verify in this time?

There are ways to speed this up.

Chris

SQL_ME_RICH

ChrisAVWood wrote:

How big are your databases that they take 4 hours to backup and do you include the verify in this time?

The main database is 318gb and growing...The other 3 are about roughly 50gb combined. CHECKSUM and VERIFY are NOT being used for these backups, for the very reason of time constraints. We are able to get around this by doing daily restores of the backups from the night before into our Testing instance.

The main database takes 3hours and 43mins to do a Full backup. The other 3 combined take about an hour. All Fulls and Diffs (as well as TLogs for that matter) run at the same times every day/night.

ChrisAVWood

You must have some very slow disks. My biggest database that I use SQLBackup on is 260Gb with 180Gb of data and using compression level of 2 I get it backed up in 30 minutes. As this is SQL2005 on W2K3 I want to change this to 2 file output so that copying the backup file to another server puts less stress on the server. I should be able to bring this down further then.

One of the original benefits of using backup compression was to bring the time taken to run the backup down from what the native maintenance plan performed at.

Have you tried some of the many backup options to bring the backup time down?

Chris

SQL_ME_RICH

Hi Chris - No - not with Fulls. The only backup type that is getting compression (maximum compression = 4) are the Diffs.

I am not certain to the speed of the platters we are spinning on the internal array for which this db resides (I want to say they are at least 10k, but they could very well be 7200's as well - I doubt they are 15k's, and I am certain that they are not SSD).

I will investigate some of the options of compression and possibly splitting the backup into multiple files, but for now - the time is not a overwhelming concern.

Thanks for sharing your experiences with me though - I know now that I cannot run simultaneous backups of the same db (i.e. fulls/tlog, or diffs/tlog).

ChrisAVWood

Why aren't you compressing the Full backups? This is one of the main reasons to use SQLBackup. Unless you have inherited this situation.

Chris

SQL_ME_RICH

Apparently, the compression throws out the storage controller's deduplication algorithms. If all Full backups have the same base data, it will only take (on our SAN side) as much storage space as the delta between the Fulls.

It will show full size on the host side though, but that is what I am being told as to why we are not going to compress them.

ChrisAVWood

Sounds like splitting the backup to multiple files should be the way to go except for the first deduplication run. You should gain in the backup and lose it once in the replication.

Chris

SQL_ME_RICH

That's why this is such a tricky deal...We probably will just stick with a single file for the backup, and no compression. The system is 24/7/365, and they want 5 9's always (LOL!!!).

I love what I do! :-D

ChrisAVWood

You must have a pretty big log backup directly after the full as lots of data could be added after the full starts and before it ends. The backup has to contain only completed transactions so it can be used in a restore that doesn't compromise the data integrity.

I share your pain as who would want to be the one to cause a data problem by implementing a new scheme. I have been waiting to implement split backups on our big production database as we cross restore the backups to testing servers and don't want to cause problems.

Chris

SQL_ME_RICH

That has been the challenge for me (gauging the length of the Full backup to my one big db, and plan to kick TLogs off again not long their after).

I've gotten it down to about a 15/30 minute window of vulnerability, but that is a lot better than the 23 hour and 59 minute window of vulnerability they use to have.

Trust me - there is enough about this beast that has compromises in it that this has become more of a joy to manage than the other parts that - well - off-topic.

:roll:

ChrisAVWood

Just out of interest.

Your full backup must be 300Gb plus and that first log backup is how big?

Chris

SQL_ME_RICH

I probably won't know that until later tomorrow morning. I literally just went back to look at the log from the last time you asked this, and it ran the first TLog for today at 4am, but the Full had not finished until 4:15am. So the next one at 4:30am (TLog) was only 157.7mb.

Does that tell me that since it tried to run before the Full had a chance to complete, at 4am, that I did not get the full log that it otherwise should have?

ChrisAVWood

Lets see next week how big the first log backup is after the full has finished. Its a holiday here Monday so I'll check back on Tuesday.

Chris

SQL_ME_RICH

Chris (et al) -

We ended up ditching the Deduplication Algorithm on our iSCSI controller in favor of compression. The comparison in disk space savings truly is noticeable (and that's an understatement!)

I will have to see what my first Differential looks like come this weekend, but the level 4 compression mode is giving us anywhere between 80%-95% size savings. Pretty awesome, and I thank you for compelling me to look into it more!

ChrisAVWood

Compression is one of the main benefits to using SQLBackup and can improve thruput because it uses the disk buffering better than using small data.

Glad to here you are running better now.

Chris