Database options: compatibility level, AUTO_CLOSE, AUTO_SHRINK (what NOT to use)

Section 7 — Lesson 7: Database Options (Compatibility Level, AUTO_CLOSE, AUTO_SHRINK — What NOT to Use)

Welcome back. A job-ready DBA does not just “install SQL Server and hope for the best.”
DBAs harden an instance by eliminating settings that quietly create performance problems, instability,
and avoidable outages. Many of these problems come from database options that were enabled accidentally,
carried over from old environments, or left at defaults that do not match production behavior.

In this lesson, you will learn three critical database options:
Compatibility Level, AUTO_CLOSE, and AUTO_SHRINK.
You will learn what they do, why AUTO_CLOSE and AUTO_SHRINK are commonly considered harmful,
and how to audit and standardize these settings with evidence.

Lesson description: You will learn how compatibility level affects SQL Server behavior and query optimization,
why DBAs manage it intentionally during upgrades, and how to validate changes safely. You will also learn why
AUTO_CLOSE and AUTO_SHRINK are widely treated as “do not use” options in production:
they can cause repeated open/close overhead, cache churn, file fragmentation, and unpredictable performance.
You will practice auditing all databases for these options, fixing them safely, and documenting the change.

1) Compatibility Level (What It Is and Why It Matters)

Every SQL Server database has a compatibility level.
It tells SQL Server which behavior “mode” to apply for that database,
especially for query optimizer behaviors and certain language features.

Why DBAs care: During SQL Server upgrades, databases may keep an older compatibility level to reduce risk.
But staying on a very old compatibility level can block optimizer improvements and newer features.

1.1 Practical DBA rules for compatibility level

• Do not change compatibility level casually in production.
• When you do change it, you must baseline performance and test critical queries.
• After changing compatibility, monitor plan changes and regressions.
• Document the change and have a rollback plan.

Lab goal: Learn to audit and understand the setting now, so you can manage it professionally later.

2) AUTO_CLOSE (Why DBAs Avoid It in Production)

AUTO_CLOSE closes the database and releases resources when the last user disconnects.
This may sound “efficient,” but in real workloads it usually causes problems.

Why it’s harmful: When the database closes and later reopens, SQL Server may have to rebuild internal state
and reload things that would normally stay warm. That leads to:

• unnecessary overhead on reconnect
• cache churn / cold cache behavior
• inconsistent response time (fast sometimes, slow after reopen)
• extra work for the engine that provides no benefit in most production systems

DBA standard: AUTO_CLOSE should almost always be OFF for production databases.
It is commonly seen in desktop/single-user scenarios, not server workloads.

3) AUTO_SHRINK (Why DBAs Avoid It in Production)

AUTO_SHRINK tries to automatically shrink database files to reclaim space.
This is one of the most universally discouraged options in SQL Server administration.

Why it’s harmful: Shrinking moves pages around inside the data file, which can:

• cause severe index fragmentation
• create I/O spikes and performance instability
• lead to a “shrink-grow-shrink” loop (you shrink, then you grow again later)
• increase maintenance workload and reduce predictability

DBA standard: AUTO_SHRINK should be OFF in production.
If you truly must reclaim space, DBAs do controlled one-time shrink operations (rare),
and then fix the root cause and pre-size appropriately.

4) Evidence First: Audit All Databases for These Options

Run this audit query. It shows compatibility level, AUTO_CLOSE, and AUTO_SHRINK for all databases.
This is the kind of script a DBA runs during server onboarding.

SELECT
    name AS database_name,
    compatibility_level,
    is_auto_close_on,
    is_auto_shrink_on,
    state_desc,
    recovery_model_desc
FROM sys.databases
ORDER BY name;

Save output to:
C:\DBA\Baselines\InstanceConfig\db_options_before_YYYYMMDD_HHMM.txt

5) Fixing AUTO_CLOSE and AUTO_SHRINK Safely

In your lab, you will practice turning off AUTO_CLOSE and AUTO_SHRINK for a user database.
Use a controlled change:

1. Identify the target database.
2. Capture baseline (section 4 audit output).
3. Apply the change with ALTER DATABASE.
4. Re-run the audit query and capture “after.”
5. Document the change and rollback plan.

5.1 Example (Replace database name)

-- Replace [YourDatabaseName] with your actual database name
ALTER DATABASE [YourDatabaseName] SET AUTO_CLOSE OFF;
ALTER DATABASE [YourDatabaseName] SET AUTO_SHRINK OFF;

Rollback plan (if needed):

-- Rollback example (rarely recommended in real production)
ALTER DATABASE [YourDatabaseName] SET AUTO_CLOSE ON;
ALTER DATABASE [YourDatabaseName] SET AUTO_SHRINK ON;

In real environments, rollback would be unusual because these options are typically disabled for stability.
But as a DBA you always document the rollback path anyway.

6) Managing Compatibility Level (How DBAs Do It Professionally)

Changing compatibility level can change query plans. That can improve performance or cause regressions.
DBAs treat it like a controlled release:

• Baseline critical query performance and CPU/I/O before change.
• Change compatibility level in a maintenance window or controlled deployment.
• Use Query Store (later section) to detect plan regressions and force known-good plans if needed.
• Monitor for hours/days depending on workload and risk tolerance.

In your lab, you can practice the command:

-- Example: set compatibility level (replace level and database as needed)
-- You should only do this intentionally and document why.
ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 160;

Lab guidance: You do not need to change compatibility level unless you are practicing the workflow.
The most important skill is understanding the impact and how to validate safely.

7) Validate and Capture “After” Evidence

Re-run the audit query and save your after snapshot:

Save output to:
C:\DBA\Baselines\InstanceConfig\db_options_after_YYYYMMDD_HHMM.txt

Practice (Required)

1. Run the database options audit and save:
   db_options_before_YYYYMMDD_HHMM.txt
2. Identify at least one user database in your lab (or create one) and ensure:
   AUTO_CLOSE = OFF and AUTO_SHRINK = OFF.
3. Re-run the audit and save:
   db_options_after_YYYYMMDD_HHMM.txt
4. Create a change note:
   C:\DBA\Logs\Changes\change_note_db_options_YYYYMMDD_HHMM.txt
   (Include: which DB, what changed, why, and rollback path.)
5. In your DBA notebook, answer:
   • Why does AUTO_CLOSE cause inconsistent performance?
   • Why does AUTO_SHRINK create fragmentation and instability?
   • Why must compatibility level changes be tested and monitored?
   • What tool helps DBAs manage plan regressions after compatibility changes? (Hint: Query Store)

Lesson Summary

• Compatibility level influences optimizer behavior and must be managed intentionally, especially during upgrades.
• AUTO_CLOSE is usually harmful for server workloads and should be OFF for production databases.
• AUTO_SHRINK is widely discouraged because it causes fragmentation, churn, and unpredictable performance.
• Job-ready DBAs audit options, standardize risky settings, and document every change with before/after evidence.

Next Lesson: Lab — Apply a “Baseline Configuration” and Document Every Change

Next, you will bundle your work from Section 7 into a professional baseline configuration:
memory, MAXDOP/CTFP, file growth, tempdb, Agent checks, and database options—then produce a documented report
you can use as portfolio evidence.