Change Log Backup Frequency Automatically

Question

Change Log Backup Frequency Automatically

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 1

Grant Fritchey wrote:

Jeff Moden wrote:
p.s. And stop using REORGANIZE for your index maintenance.
With the noted exception of columnstore indexes, but only when working in 2016 SP1 or greater.

Correct. Thanks for the reminder, Grant. I was speaking only of RowStore indexes (heh... no one uses column store, right? :D) but didn't say so. My bad.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 2

Neil Burton wrote:

Jeff Moden wrote:
I have my production databases set to be "exposed" to the possibility of a transaction log backup every 15 minutes, but it's stupid to do an actual T-Log backup if nothing has actually changed. When you do your laundry, do you wash the clean clothes as well as the dirty ones?
sys.databases has a column called "log_reuse_wait" for every database. If it contains the value of "Nothing", then don't do a backup. It's that simple.
That makes total sense. However we're a truly 24 hour business and the database in question is one of our busiest. If nothing changed for more than a few tens of seconds I'd be surprised. If nothing changed for a couple of minutes, the users would be on the phone.

Ah. Got it. Thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 3

Neil Burton wrote:

With regard to the recovery model, I'd forgotten about Bulk-Logged. It's definitely something to consider. Another part of the standardisation is to review the backup policies and procedures so there's an opportunity to look at that.

To be sure, I'd forgotten that you were doing a bit of homegrown log shipping. Because slipping into the Bulk Logged Recovery Model will cause the indexes to be rebuilt in a minimally logged fashion, that might kill your HG log shipping. I know you know this but I have to say it out loud to make myself feel better, do a small test before committing to changing Recovery Models to support indexing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Neil Burton SSC-Insane Points: 22999 More actions · Answer 4

Jeff Moden wrote:

Neil Burton wrote:
With regard to the recovery model, I'd forgotten about Bulk-Logged. It's definitely something to consider. Another part of the standardisation is to review the backup policies and procedures so there's an opportunity to look at that.
To be sure, I'd forgotten that you were doing a bit of homegrown log shipping. Because slipping into the Bulk Logged Recovery Model will cause the indexes to be rebuilt in a minimally logged fashion, that might kill your HG log shipping. I know you know this but I have to say it out loud to make myself feel better, do a small test before committing to changing Recovery Models to support indexing.

It suddenly seems that messing with how the logs are created to solve a problem with how the logs are created sounds like a recipe for disaster. It's one to file under to something to remember I think.

I'm a bit frustrated that the investigation has been parked. It would be nice to look into to keeping the log growth down somehow but I can understand why the boss has put a stop to things. There's enough space for the logs to grow into and I suppose it's not inherently a problem. We've found away of handling the large although it may not be the most elegant.

I will have a chat with people about the multiple schedules approach though. I think even if it's not done immediately, it's something we've got a window to implement in the near future. It's also something that could be rolled out across the other servers a little less intrusively than adding steps to the jobs.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

Jeff Moden wrote:

(heh... no one uses column store, right? :D) but didn't say so. My bad.

BWA-HA-HA!!!

Exactly.

I sure wasn't trying to correct your answer, just add to it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 6

Grant Fritchey wrote:

Jeff Moden wrote:
(heh... no one uses column store, right? :D) but didn't say so. My bad.
BWA-HA-HA!!!
Exactly.
I sure wasn't trying to correct your answer, just add to it.

Not to worry. It's a correction that I actually do need to adopt and very much appreciated the reminder.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)