Red Gate forums :: View topic - Transaction Log backups while Full/Diff backups are running
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 7
SQL Backup 7 forum

Transaction Log backups while Full/Diff backups are running

Search in SQL Backup 7 forum
Post new topic   Reply to topic
Jump to:  
Go to page 1, 2  Next
Author Message
SQL_ME_RICH



Joined: 08 May 2012
Posts: 64

PostPosted: Thu May 17, 2012 9:30 pm    Post subject: Transaction Log backups while Full/Diff backups are running Reply with quote

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!
Back to top
View user's profile Send private message
ChrisAVWood



Joined: 18 Dec 2007
Posts: 308
Location: Edmonton, Alberta, CANADA

PostPosted: Thu May 17, 2012 11:03 pm    Post subject: Reply with quote

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
_________________
English DBA living in CANADA
Back to top
View user's profile Send private message
SQL_ME_RICH



Joined: 08 May 2012
Posts: 64

PostPosted: Thu May 17, 2012 11:17 pm    Post subject: Re: Reply with quote

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.
Back to top
View user's profile Send private message
ChrisAVWood



Joined: 18 Dec 2007
Posts: 308
Location: Edmonton, Alberta, CANADA

PostPosted: Fri May 18, 2012 3:27 pm    Post subject: Reply with quote

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
_________________
English DBA living in CANADA
Back to top
View user's profile Send private message
SQL_ME_RICH



Joined: 08 May 2012
Posts: 64

PostPosted: Fri May 18, 2012 7:22 pm    Post subject: Reply with quote

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).
Back to top
View user's profile Send private message
ChrisAVWood



Joined: 18 Dec 2007
Posts: 308
Location: Edmonton, Alberta, CANADA

PostPosted: Fri May 18, 2012 7:24 pm    Post subject: Reply with quote

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
_________________
English DBA living in CANADA
Back to top
View user's profile Send private message
SQL_ME_RICH



Joined: 08 May 2012
Posts: 64

PostPosted: Fri May 18, 2012 9:55 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
ChrisAVWood



Joined: 18 Dec 2007
Posts: 308
Location: Edmonton, Alberta, CANADA

PostPosted: Fri May 18, 2012 10:25 pm    Post subject: Reply with quote

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
_________________
English DBA living in CANADA
Back to top
View user's profile Send private message
SQL_ME_RICH



Joined: 08 May 2012
Posts: 64

PostPosted: Fri May 18, 2012 10:34 pm    Post subject: Reply with quote

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! Very Happy
Back to top
View user's profile Send private message
ChrisAVWood



Joined: 18 Dec 2007
Posts: 308
Location: Edmonton, Alberta, CANADA

PostPosted: Fri May 18, 2012 10:40 pm    Post subject: Reply with quote

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
_________________
English DBA living in CANADA
Back to top
View user's profile Send private message
SQL_ME_RICH



Joined: 08 May 2012
Posts: 64

PostPosted: Fri May 18, 2012 10:44 pm    Post subject: Reply with quote

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.

Rolling Eyes
Back to top
View user's profile Send private message
ChrisAVWood



Joined: 18 Dec 2007
Posts: 308
Location: Edmonton, Alberta, CANADA

PostPosted: Fri May 18, 2012 10:47 pm    Post subject: Reply with quote

Just out of interest.

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

Chris
_________________
English DBA living in CANADA
Back to top
View user's profile Send private message
SQL_ME_RICH



Joined: 08 May 2012
Posts: 64

PostPosted: Fri May 18, 2012 10:54 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
ChrisAVWood



Joined: 18 Dec 2007
Posts: 308
Location: Edmonton, Alberta, CANADA

PostPosted: Fri May 18, 2012 10:56 pm    Post subject: Reply with quote

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
_________________
English DBA living in CANADA
Back to top
View user's profile Send private message
SQL_ME_RICH



Joined: 08 May 2012
Posts: 64

PostPosted: Thu May 24, 2012 7:22 pm    Post subject: Reply with quote

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!
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Go to page 1, 2  Next
Page 1 of 2

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group