Checking Every Database for Risky Settings

A newly inherited instance can hold one database with a setting nobody intended. Checking every database for risky settings puts those quiet exceptions in one readable report.

Begin With One Instance-Wide Query for Risky Settings

sys.databases provides a quick view of settings that are easy to overlook. The query below includes user databases and labels the owner. It deliberately reports values rather than declaring every difference an error. A development database and a production database can have legitimate differences. Your recovery requirements decide which ones matter.

I run this before making any inherited-server change. It catches risky settings that arrived through restores, old templates, or one-off fixes. The first pass is an inventory. Save it with the server name and time so you can compare later. An unexplained setting becomes a question for the owner, not an automatic ALTER DATABASE command.

SELECT name,
       state_desc,
       recovery_model_desc,
       page_verify_option_desc,
       is_auto_close_on,
       is_auto_shrink_on,
       SUSER_SNAME(owner_sid) AS owner_name
FROM sys.databases
WHERE database_id > 4
ORDER BY name;

Question AUTO_SHRINK

AUTO_SHRINK periodically attempts to reclaim space. In a database that needs that space again, the result is a shrink-and-grow cycle. It can add file growth events and index fragmentation without solving a capacity problem. A setting inherited from a small demonstration database does not belong in production by default. Check why it was enabled before you disable it.

I have seen teams celebrate reclaimed space while the same file grew during the next workload peak. That is a very short celebration. Size data and log files for known demand, monitor free space, and use a one-time shrink only when a specific event permanently reduced the needed size. Ask what capacity plan AUTO_SHRINK was supposed to replace.

Question AUTO_CLOSE

AUTO_CLOSE releases database resources after the last connection closes and reopens them for the next connection. That behavior can add repeated startup work for lightly used databases. On a shared production instance, the setting is usually an accidental inheritance from an old template or small single-user scenario. Check the actual workload and platform guidance before changing it.

A database that looks idle between scheduled jobs is still an operational database. Repeated opens can complicate troubleshooting. If AUTO_CLOSE is on, record the reason and test the effect of disabling it. Do not simply assume that an unused database is safe to remove. Database ownership and application schedules need to be checked first.

Verify Page Protection

PAGE_VERIFY should normally be CHECKSUM on current SQL Server databases. It helps SQL Server detect certain page corruption when pages are read. A value of NONE or TORN_PAGE_DETECTION deserves investigation. Changing the setting does not retroactively add checksums to pages already on disk. Those pages gain checksums when rewritten through normal activity or maintenance.

Use a focused query to identify exceptions. If you change a database to CHECKSUM, keep integrity checks and backup validation in place. Page verification is one layer of evidence, not a substitute for DBCC CHECKDB or tested restores. The word checksum can sound like a complete guarantee. It is not.

SELECT name,
       page_verify_option_desc
FROM sys.databases
WHERE database_id > 4
  AND page_verify_option_desc <> N'CHECKSUM'
ORDER BY name;

Match Recovery Model to Restore Goals

FULL, SIMPLE, and BULK_LOGGED recovery models support different log backup and restore possibilities. A FULL database without log backups can grow its log and still fail the intended point-in-time recovery plan. A SIMPLE database cannot offer point-in-time restore from log backups. The correct setting follows the recovery objective and backup schedule, not a universal template.

I ask for the restore requirement before recommending a model. How far back can the business afford to lose data? Then I check whether actual backups meet that answer. A setting that looks correct in sys.databases can still be operationally wrong if the job is missing or failing. Inventory and backup evidence belong in the same review.

Resolve Database Ownership

The database owner is a security and maintenance detail. An owner tied to a departed employee or disabled login creates avoidable ambiguity. Some features and cross-database behavior make ownership especially important. Review the owner name, confirm the intended principal, and record any required change. Do not assume that every database should be owned by sa without understanding local policy.

I include the owner in the initial inventory because it is easy to miss in a settings-only report. A forgotten owner is often a sign that the database arrived without a complete handoff. Ask who approves schema changes and who owns backup and restore decisions. The SID in metadata is evidence; the human owner still needs to be named.

Check Compatibility Separately From Risky Settings

Compatibility level changes query behavior and optimizer choices. A restored database keeps its level until someone changes it deliberately. Include the level in a second report if you are preparing an upgrade or investigating different plans across environments. Do not change it merely to make every row match the engine version. Query testing and a rollback plan matter.

The query below exposes the current level alongside state and collation. Compare it to the application support matrix and tested workload. A level that looks old can be an intentional temporary choice during an upgrade. It should still have an owner and a review date.

SELECT name,
       compatibility_level,
       collation_name,
       state_desc
FROM sys.databases
WHERE database_id > 4
ORDER BY name;

Turn Risky Settings Into Decisions

A useful report distinguishes urgent risk, planned review, and approved exception. A database with failed backups deserves attention before a cosmetic settings difference. Record the database, setting, current value, desired value, reason, owner, and intended change window. Keep the original snapshot so you can verify the result afterward.

I avoid bulk alteration scripts for this first review. Each database has its own recovery and application needs. For risky settings, test the correction on a suitable environment and confirm its effect. The value of the inventory is that no database stays invisible. The value of the follow-up is that every changed setting has a reason.

Related reading on this blog: Set AUTO_CLOSE Database Option to OFF for Better Performance and Monitoring Database Autogrowth Settings.

A database setting is not safe because it is familiar, it is safe when its effect matches the recovery and workload plan.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.