Skip to main content

Policy Based Management - Disable Autoclose

I posted recently about my run-in with autoclose and my online friend Jorge Segarra (Blog|@SQLCHICKEN) left a great comment on my syndicated post on SSC where he said "Yeah Auto_Close came straight from the devil. One cool thing you could do on 2008 is use Policy-Based Management to evaluate all your servers/databases and make sure auto_close is set to off. Easy to do and saves you a ton of headaches!"
Which led me to this post, how do you do it? Well for June's TSQL2sday I wrote a post all about using Policy Based Management (PMB) to try and enforce some sort of sensible naming convention for stored procedures. And we will follow a similar process here.
An important point to note here is the execution mode of each policy is determined by the characteristics of the facet that is used by the condition in the policy. The Autoclose option is available in several facets including Database, Database Option, Database Performance. I was hoping to implement a policy that prevents AUTCLOSE being turned on altogether but alas I don't think PMB is capable of preventing 'auto close' being turned on at the time the database option is changed, as the facets that include 'Autoclose' only have 'On demand', 'On Schedule', or 'On Change - Log Only' execution mode's available to them. Instead we will have to make to make alternative arrangements to be alerted when the option is set so we can  correctly fix it before it becomes an issue. As you can see from my last AutoClose post, in a small number of circumstance some people have found a use for it but generally speaking it is better for 'auto close' to be turned off.

Create the Policy

I have talked about the steps involved in setting up Policies in a previous post, you can find that here. ( so I will simply walk through creating the policy.

We have two options when it comes to creating the policy, we can use the management studio GUI or we can use TSQL. We will use the GUI for this demonstration but I will include the TSQL scripts at the end of the post for completeness, the scripts will be generated from Management Studio.
1. Connect to the instance in question in Management Studio
2. Expand the <Management> Folder, you will see 3 sub folders called <Policies>, <Conditions> and <Facets>.
3. We will start with the last folder, the <Facets> folder, expand the <Facets> folder and you will see a big long list of facets.
4. We will use the <Database Options> Facet. Right click on the <Database Option> and select <New Condition>
5. Give your condition a name, mine is called AutoCloseCheck
6. Ensure that the 'Database Options' Facet is selected.
7. In the expressions section click to add a clause.
8. In the Field Column, from the drop down list, pick @AutoClose
9. Ensure that operator is still '='
10. and change the value field to 'False' - thus ensuring that our condition checks for autoclose being set to false.
11. You can add a description in the description tab if you wish. When you're done, click <OK> and we have created the necessary condition to test.
Next we need to create a policy that tests for that condition:
12. Expand the <polices> folder.
13. Right-Click the policies folder and select <New Policy...>
14. Give you policy a name, I have picked the same name as my condition, mainly to avoid confusion so I have called it "AutoCloseCheck"
15. From the <Check Condition> drop-down list pick the condition we created above. in the <Against targets> box Every Database is selected. I'm going to leave that as the default setting as I want the policy to check on each database.
16.In the <Evaluation Mode:> drop-down list I picked the 'On-change: log only' I left the <Server Restriction> set to 'None'
17. In the description tab I simply enter a <description> and some <text to display>: 'Autoclose should be off '
We now have a policy created, so how will this policy help? Let's test the policy by changing the AUTO_CLOSE database option of one of many test databases to true. We can do this with the following script:
USE [master]

What do you expect to happen? That's right because our Facet Database Option does not support prevention we are only able to use the Execution Mode: On Change - Log Only which writes a useful error to the SQL Server log:


So when you come in the next morning and perform your daily SQL Server checks, or if you are very diligent as you should be and perform afternoon checks as well as morning checks on your database servers you will see this error, become very upset with your colleague, or more likely a DEV who has the necessary permissions on a DEV box or a third party supplier who setup the database and you can go and switch off the AUTO_CLOSE option and wrap the offending person on the knuckles...Hey its better than before, you know when it would be left like that until it becomes a problem.

There is something else that you can do though to make sure that you get alerted when the policy fires and writes out the log: You can use Alerts in the SQL Agent and get sent a nice warning email as soon as the database option is changes which means that you can fix the issue before it becomes a problem for you.


We can see from the screen shot above that my On Change - Log Only error was raised with a message number of 34053. The execution mode message number can be found in the following table:

Execution Mode Message Number

Execution Mode
Message Number

On Change - Prevent (automatic) 34050
On Change - Prevent (on demand) 34051
On Schedule 34052
On Change - Log Only 34053

We need to setup an alert to look for message number 34053 and get the alert to notify us when it fires, this is how we do it:

  1. In SSMS expand the <SQL Server Agent>
  2. Expand the <Alerts> Folder
  3. Right-Click the <Alerts> folder and select <New Alert>
  4. Give your Alert a Name: AutoCloseCheck
  5. In the event alert definition leave <All databases> selected for <Database name>
  6. Make sure that Alerts will be raised based on <error number> is selected and change the error number to 34053
  7. On the response tab select notify operators and select who you want to alert when the error is raised, I use email, you can use whatever best suits your needs.

That's about it, we have looked at Policy Based Management and configuring it to log any changes to the database option AUTO_CLOSE and we have configured alerts to notify us of when that policy is violated so we can take action. The TSQL scripts (generated from SSMS) that can be used to recreate this can be found below. I'd be really interested to hear your thoughts on this, in particular if you used PBM and if you would use a different technique.

TSQL Scripts

These are the scripts generared by SSMS:

--Create the neccessary condition
--script generated using SSMS
Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'AutoCloseCheck', @description=N'', @facet=N'IDatabaseOptions', @expression=N'<Operator>
</Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT
Select @condition_id


--CREATE A POLICY using the auto close condition created
Declare @object_set_id int
EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name = N'AutoCloseCheck_ObjectSet',
@facet = N'IDatabaseOptions', @object_set_id = @object_set_id OUTPUT
Select  @object_set_id

Declare @target_set_id int
EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name = N'AutoCloseCheck_ObjectSet',
@type_skeleton = N'Server/Database', @type = N'DATABASE', @enabled = True,
@target_set_id = @target_set_id OUTPUT
Select  @target_set_id

EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id = @target_set_id,
@type_skeleton = N'Server/Database', @level_name = N'Database',
@condition_name = N'', @target_set_level_id = 0


Declare @policy_id int
EXEC msdb.dbo.sp_syspolicy_add_policy @name = N'AutoCloseCheck',
@condition_name = N'AutoCloseCheck', @policy_category = N'',
@description = N'Autoclose should be off ',
@help_text = N'''Autoclose should be off ', @execution_mode = 2,
@is_enabled = True, @policy_id = @policy_id OUTPUT,
@object_set = N'AutoCloseCheck_ObjectSet'
Select  @policy_id

--Turn on autoclsoe for a database.
USE [master]

--Create Alert to notify if autoclose is enabled
USE [msdb]

/****** Object:  Alert [AutoCloseCheck]    Script Date: 09/10/2010 09:35:43 ******/
EXEC msdb.dbo.sp_add_alert @name=N'AutoCloseCheck', 


Post a Comment

Popular posts from this blog

Always Encrypted

By David Postlethwaite

Always Encrypted is new features in SQL Server 2016 and it is also available in Azure SQL Database. Here you can encrypt columns in a table with a master key and a certificate so that they will appear as encrypted strings to those who don’t have the required certificate installed on their pc.
Once the certificate is installed on the computer then the unencrypted data can then be seen as normal.

The data passes from database to your application as the encrypted value, only the application with the correct certificate can unencrypt the data so it is secure across the wire. This will go some way to resolving the concern of people worried about putting their sensitive data on a shared server in the cloud such as Microsoft Azure and accessing the data across the Internet.

At the time of writing Always Encrypted is only supported with ADO.NET 4.6, JDBC 6.0 and ODBC 13.1 but expect other driver to become available.

The calling application (including SSMS) must also hav…

SQL Server 2012 and Virtual Service Accounts

This post is written by David Postlethwaite
If you are using SQL Server 2012 you will probably have noticed that the default account for the SQL services has changed from that used in previous versions. With SQL 2005 and 2008 the default account for SQL service and SQL Agent service was “NT Authority\System”. This is one the built in accounts on a Windows machine, managed by the machine and selectable from a dedicated dropdown list

The Network Service account was introduced in Windows 2003 as an alternative to using the LocalSystem account, which has full local system privileges on the local machine, a major security concern.
The Network Service has limited local privileges easing these security concerns but when many services on a machine use the Network Service account it becomes harder to track which service is actually accessing resources and performing actions, because all the services are using the one Network Service account.
Also, this account, by default, has sysadmin per…

SQL Server Express v SQL Server Developer Edition

SQL Server Express v  SQL Server Developer Edition
Over the weekend I received the following in an email from Ali Ahmad who asked me some questions about learning SQL Server. We exchanged a couple of emails so I have boiled this down to the most salient points.

Goal: I want to learn SQL Server inside out… for career progression as DBA/BI/data mining. I'm a data analyst and want to learn inside out about data warehousing.
•I understand relational database concepts...
•I have SQL Server 2014 express installed…
•I need to download the adventure works sample in order to play with it.
•So much knowledge on Microsoft website it's easy to get lost… where do I begin?

SQL Server Express v SQL Server Developer
This is interesting. If you want to learn SQL Server inside out including the Business Intelligence suite of applications I would suggest downloading the developer edition of SQL Server which since 2016 has been made available free of charge. Prior to that there was a f…