A database keeps reopening or its files keep shrinking while nobody runs maintenance. Check auto shrink and auto close before searching for a more complicated explanation.
Read Auto Shrink and Auto Close Together
AUTO_SHRINK schedules automatic attempts to reduce database file space. AUTO_CLOSE closes a database and releases its resources after its last user connection leaves. Their names sound tidy, but a busy server needs predictable access and capacity.
I check these options early when inheriting an instance. They are easy to inspect and easy to overlook. A database can carry an old setting long after the workload that originally motivated it has changed.
Read the catalog before generating any corrections. Include state, recovery model, and read-only status so the resulting list has context. The first query reports user databases without changing anything.
SELECT name, state_desc, recovery_model_desc, is_read_only,
is_auto_shrink_on, is_auto_close_on
FROM sys.databases
WHERE database_id > 4
AND (is_auto_shrink_on = 1 OR is_auto_close_on = 1)
ORDER BY name;A returned row deserves investigation rather than an automatic batch execution. Confirm who owns the database and how it is used. The setting is a useful lead, not a complete explanation of every performance complaint.
Catalog visibility depends on the collecting account. Use an approved account that can see the intended database population. Record the instance name and capture time alongside the list so later checks refer to the same server.
Understand the Auto Shrink and Growth Cycle
Shrinking moves pages toward the beginning of a data file so trailing space can be released. Later growth needs that capacity again. A workload cycling between shrinking and growing spends resources rearranging space it still needs.
Page movement can increase logical fragmentation. File growth also needs operational planning, especially for the transaction log. Repeated changes to file size are therefore more than a cosmetic difference in a storage report.
AUTO_SHRINK does not solve the reason a transaction log cannot reuse its space. Investigate the recovery model, log backups, long transactions, and other reuse blockers separately. A smaller file size in a report will not unblock a transaction.
Keep normal free space inside data files for expected growth. That space is capacity already available to SQL Server. Returning it to Windows is useful only when there is a real storage need and a reviewed reason the database will not immediately need it again.
A database that keeps repacking its own suitcase is not getting more organized. It is spending time packing. Turning the option off removes that automatic behavior, but it does not reconstruct an earlier file layout.
Understand What Closing Costs the Next Caller
AUTO_CLOSE releases database resources after the last connection disconnects. The next caller must reopen the database and acquire resources again. Short-lived connections can repeatedly pay that cost under an otherwise ordinary workload.
Database closure also affects cached resources associated with that database. A frequently reopened database therefore has different behavior from one staying available between requests. Connection pooling influences how regularly the last connection disappears.
Do not confuse AUTO_CLOSE with idle-session cleanup. It does not enforce application session ownership, identify abandoned transactions, or correct a connection leak. Those are separate questions with separate diagnostics.
Check whether the database belongs to a deliberately intermittent workload before changing its operation. A continuously used application generally needs predictable availability. Make the database setting follow that workload rather than an old installation choice.
Generate Commands You Can Review
Build the corrective statements with QUOTENAME around database identifiers. Generate only the settings that are enabled. Returning text makes the intended actions visible before anyone executes them.
SELECT name AS DatabaseName,
CASE WHEN is_auto_shrink_on = 1 THEN
N'ALTER DATABASE ' + QUOTENAME(name) + N' SET AUTO_SHRINK OFF;'
ELSE N'' END AS ShrinkCorrection,
CASE WHEN is_auto_close_on = 1 THEN
N'ALTER DATABASE ' + QUOTENAME(name) + N' SET AUTO_CLOSE OFF;'
ELSE N'' END AS CloseCorrection
FROM sys.databases
WHERE database_id > 4 AND state_desc = N'ONLINE'
AND is_read_only = 0 AND source_database_id IS NULL
AND (is_auto_shrink_on = 1 OR is_auto_close_on = 1)
ORDER BY name;This query does not execute the generated text. Review the targeted database names and options, then choose the approved execution window. An offline or read-only database excluded here still belongs in the wider inventory if its settings need review.
Keep the before values with the approved change. If only one option needs correction, do not bundle unrelated database alterations into the same action. Small reviewed changes are easier to explain and verify.
I check the generated identifiers before execution, especially on instances with unusual database names. A script that works only for simple names will fail precisely when a less familiar database needs attention.
Check the Template for New Databases
Model supplies settings and objects for newly created databases. Inspect its options separately from the user database list. Correcting today's databases while leaving an unsuitable template simply creates tomorrow's repeats.
SELECT name, is_auto_shrink_on, is_auto_close_on,
recovery_model_desc
FROM sys.databases
WHERE name = N'model';Do not change every model setting because one option was unsuitable. Model's recovery model, file configuration, and existing objects deserve their own review. Keep the requested correction narrow and record its effect on future database creation.
After an approved change, create a disposable database through the normal process and inspect its inherited options. Also review application-driven database creation scripts. An explicit option in those scripts can override what you expected from model.
Existing databases do not automatically inherit later corrections to model. They need their own reviewed changes. Restored databases also bring settings from their source, so include them in your intake checks.
Verify Settings and Investigate Earlier Damage
Run the inventory again after the approved execution. Confirm that the intended options are off and that unrelated databases still have their reviewed values. Keep the post-change snapshot alongside the original list.
SELECT name, is_auto_shrink_on, is_auto_close_on
FROM sys.databases
WHERE database_id > 4 OR name = N'model'
ORDER BY name;Which workload symptom still exists after the setting is corrected? Investigate it using fresh evidence. The change prevents a behavior, but it does not prove that behavior caused every delay the application experienced.
Past shrink work can leave fragmented indexes and poor page density. Inspect important indexes and their workload before choosing maintenance. A rebuild has logging, locking, space, and time costs of its own.
Review file growth settings and expected capacity separately. Turning off shrink leaves room for growth inside the file. It does not establish that the disk, log backup schedule, or workload retention is now correctly sized.
Keep the settings check in your recurring inventory. New restores and newly created databases can introduce enabled options later. The useful outcome is predictable database behavior supported by an explanation of why each setting belongs there.
Auto shrink deserves a separate review from automatic growth. Record why auto shrink is enabled before replacing it with an approved capacity and maintenance policy.
Related reading on this blog: Manage Database Size with DBCC SHRINKDATABASE and WAIT_AT_LOW_PRIORITY and Why DBCC SHRINKFILE Crawls and How to Watch Its Progress.
A smaller file is not automatically a healthier database, it is one storage decision within a workload that still needs room.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
