Policy-based Management in SQL Server 2008 - Part II

Tuesday May 13th 2008 by Yan Pan

Learn how to use SQL Server Agent alerts and jobs to fix policy incompliance automatically.

As discussed in Part I, the execution mode of each policy is determined by the characteristics of the Management facet that is used by the condition in the policy.

Facets support “On Change – Prevent” if there is transactional support for the DDL statements that change the facet state. Only the Login, User and Database Security facets support the “On Change – Prevent” mode. However, it is usually more important to prevent or correct a violation than just to log it. Fortunately, we can use SQL Server Agent alerts and jobs to remedy the limitations. When policies are executed in one of the three automated modes, if a policy violation occurs, a message is written to the SQL Server error log and the Application log. The error message numbers are shown below.

Execution mode

Message number

On Change - Prevent (if automatic)


On Change - Prevent (if On Demand)


On Schedule


On Change - Log Only


A SQL Server Agent alert can be set up to detect the error message and invoke a job to correct the violation.

Let’s look at an example. As we know, it is important to back up the transaction log of a database that is on Full or Bulk-logged recovery model regularly so that the transaction log won’t fill up. We can create a policy to check the last time the transaction log was backed up and make sure it was done within the last day.

1.  In SSMS, expand Management in Object Explorer, expand Policy Management, right click Conditions, and select New Condition. In the New Condition dialog box, in the Name field, type Transaction Log Last Backup Date. Pick the Database facet. In the Expression area, in the Field box, select @RecoveryModel, in the Operator box select =, and in the Value field select Full. Create another clause with @RecoveryModel and a different value of BulkLogged, and select Or in the AndOr box. Select both clauses and right click in the highlighted area, then click Group Clause to group the two clauses. This creates an expression to check if the targeted database is in Full or Bulk-logged recovery model.

We still need another clause to check if the transaction log of the database was backed up within the last day. Select AND in the AndOr field, @LastLogBackupDate in the Field box, >= in the Operator box, and in the Value field, click on the button. This brings up an Advanced Edit dialog box. Type DateAdd('day', -1, GetDate()) in the Cell value box. Close the dialog box.

2.  Right click Policies in the Object Explorer, and select New Policy. In the New Policy dialog box, in the Name box, type Safe Transaction Log Backup Date. Check the Enabled box to enable the automated execution modes. In the Check condition box, select the Transaction Log Last Backup Date condition under the Database facet. Select the Online User Database condition under the Database facet in the Against targets box. In the Execution Mode box, select On Schedule as the execution mode, and in the Schedule box, pick the CollectorSchedule_Every_15min schedule that will run the policy every 15 minutes. Note that the On Change - Prevent execution mode is not available for the database facet.

The Online User Database condition is one of the policies that are shipped with SQL Server 2008. By default, the policies are not installed on the SQL Server. To import them, right click Policies under Policy Management, and select Import Policy. This brings up an Import dialog box. Click the button in the Files to import box. Another Select Policy box pops up. Navigate to the directory C:\Program Files\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033, and select all of the files under this folder. Click Open to close this box. Click Ok to close the Import dialog box. SQL Server will import all the policies under that directory.

SQL Server creates a job called “check_Safe Transaction Log Backup Date_job” to evaluate the “Safe Transaction Log Backup Date” policy every 15 minutes. If you look at the only job step of this job, you see that SQL Server Agent uses a PowerShell cmdlet called Evaluate-Policy to evaluate the policy.

If the transaction log of a database is found not backed up within the last day, an error message with an error number 34052 will be logged into the SQL Server error log and the Application log. In our example, we have a user database called Matrix that has not been backed up. Once the policy is evaluated by the “check_Safe Transaction Log Backup Date_job” job, a red cross appears on the Matrix database, which means the Matrix database violates the policy.

An error message is also logged into the SQL Server error log.

Error: 34052, Severity: 16, State: 1.
Policy 'Safe Transaction Log Backup Date' has been violated by target '/Server/POWERPC/Database/Matrix'.

We can implement an alert to detect the error 34052 and invoke a job called “Fix Transaction Log Backup” to parse the error message and back up the transaction log of the violating database. The error message is passed by the alert to the job in an (A-MSG) token. The job contains one Transact-SQL script job step with the following script.

DECLARE @errormsg nvarchar(800),  @start int
DECLARE @policyname sysname
DECLARE @dbname sysname
DECLARE @sqlstring nvarchar(800)
SET @errormsg = N'$(ESCAPE_SQUOTE(A-MSG))'
SET @start=9
SET @errormsg = SUBSTRING(@errormsg, @start, LEN(@errormsg) - @start)
SET @policyname = SUBSTRING(@errormsg, 1, CHARINDEX('''', @errormsg)-1)
SET @start=CHARINDEX('Database', @errormsg) + 9
SET @dbname = SUBSTRING(@errormsg, @start, LEN(@errormsg) - @start)
SET @sqlstring = 'BACKUP LOG ' + @dbname + ' TO DISK=''C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\' 
  + @dbname + '_Log.bak'''
print 'sqlstring: ' + @sqlstring
exec (@sqlstring)

In our example, we create an alert called Unsafe Transaction Log Backup Alert to invoke the job. This alert detects any errors with a number 34052 and a message text containing the policy name, Safe Transaction Log Backup Date. The Fix Transaction Log Backup job is also specified in the Response pane of the alert, and it will run when the alert is raised.

After the “Unsafe Transaction Log Backup Alert” alert is defined, the next time the policy is evaluated, an alert is raised automatically and the “Fix Transaction Log Backup” job is invoked to back up the transaction log of the Matrix database. Here is the output from the job in the job history.

Date  4/20/2008 8:30:03 PM
Log  Job History (Fix Transaction Log Backup)
Step ID  1
Job Name  Fix Transaction Log Backup
Step Name  Back Up Log of Violating Database
Duration  00:00:00
Sql Severity  0
Sql Message ID  3014
Operator Emailed  
Operator Net sent  
Operator Paged  
Retries Attempted  0
Executed as user: POWERPC\Yan. sqlstring: BACKUP LOG Matrix TO DISK='C:\Program 
Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Matrix_Log.bak' [SQLSTATE 
01000] (Message 0)  Processed 3 pages for database 'Matrix', file 'Matrix_log' on file 1. [SQLSTATE 
01000] (Message 4035)  BACKUP LOG successfully processed 3 pages in 0.037 seconds (0.607 
MB/sec). [SQLSTATE 01000] (Message 3014).  The step succeeded.

As we can see above, the job backed up the transaction log of the Matrix database successfully.

If you got an error message “Variable A-MSG not found”, you need to enable alert tokens. Right-click SQL Server Agent in Object Explorer, select Properties, and on the Alert System pane, select Replace tokens for all job responses to alerts to enable tokens.


In this article, we have shown you how to use SQL Server Agent alerts and jobs to fix policy incompliance automatically.

» See All Articles by Columnist Yan Pan

Mobile Site | Full Site