Friday, February 16, 2007

SQL Server: Which databases are set to auto close?

When you import a MSDE version of a database to regular SQL Server, it imports the "Auto Close" property. This is useless in regular SQL Server-- Auto Close is a property used to close down the database when the last connection is closed. This is needless overhead in the Server version of SQL server.

This VBScript script polls all the databases in a SQL Server instance to see their Auto Close property. This script could be modified to show other properties as well, of course.

' Drop this in a text file called show_dbs_with_autoclose.vbs,
' modify the server name to your server name,
' save it, then double click it, and you will get a message box for each
' database set to Auto Close
Dim svr 'As New SQLDMO.SQLServer
Dim db 'As SQLDMO.Database
Set svr = CreateObject("SQLDmo.SqlServer")
svr.LoginSecure = True
svr.Connect "MySQLServer"
For Each db In svr.Databases
If db.DBOption.AutoClose Then
WScript.echo db.Name & " is set to autoclose"
End If

The commented code is there to show how you would use this in VBA or Visual Basic-- this is nice, because then you get all the IntelliSense. Make sure to open references and add in SQLDMO Objects if you do this (of course, in VBScript, this is unnecessary (and impossible)).

No comments:

Post a Comment

I moderate comments blog posts over 14 days old. This keeps a lot of spam away. I generally am all right about moderating. Thanks for understanding.