Skip to main content

Posts

Showing posts from 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.
DBCC TRACEON (3004) DBCC TRACEON (3605) CREATEDATABASE IFI


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 fi…

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 prevent…

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!

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 …

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…