SQL Express Migration Auto Close Setting

With over 700 databases to look after at MyWork automation is high on my list of priorities. I have two PowerShell scripts which run regularly checking SQL Error logs. One checks for the output from DBCC CHECKDB and one for errors. They then email the results to the DBA team.

This week we noticed that a new database was creating a lot of entries. It appeared to be starting up every few minutes. A bit of investigation by my colleague revealed that this database had been created on SQL Express and migrated to SQL Server.

SQL Express sets AUTO_CLOSE to on by default and this is what was creating the entries.

What does the AUTO_CLOSE setting do?

According to BoL Link

Description Default value
When set to ON, the database is shut down cleanly and its resources are freed after the last user exits. The database automatically reopens when a user tries to use the database again.

When set to OFF, the database remains open after the last user exits.

True for all databases when using SQL Server 2000 Desktop Engine or SQL Server Express, and False for all other editions, regardless of operating system.

That explains what was happening, the database was shutting down as the session finished and then starting back up again when the next one started. Repeatedly. Filling up the log files with entries, resetting the DMVs and using resources unnecessarily.

To find databases with this setting on query the master.sys.databases for the is_auto_close_on column Link or check the properties page in SSMS

image

You can change the setting there or with T-SQL

image

Of course I like to do it with PowerShell!!

To find the databases with AUTO_CLOSE setting on

image

To change the setting with PowerShell

image
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s