• Home
  • About
  • Blog
  • Speaking
  • Contact

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

8 December 2015SQL StijnSQL Maintenance, SQL Server Misconfiguration ChroniclesNo Comments

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.

The 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 STATEMENTS
—                     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 Default Memory Settings Misconfiguration!
(<a href="https://swyssql.wordpress.com/2015/12/17/the-sql-server-misconfiguration-chronicles-part-2-instance-memory-settings//)

Stay Tuned!

Tags: AutoClose, AutoShrink, Database, Databases, Script, SQL SERVER, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server Database, SQL Server Misconfiguration, SQL Server Script

Related Articles

TEMPDB: The Ghost of VersionStore

30 October 2015SQL Stijn

THE SQL SERVER MISCONFIGURATION CHRONICLES: PART 3 Database Files

24 December 2015SQL Stijn

Clustered Columnstore Index TupleMove Magic

6 November 2014SQL Stijn

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

Recent Posts

  • Building a Modern Lakehouse Data Warehouse with Azure Synapse Analytics: Moving your Database to the lake
  • Azure Synapse Dedicated SQL Pools: Workload Management
  • Synapse Spark – Playing with Data Frames with Python
  • Synapse Spark – Reading CSV files from Azure Data Lake Storage Gen 2 with Synapse Spark using Python
  • Azure Synapse Analytics – Serverless SQL Pools: Data Elimination

Recent Comments

    Archives

    • June 2022
    • December 2021
    • May 2021
    • April 2021
    • March 2021
    • February 2021
    • January 2021
    • December 2020
    • August 2016
    • July 2016
    • March 2016
    • February 2016
    • December 2015
    • October 2015
    • August 2015
    • March 2015
    • December 2014
    • November 2014
    • July 2014
    • February 2014
    • January 2014
    • December 2013

    Categories

    • Azure Synapse Analytics
    • Building a Modern Lakehouse Data Warehouse
    • Geen categorie
    • MS PDW
    • SQL 2016
    • SQL Maintenance
    • SQL Server Migration
    • SQL Server Misconfiguration Chronicles
    • SQL2014
    • Troubleshooting

    Contact Stijn now


    © SQL Stijn 2017 - Privacy Policy - Cookie Policy
    Manage Cookie Consent
    To provide the best experiences, we use technologies like cookies to store and/or access device information.
    Functional Always active
    The technical storage or access is strictly necessary for the legitimate purpose of enabling the use of a specific service explicitly requested by the subscriber or user, or for the sole purpose of carrying out the transmission of a communication over an electronic communications network.
    Preferences
    The technical storage or access is necessary for the legitimate purpose of storing preferences that are not requested by the subscriber or user.
    Statistics
    The technical storage or access that is used exclusively for statistical purposes. The technical storage or access that is used exclusively for anonymous statistical purposes. Without a subpoena, voluntary compliance on the part of your Internet Service Provider, or additional records from a third party, information stored or retrieved for this purpose alone cannot usually be used to identify you.
    Marketing
    The technical storage or access is required to create user profiles to send advertising, or to track the user on a website or across several websites for similar marketing purposes.
    Manage options Manage services Manage vendors Read more about these purposes
    View preferences
    {title} {title} {title}