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

by

Troubleshooting Guide: Error 5230 — DBCC CHECKCATALOG Cannot Be Run on tempdb

Why This Error Happens

This error occurs because DBCC CHECKCATALOG is designed to validate consistency between system catalog tables in user and system databases, but tempdb does not maintain persistent catalog metadata in the same way since it is recreated at every SQL Server service restart. SQL Server 2025 explicitly blocks this operation on tempdb because the catalog consistency checks are not applicable to a database that is rebuilt from scratch on startup. This is typically a scripting or automation error where a maintenance job or custom script is looping through all databases (including tempdb) and calling DBCC CHECKCATALOG indiscriminately, rather than excluding system databases that don’t support the check.

Step-by-Step Resolution Guide

Step 1: Identify the Source of the DBCC Call

  • Check SQL Server Agent Job steps, Maintenance Plans, or Ola Hallengren’s scripts for a loop that iterates over sys.databases.
  • Look specifically for T-SQL blocks using a cursor or sp_MSforeachdb that calls DBCC CHECKCATALOG('tempdb').
  • Review the SQL Server Error Log (Management > SQL Server Logs in SSMS) to confirm the exact timestamp and originating session.

Step 2: Modify the Script to Exclude tempdb

  • Add a WHERE clause filter to exclude database_id = 2 (tempdb) from any dynamic SQL generation.
  • Example filter for custom scripts:
  SELECT name FROM sys.databases WHERE name <> 'tempdb' AND state = 0
  • If using Ola Hallengren’s DatabaseIntegrityCheck stored procedure, ensure the @CheckCommands parameter does not explicitly force CHECKCATALOG on system databases without using the built-in @Databases = 'SYSTEM_DATABASES' logic, which already handles this exclusion correctly.

Step 3: Run DBCC CHECKDB Instead for tempdb Validation

  • Since CHECKCATALOG is unsupported, use DBCC CHECKDB(tempdb) instead if you need to validate the physical and logical integrity of temporary objects.
  • Execute the following in a New Query window in SSMS:
  DBCC CHECKDB ('tempdb') WITH NO_INFOMSGS, ALL_ERRORMSGS;
  • Note that CHECKDB inherently skips the catalog-specific checks for tempdb but still validates allocation and structural integrity.

Step 4: Update SQL Server Agent Job Schedules

  • Navigate to SQL Server Agent > Jobs in SSMS.
  • Open the properties of the failing job and update the Command text in the Job Step to reflect the corrected script from Step 2.
  • Save the job and manually trigger a test run via right-click > Start Job at Step… to confirm the error no longer appears in the job history.

Alternative or Verification Steps

  • Verify the fix by manually executing the corrected maintenance script or job; the job history in SSMS should show a “Succeeded” status with no Error 5230 referenced in the output log.
  • As an alternative, if using third-party backup/maintenance tools (e.g., Redgate, Idera), check for a software update or patch, as vendor-specific tools may need a version update to recognize the stricter tempdb restrictions introduced in SQL Server 2025.