Is there any way to "mute" msdb backup entries?

Question

Post reply

Is there any way to "mute" msdb backup entries?

sql-lover

SSCoach

Points: 18530
More actions
September 8, 2016 at 7:25 am

#329950

IT has decided to implement VSS snapshots at SAN level. Those are being taken every hour (please do not ask me why, that's out of my scope) and are creating thousands and thousands of msdb entries on my server.
Now... you will say what's the problem with that? Well, Redgate Backup Pro GUI is no longer working. I already attempted the workaround of purging msdb entries and RedGate Backup Pro internal cache via script (a code was provided by me on the 2nd one) but what I really want it's just suppress those entries for good.
Is there any trace flag or way to avoid successful backups being logged into msdb system databases? Please keep in mind I am talking about msdb, not SQL error log.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Gazareth One Orange Chip Points: 27737 More actions · Answer 1

Try trace flag 3226 https://msdn.microsoft.com/en-us/library/ms188396.aspx

Although I'm not sure if it works for third party backups.

Don't think you can stop entries going into msdb tables, but they can be purged with a maintenance plan task.

sql-lover SSCoach Points: 18530 More actions · Answer 2

Gazareth (9/8/2016)
Try trace flag 3226 https://msdn.microsoft.com/en-us/library/ms188396.aspx
Although I'm not sure if it works for third party backups.
Don't think you can stop entries going into msdb tables, but they can be purged with a maintenance plan task.

That's not what I want. That mute entries from error log. My problem is with msdb itself.

Yeah, I know I can create maintenance plans or TSQL jobs that will continuously delete old entries from msdb, but I want to stay a way of that. There are so many that I will probably have to set a frequency of delete those every hour. If I can stop SQL from logging FULL backup info into msdb, that will fix my problem without the need of a job. I use Ola's solution so I already have all backup info on a table.

Gazareth One Orange Chip Points: 27737 More actions · Answer 3

Ok, then no, I don't think there's a way to stop them being logged in msdb. Don't envy you having to deal with it!

sql-lover SSCoach Points: 18530 More actions · Answer 4

Gazareth (9/8/2016)
Ok, then no, I don't think there's a way to stop them being logged in msdb. Don't envy you having to deal with it!

Thanks for reply.

Yes, I'm afraid there is no way, but was giving it a shot here on a forum. You'll be surprised of how many new stuff you learn.

I honestly think snapshots every hour are over killing, but that's out of my scope.

Gazareth One Orange Chip Points: 27737 More actions · Answer 5

Ah well, there may be someone along soon with a better answer, even from the Redgate side - there's plenty of Redgate people around here!

Full snapshots that frequently are definitely overkill IMHO. Seems like a good way to stress test the SAN's capabilities!

Doesn't database I/O need to be frozen/thawed every time too?

And please tell me there's some way that these 'backups' make their way offsite?

sql-lover SSCoach Points: 18530 More actions · Answer 6

Gazareth (9/8/2016)
Ah well, there may be someone along soon with a better answer, even from the Redgate side - there's plenty of Redgate people around here!
Full snapshots that frequently are definitely overkill IMHO. Seems like a good way to stress test the SAN's capabilities!
Doesn't database I/O need to be frozen/thawed every time too?
And please tell me there's some way that these 'backups' make their way offsite?

That's 100% correct. I/O freezes every time, that's why my DIFFs and FULLs fail, and I see some retries.Thanks goodness for Backup Pro 7.

I opened a ticket from RedGate and they closed it because there was no way "to fix" that.

Like I said, all these are IT decisions. Already chime in, but it is what it is. :rolleyes:

Gazareth One Orange Chip Points: 27737 More actions · Answer 7

Gazareth

One Orange Chip

Points: 27737

September 8, 2016 at 11:41 am

#1899896

Ah, well good luck!

Sergiy SSC Guru Points: 110208 More actions · Answer 8

sql-lover (9/8/2016)
Is there any trace flag or way to avoid successful backups being logged into msdb system databases? Please keep in mind I am talking about msdb, not SQL error log.

You may reduce the number of history rows per job in SQL Agent properties.

_____________
Code for TallyGenerator

SoHelpMeCodd SSCertifiable Points: 5871 More actions · Answer 9

There is a way to suppress inserts, but it is not going to be supported by MS:

use msdb

create trigger NoJobHistory on dbo.sysjobhistory for insert as

rollback

I cannot predict what other problems might arise (at very least you should trace a successful sysjobhistory insert and one with above trigger, perhaps with a more surgical predicate). And I suggest you drop above trigger before any upgrade.

Caveat Emptor :).

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

SoHelpMeCodd (9/12/2016)
There is a way to suppress inserts, but it is not going to be supported by MS:
use msdb
create trigger NoJobHistory on dbo.sysjobhistory for insert as
rollback
I cannot predict what other problems might arise (at very least you should trace a successful sysjobhistory insert and one with above trigger, perhaps with a more surgical predicate). And I suggest you drop above trigger before any upgrade.
Caveat Emptor :).

Or in this case, it would make more sense to create the rollback trigger on backupmediafamily and backupset to prevent the various entries into those tables related to SAN snaps or backups.

I think, as probably has already been surmised, this hourly snapshot method is completely ridiculous. It seems to me that the server admins made an ill-informed decision and there decision is absolute truth.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events