Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Auto Close and Auto Shrink - Just Don't

By Mike Pearson,

I was on-site with a client, whose had a server which performing very sluggishly. It was a beefy brute with heaps of memory and processing power, so clearly something was just not what it should have been.

For me step 1 in doing any sort of trouble-shooting is to look at the logs. Yup, always a good place to start because the problem was ticking over at 5-8 entries per second…

Starting up Database ‘Tom’ 
Starting up Database ‘Dick’
Starting up Database ‘Harry’

So, what’s the problem then? Well, before I answer that, you can find these properties either by looking at the ‘Options’ tab of your database properties, or by running

SELECT DATABASEPROPERTYEX('DatabaseName','IsAutoShrink')
GO

SELECT DATABASEPROPERTYEX('DatabaseName','IsAutoClose')
GO

If the option is ‘True’ (the T-SQL statement will return 0=false or 1=True), then there’s a performance hit just looking for a place to happen.

Auto_Close 

When Auto_Close is set to ON/TRUE, the database is closed and shut down when all processes in the database complete and the last user exits the database, thereby freeing up the resources held by that database. When a new connection calls the database again, it automatically reopens. This option is set to ON/TRUE when using the SQL Server Desktop Edition, but is set to OFF/FALSE for all other editions of SQL Server. 

The problem is that most servers sit behind applications that are repeatedly opening and closing connections to your databases, so the overhead of closing and reopening the databases between each connection is, well, “performance abuse”. The amount of memory that is saved by this is insignificant, and certainly does not make up for cost of repeatedly initializing the database.

Admittedly, this option may have advantages on personal desktop scenarios as (when they are closed) you can treat these database files as any other files. You can move them and copy them, or even e-mail them to other users. However, when it comes to a proper server environment these points are fairly irrelevant. 

So as far as Auto_Close is concerned, don’t even be tempted. Just Don’t.

Auto_Shrink

The auto_shrink option has it’s uses in scenarios such as development servers and the like where disk space resources are usually limited and hotly contested, but (there’s always a ‘but’) there is a performance cost. Shrinking a database hogs the CPU and takes a long time. Plus, any indexes on the heaps (a table without a clustered index) affected by the shrink must be adjusted because the row locators will have changed. More work for the CPU. Like Auto_Close, this option is set to ON/TRUE for all databases when using SQL Server Desktop Edition, and OFF for all other editions, regardless of operating system.

When this option is set to ON/TRUE, all of a database's files are marked for shrinking, and will be automatically shrunk by SQL Server. This option causes files to be shrunk automatically when more than 25 percent of the file contains unused space. Not a wise option for your production systems which would suddenly suffer a performance hit when SQL decides it’s shrink-time. So, again – just don’t.

Here is a quick script which will run on SQL2000 giving you a list of your databases with the status of these options.
SET NOCOUNT ON

SELECT [name] AS DatabaseName
 , CONVERT(varchar(100),DATABASEPROPERTYEX([Name] , 'Recovery')) AS RecoveryType
 , CONVERT(varchar(10),DATABASEPROPERTYEX([Name] , 'IsAutoClose')) AS AutoClose
 , CONVERT(varchar(10),DATABASEPROPERTYEX([Name] , 'IsAutoShrink')) AS AutoShrink
FROM master.dbo.sysdatabases
Order By DatabaseName 
Total article views: 19923 | Views in the last 30 days: 10
 
Related Articles
FORUM

Shrink database

Shrink database

BLOG

Common SQL Server Mistakes - Shrinking Databases

I don’t like there being an easy command to shrink databases, and I especially don’t like seeing the...

FORUM

Database Shrink

Database Shrink or Database Files Shrink?

FORUM

Shrink File or Shrink Database

Shrink Database takes time

FORUM

Shrink database

Shrink database

Tags
administration    
configuring    
miscellaneous    
monitoring    
performance tuning    
sql server 7    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones