How to fix Error 7992 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: Error 7992 – Cannot Shrink Read-Only Database

Why This Error Happens

This error occurs because SQL Server’s shrink operation (DBCC SHRINKDATABASE or the Shrink task in SSMS) requires write access to the database files in order to reorganize pages and release unused space back to the operating system. When a database is explicitly set to READ_ONLY mode, SQL Server blocks the shrink operation entirely since it cannot modify the file structure. This typically happens when an administrator intentionally set the database to read-only for archival purposes, or when a database was restored/attached in a read-only state and a maintenance plan or script subsequently attempted an automated shrink.

Step-by-Step Resolution Guide

Step 1: Verify the Current Database State

  • Open SQL Server Management Studio (SSMS) and connect to the instance running SQL Server 2025.
  • Run the following query to confirm the read-only status:
  SELECT name, state_desc, is_read_only 
  FROM sys.databases 
  WHERE name = 'YourDatabaseName';
  • Confirm is_read_only returns 1 before proceeding.

Step 2: Switch the Database to Read-Write Mode

  • Ensure no active connections are using the database, as changing access mode requires exclusive access.
  • Execute the following T-SQL command to set the database to multi-user read-write mode:
  ALTER DATABASE [YourDatabaseName] SET READ_WRITE WITH ROLLBACK IMMEDIATE;
  • The WITH ROLLBACK IMMEDIATE clause forcibly disconnects any lingering sessions to apply the change instantly.

Step 3: Execute the Shrink Operation

  • Once the database reports is_read_only = 0, run the shrink command:
  DBCC SHRINKDATABASE (YourDatabaseName, 10);
  • Monitor the tempdb and transaction log usage during this process, as shrink operations on Windows Server 2025 can spike I/O temporarily on large data files.

Step 4: Revert to Read-Only (If Required)

  • If the read-only state was intentional (e.g., for a compliance archive), set it back after the shrink completes:
  ALTER DATABASE [YourDatabaseName] SET READ_ONLY WITH ROLLBACK IMMEDIATE;
  • Document this change in your maintenance logs to prevent future automated jobs from failing on this same error.

Alternative or Verification Steps

  • Verification: Re-run the sys.databases query from Step 1 to ensure is_read_only matches your desired end-state (0 or 1), and check the SQL Server Error Log to confirm Error 7992 no longer appears during subsequent maintenance windows.
  • Alternative: If you cannot take the database offline for a READ_WRITE switch due to high availability requirements, consider excluding this specific database from your shrink maintenance plan via the Maintenance Plan Wizard in SSMS by deselecting it under the “Databases” dropdown in the Shrink Database task.