Tuesday, 21 September 2010

Instant File Initialization – Is it Turned On?

I have posted a short note on instant file initialization previously, that post can be found here. The crux of enabling instant file initialisation is to give the service account running the SQL Service permission on the 'Perform Volume Maintenance Task' This is just a little follow up post to demonstrate how you can check that Instant File Initialisation is enabled. And I will show you how to temporarily disable it too.
In order to check for Instant File Initialization being enabled we need to turn on two undocumented trace flags, these output the result of a CREATE database statement to the log. If Instant File Initialization is enabled then you will see the log file being zeroed out. If it is not enabled you will see both the log and data files being initialised.


You will see in the error log I currently I have instant file initialization enabled and as such I only have entries in the SQL Server log for the log file being initialized.


You can temporarily disable Instant file initialization, to do this you need to turn on trace flag 1806.


If I then drop and recreate the IFI database.


We should then see both the ldf and mdf files being initialised in the log:


The 1806 trace flag is only a temporary measure if you wish to disable Instant File Initialisation then you need to remove the 'Perform Volume Maintenance Tasks' right from the service account and the SQL Service restarted.

Paul Randal (blog | Twitter) recommends that you enable Instant File Initialization if at all possible.

Edit: 21 Sept 2010 to correct typos

Wednesday, 15 September 2010

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. (http://www.gethynellis.com/2010/06/t-sql-tuesday-007-policy-based.html) 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', 

Friday, 10 September 2010

Mastering PowerShell – Free EBook

Someone who I follow on twitter posted a tweet recently with a link to a free eBook on PowerShell. I have forgotten  who posted the link, sorry. I followed the link and downloaded the book. I have been ‘learning’ PowerShell for a little while, from various sources and I thought I would try out this eBook too. It is written by Dr Tobias Weltner a PowerShell MVP. I’ve only read a couple of chapters but I think it is well worth a read if you are looking to use and learn PowerShell.

You can find the book here.

This is just a short post for a Friday.

Have a good weekend!

Thursday, 9 September 2010

SQL Server - Dedicated Administrator Connection (DAC)

This a short post looking at the Dedicated Administrator Connection or DAC for short. In extreme circumstances, when there is a complete lack of resources available, for example, it is possible for SQL Server to enter a state whereby no further connections can be made to the instance. In versions prior to SQL server 2005 this meant the DBA might not be able to connect to the said instance to begin troubleshooting, kill rouge processes causing the issue, or diagnose the cause of the problem. A special connection called a Dedicated Administrator Connection (DAC) was introduced in SQL Server 2005 that was designed to be accessible even when no other connections are available.

DAC access must be specifically requested. You connect via the command line tool SQLCMD using the -A or /A flag. This is generally the recommended way as it uses fewer resources than connecting through management studio but you can use both and we will look at connecting via the GUI first.

Connecting to the DAC using the GUI

If you try and connect to DAC using the object explorer in management studio you will get an error similar to:

Cannot connect to admin:SQLSERVER2008R2.

This is because you can't connect to the DAC in object explorer. You can however connect to it from a query window from management studio click <file> <New Database Engine Query>

You then connect using the DAC by specifying ADMIN: in front of the instance name:


Click on connect and you should get in. You can the run TSQL statements such as SP_WHO query DMV's and KILL any rouge processes in your efforts to troubleshoot and fix the problem

Connecting to DAC using SQLCMD

To connect to the DAC using the SQLCMD console:

Open a command prompt and connect to the instance using the necessary flag


from the SQLCMD prompt you can then run things like sp_who query DMVs and kill sessions and troubleshoot as needed.

There can only be on connection on the DAC at anyone time if two people try and connect the last person to try and connect will get an error similar to:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (Microsoft SQL Server, Error: 64)

which I think you will agree is pretty non-specific but there will be a more meaningful message in the SQL Server log:

Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process.

Error: 17810, Severity: 20, State: 2

My GUI session was still connected using the DAC. For this reason I would recommend only connecting via the DAC as needed and disconnecting from it as soon as your done.

Some other points to note:

The DAC is only available locally by default but this can be reconfigured to allow remote connections using the configuration option called 'Remote Admin Connections'

To login using the DAC you must be a member of the sysadmin server role.

Tuesday, 7 September 2010

Auto Close – SQL Server

I ran into an issue recently where I had a third party supplied database set to auto close. I couldn't work out why they would use such a setting but I could think of a couple of reasons not to including:

  • Resources used to maintain the state of the open database will always be ready and waiting when the database is in an ‘open’ state. Resources are allocated to an Open database that allow SQL Server to maintain that state, including memory for locks buffers etc. When a connection is made to the database these resources are ready. If Auto Close  the database ‘closes’ when the last connection disconnects and these resources are given up. When the next connection comes along you a may suffer a performance hit as the database 'starts' again and has to reallocate the necessary resources to the database. This was my problem my app was taking an eternity to log in when it hadn’t been used for a while.
  • According to SQL Server 2008 Books online the auto close feature will be removed in future.

I really struggled to think of a good use of auto close but at the same time thought that someone out there must have a use for it. So I posted a question on twitter using the #SQLHELP hashtag and I got several replies:

  • Not a good reason: Want to use a backup solution that cannot backup open files.
  • Auto close has been used effectively on POS (point-of-sale) applications that had minimal spec hard ware.
  • Web hosts use it, they cram 500 plus databases onto a server  and set it to auto close so the less used ones release resources

A big thanks to @Brento @SQLRich @onpnt @PaulWhiteNZ , @AaronBetrand, @banerjeeamit for their replies to my question

Buck Woody on his blog  suggests as a best practice it should be turned off and lists some excellent reasons for doing so.

The long and short of it, I have now turned off auto-close and the application which is not heavily used now fires up much more quickly.

Featured post

Creating a Linked Server to an Azure SQL Database

Why would you create a Linked Server to an Azure SQL Database? If you work in a hybrid environment with some databases stored on your on ...