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
|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
You can change the setting there or with T-SQL
Of course I like to do it with PowerShell!!
To find the databases with AUTO_CLOSE setting on
To change the setting with PowerShell