This SP controls deferments for all alerts. It appears in alert emails when Master Mode is enabled. For more details see Configuring Report Options.
DeferMaster has some nice flexibility and is used in conjunction with the previous defer SPs.
Here are the parameters:
| Parameter Name | Data Type | Definition | IsOutput |
|---|---|---|---|
| @ExecutionDateTime | DATETIME | The execution date of the alert you're deferring. This is the ExecutionDateTime col from the alert's history table. | 0 |
| @ShowDefer | BIT | Whether to show the Defer code in the alerts. For more info, see Configuring Report Options. | 1 |
| @ShowChange | BIT | Whether to show the Change code in the alerts. For more info, see Configuring Report Options. | 1 |
| @ShowException | BIT | Whether to show the Exception code in the alerts. For more info, see Configuring Report Options. | 1 |
| @ShowDeferTable | BIT | Whether to show the Defer table in the alerts. For more info, see Configuring Report Options. | 1 |
| @UseNIghtMode | BIT | Whether to use Night Mode in alerts. For more info, see Configuring Report Options and About: NIght Mode | 1 |
| @AutoCodeThreshold | INT | Threshold for switching between Detail and Master mode. For more info, see Configuring Report Options. | 1 |
| @DeferDate | DATE | The date the deferment will expire. You will not receive alerts on deferred objects until this date. | 1 |
| @DeferTime | TIME(0) | The time the deferment will expire. This works in conjunction with @DeferDate. This is the time of that day that the deferment will expire. You will not receive alerts on deferred objects until the combination of the date and time are reached. | 1 |
| @StmtOnly | BIT | This allows you to generate the individual deferment statements instead of having the SP run them for you. Use this if you want to change the objects being deferred. | 0 |
| @OptionsOnly | BIT | See which report options are going to be used when this SP runs against a specific alert and SLA combination. You can also use this to test your configuration to make sure it does what you want it to do. | 0 |
How it works
You may realize that this procedure works for every alert but doesn't take an alert name as a parameter. So how does it do what it does? Well, it's simple really. ExecutionDateTime is accurate to the millisecond and it's very unlikely that any 2 alerts, even if fired at the same time, will have the exact same ExecutionDateTime. So we've made it as easy as possible for you to call this SP. DeferMaster does all the work for you. It searches all the alert history tables and when it finds one that has the right ExecutionDateTime it processes the deferments for it. It's just that simple.
StmtOnly
Now, what if you want to change the objects that get deferred or change a property that isn't exposed to you as a parameter? That's where this parameter comes in. StmtOnly returns all the statements that would have been run to setup your deferments. You'll see that DeferMaster simply calls the existing deferment SPs for each alert, and that's what gets returned. An example of when you'd want to use this is when your alert is in Master mode and you don't want to defer every object. Or if there are say 300 DBs that fail backups for a single server and you would rather use 'All' for the DB name in the deferment instead of having 300 DBs in the defer table. In this case you would use StmtOnly and take a single row and replace the DBName with 'All' and run it. Or you would pick and choose the DBs you want to defer.
Here are some sample calls to make it easier for you to use this SP:
Get Options Only
--Get the date from the appropriate History table. DECLARE @ExecutionDateTime DATETIME = 'Insert Date', @ShowDefer BIT = 1, @ShowChange BIT = 1, @ShowException BIT = 1, @ShowDeferTable BIT = 1, @UseNightMode BIT = 1, @AutoCodeThreshold INT = 10, @DeferDate DATE = '', @DeferTime TIME(0) = '', @StmtOnly BIT = 1, @OptionsOnly BIT = 0; EXEC Setup.DeferMaster @ExecutionDateTime = @ExecutionDateTime, @ShowDefer = @IncludeDefer OUTPUT, @ShowChange = @IncludeChange OUTPUT, @ShowException = @IncludeException OUTPUT, @ShowDeferTable = @ShowDeferTable OUTPUT, @UseNightMode = @UseNightMode OUTPUT, @AutoCodeThreshold = @AutoCodeThreshold OUTPUT, @DeferDate = @DeferDate OUTPUT, @DeferTime = @DeferTime OUTPUT, @OptionsOnly = 1; SELECT @ShowDeferTable AS ShowDefer, @ShowChange AS ShowChange, @ShowException AS ShowException, @ShowDeferTable AS ShowDeferTable, @UseNightMode AS UseNightMode, @AutoCodeThreshold AS AutoCodeThreshold, @DeferDate AS DeferDate, @DeferTime AS DeferTime
Print All Defer Statements
--Get the date from the appropriate History table. [Setup].[DeferMaster] @ExecutionDateTime = 'Insert date' , @StmtOnly = 1;
Related Topics