The SQL Server Misconfiguration Chronicles part 1: Database AutoClose & AutoShrink

In the SQL Server Consulting world you see a lot of different environments, but mostly you see the same basic misconfigurations for their SQL Server Enviroment.

The following blogposts will show you the most common issues with the configuration of SQL Server, they will tell you what the issue is, why it is a bad configuration and how you can solve it.

This first post will be on two common misconfigured database options: AutoClose & AutoShrink.

What does Autoclose do to my database?

Autoclose will close your database when the last user who uses the database is disconnected.

Why is that a bad thing?

Everytime a user connects to a SQL Database which is closed it will have to be opened again, this will degrade performance because the connecting user will have to wait until the database is opened again before being able to access the data.

How do we solve this issue?

You can execute the following script which will generate statements to alter auto close to false for all databases of the instance.

Created By Stijn Wynants
SQL AutoClose Fixer
Description: Execute Script to PRINT ALTER STATEMENTS
auto_close to false on all databases

DECLARE @CMD varchar(max)
DECLARE @Database varchar(200)
DECLARE AutoCloseFixer CURSOR FOR
select
name
from sys.databases
where
database_id > 4
and
is_auto_close_on = 1
OPEN AutoCloseFixer
FETCH NEXT FROM AutoCloseFixer
INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
PRINT ‘
————> DisableAutoClose on ‘+@Database+’
USE [master];
GO
ALTER DATABASE [‘+@Database+’] SET AUTO_CLOSE OFF WITH NO_WAIT;
PRINT ”Disabled AutoClose on ‘+@Database+”’
‘
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE()
END CATCH
FETCH NEXT FROM AutoCloseFixer
INTO @Database
END
CLOSE AutoCloseFixer
DEALLOCATE AutoCloseFixer

What does AutoShrink do to my Database?

It will remove the unused space from your database file and thus making your database file smaller.

Why is that a bad thing?

Because auto shrink process removes all free space out of your database file, there will be no space left for new data. So the moment when new data gets loaded in your database your database file will have to grow again. (This is handled by auto growth, this will be discussed in the next post) This will grow larger than the actual space you needed, which will leave some free space, when the auto shrink feature kicks in again it will remove this space again. This constant shrink grow shrink grow operation will cause file fragmentation (on a system level) and this uses a lot of resources. Also the shrink operation itself uses a lot of resources because it will move pages to other places in you database file which takes CPU IO & generates transaction log. But last but not least when auto shrink services your database it will not only shrink your database but because it moves around the pages it will have a devastating side-effect! It will cause a complete fragmentation of all your database indexes which will cause your performance to plummet.

How do we solve this issue?

You can execute the following script which will generate statements to alter auto growth to false for all databases of the instance.

Created By Stijn Wynants
SQL AutoShrink Fixer
Description: Execute Script to PRINT ALTER
auto_shrink to false on all databases

DECLARE @CMD varchar(max)
DECLARE @Database varchar(200)
DECLARE AutoShrinkFixer CURSOR FOR
select
name
from sys.databases
where
database_id > 4
and
is_auto_shrink_on = 1
OPEN AutoShrinkFixer
FETCH NEXT FROM AutoShrinkFixer
INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
PRINT ‘
————> DisableAutoShrink on ‘+@Database+’
USE [master];
GO
ALTER DATABASE [‘+@Database+’] SET AUTO_SHRINK OFF WITH NO_WAIT;
PRINT ”Disabled AutoShrink on ‘+@Database+”’
‘
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE()
END CATCH
FETCH NEXT FROM AutoShrinkFixer
INTO @Database
END
CLOSE AutoShrinkFixer
DEALLOCATE AutoShrinkFixer

Thank you all for reading and happy reconfiguring! The next part will be on Autogrowth Misconfiguration, stay tuned.