Friday, January 29, 2010

Tip: turn auto close Off for you SQL Server databases

On a development machine, I had a high CPU usage for the SQL server service. To understand the reason, I looked at the SQL Server logs (From SQL Server Management Studio - SQL Server Logs).

As you may notice from the screenshot above, the activity of starting the reporting service database was recurring again and again.
I checked the database options, and the noticed that the auto close property was set true. This explains why SQL Server tries to start the database at every connection.
If the Auto close property is set to on:
the database is closed and shut down cleanly when the last user of the database exits and all processes in the database complete.
As quoted from msdn.
Unfortunately, the property was set to true when the database was created during the SQL server installation.
Turning Auto close to false (off), and maybe restarting the service, returned the CPU usage of the SQL Server service to a normal level.

No comments: