• Repost - originally posted 19-07-2012

Autoclose and autoshrink - set them to off


By running a simple check of your database settings (use helpdb or query sys.databases) you may find databases that are set to autoclose and/or autoshrink. If so, you should change these settings as part of your best practices. Autoclosing a database frees up its resources when no user connection accesses the database, but adds overhead when the database next needs to be accessed and will cause the initial connection to be slower, as the database needs to be opened. Autoshrinking shrinks the data and log files . It may save you disk space but file growth will occur as the database grows and this will cause expensive IOs. This will more than likely occur when the database is at its busiest. The data and log files will become fragmented and further degrade performance.


Recent Posts

See All

Migrating SSIS packages to AWS

We are currently migrating a large amount of SSIS packages (SQL Server 2008R2) to SQL Server 2016 at AWS. It has been a learning exercise to get these packages running. What we have learnt (so far) is