Troubleshooting Guide: SQL Server Error 5270 – Unmovable Page in Critical System Table

Why This Error Happens

Error 5270 occurs when SQL Server’s DBCC SHRINKFILE or auto-shrink operation attempts to relocate a page that belongs to a critical system table, which is physically “pinned” to its location and cannot be moved during the compaction process. This typically happens because certain system catalog pages (such as those backing sys.sysschobjs, sys.sysrowsets, or other base system tables) have hard dependencies that prevent relocation within the data file. The issue is frequently triggered by attempting to shrink a database file that is already near its minimum possible size, or when the database was upgraded from an older SQL Server version with legacy system table structures still present.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Database and File

• Capture the full error message, noting the file ID and page ID referenced (e.g., Page 1:152).
• Run the following to identify which database and logical file triggered the error:

SELECT DB_NAME(), name, physical_name FROM sys.database_files;

• Confirm whether the shrink was manual (DBCC SHRINKFILE) or triggered by auto-shrink.

Step 2: Disable Auto-Shrink and Avoid Aggressive Shrink Operations

• Check if auto-shrink is enabled on the database:

SELECT name, is_auto_shrink_on FROM sys.databases WHERE name = 'YourDatabaseName';

• Disable it to prevent recurring failures:

ALTER DATABASE YourDatabaseName SET AUTO_SHRINK OFF;

• Avoid running DBCC SHRINKFILE with TRUNCATEONLY or aggressive target sizes on data files containing system catalogs.

Step 3: Rebuild Indexes to Consolidate Pages Before Shrinking

• Rebuild clustered indexes on user tables first to reduce fragmentation and reclaim space logically rather than relying on shrink to compact physically:

ALTER INDEX ALL ON YourTableName REBUILD WITH (ONLINE = ON, MAXDOP = 4);

• After rebuilding major indexes, retry the shrink operation targeting a more conservative free space percentage (leave at least 10-15% free).

Step 4: Use a File Group Migration Instead of Direct Shrink

• If the page remains unmovable, create a new filegroup and migrate user tables/indexes to it:

ALTER DATABASE YourDatabaseName ADD FILEGROUP FG_New;
ALTER DATABASE YourDatabaseName ADD FILE (NAME = 'NewDataFile', FILENAME = 'D:\SQLData\NewDataFile.ndf') TO FILEGROUP FG_New;

• Rebuild indexes onto the new filegroup, then shrink the original (now emptier) primary data file, which reduces the chance of hitting pinned system pages.

Step 5: Validate Database Consistency

• Run DBCC CHECKDB to rule out underlying corruption as a contributing factor:

DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• Address any corruption found before attempting further shrink operations.

Alternative or Verification Steps

• Re-run DBCC SHRINKFILE (YourFileName, TargetSizeMB) after completing the steps above and confirm it completes without Error 5270 in the SQL Server Error Log or sys.messages.
• If the error persists specifically on system tables, consider leaving the file at its current size (shrinking system-heavy primary files is not recommended for production databases) and instead manage growth proactively through pre-sized files and regular index maintenance.