_Originally published at SQL & Cloud Lab._ Database corruption is rare, but when it happens, the difference between a five-minute fix and a lost weekend is how early you find it. SQL Server will happily keep running on a damaged database until a query touches the bad page. **DBCC CHECKDB** is the tool that finds damage before your users do, and knowing how to run it and what to do with the results is a core DBA skill. ## What corruption looks like Most corruption comes from the storage layer: a disk, controller, driver or SAN firmware writing a page incorrectly. SQL Server detects it when a page is read and raises one of these errors: * **Error 823:** the operating system reported an I/O error reading or writing the page. * **Error 824:** the read succeeded, but the page failed a logical check, usually a bad page checksum. * **Error 825:** a read had to be retried before it succeeded. The data is fine for now, but the storage is failing. All three are serious. Set up alerts for them (see below) so a single occurrence reaches a human. ## Make sure page verification is on Page checksums let SQL Server detect corruption on read. They are the default for new databases, but databases upgraded from very old versions can still use `TORN_PAGE_DETECTION` or `NONE`: SELECT name, page_verify_option_desc FROM sys.databases WHERE page_verify_option_desc <> 'CHECKSUM'; ALTER DATABASE [Payroll] SET PAGE_VERIFY CHECKSUM; A page only gets a checksum the next time it is written, so existing pages are protected gradually. ## Run DBCC CHECKDB DBCC CHECKDB (N'Payroll') WITH NO_INFOMSGS, ALL_ERRORMSGS; * `NO_INFOMSGS` hides the per-table progress messages, so only real problems are shown. * `ALL_ERRORMSGS` lists every error instead of stopping at the first 200 per object. If the output is empty, the database is consistent. CHECKDB runs online using an internal database snapshot, so users can keep working, but it is I/O and CPU heavy. ### Options for large databases * **`WITH PHYSICAL_ONLY`** checks page structure and checksums but skips the expensive logical checks. Much faster, and catches most storage corruption. A common pattern is PHYSICAL_ONLY on weekdays and a full check at the weekend. * **`DBCC CHECKTABLE`** and **`DBCC CHECKFILEGROUP`** let you split a huge database across several nights. * **Offload to a restored copy.** Restore last night's full backup on another server and run CHECKDB there. It checks the backup at the same time, which is a valuable bonus. It does not replace occasional checks on production, because corruption in memory or on the live disk will not appear in an older backup. ### How often? At least weekly for every database, ideally more often for critical ones. The rule of thumb: run CHECKDB more often than you delete old backups, so you always have a clean backup from before the corruption appeared. ## When CHECKDB reports errors Do not panic, and do not run repair first. Work through these steps. ### 1. Read the last line of the output CHECKDB ends with a summary and the **minimum repair level** needed. `repair_rebuild` means only nonclustered index damage, which can be fixed without data loss. `repair_allow_data_loss` means data pages are affected. ### 2. Find out what is damaged Each error names an object ID and index ID: * **Index ID 2 or higher:** a nonclustered index. Drop and recreate it (or rebuild offline). No data is lost, because the index can be rebuilt from the table. * **Index ID 0 or 1:** the heap or clustered index, which is the actual data. This is where you need backups. SELECT OBJECT_SCHEMA_NAME(123456789) AS schema_name, OBJECT_NAME(123456789) AS table_name; ### 3. Check suspect pages SQL Server records pages that failed with 823 or 824 errors: SELECT database_id, file_id, page_id, event_type, error_count, last_update_date FROM msdb.dbo.suspect_pages; ### 4. Restore from backup For damaged data pages, restoring is the correct fix. Options: * **Page restore:** restore only the damaged pages from a full backup, then apply log backups. Fast for a handful of pages and available in the full recovery model (online in Enterprise edition). * **Full restore** of the database to a point before the corruption, plus log backups to bring it up to date. Often the simplest when many pages are affected. ### 5. Repair only as a last resort ALTER DATABASE [Payroll] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DBCC CHECKDB (N'Payroll', REPAIR_ALLOW_DATA_LOSS); ALTER DATABASE [Payroll] SET MULTI_USER; The name is honest: it **deletes** damaged pages and whatever rows were on them to make the database consistent again. It does not tell you which business data disappeared, and it can break referential integrity. Use it only when there is no usable backup, and take a backup of the damaged database first. ### 6. Fix the cause Corruption almost always means a hardware or driver problem. Check the Windows system log, storage logs and firmware versions. If you only restore, it will come back. ## Alert on corruption errors EXEC msdb.dbo.sp_add_alert @name = N'Error 823', @message_id = 823, @severity = 0, @include_event_description_in = 1; EXEC msdb.dbo.sp_add_alert @name = N'Error 824', @message_id = 824, @severity = 0, @include_event_description_in = 1; EXEC msdb.dbo.sp_add_alert @name = N'Error 825', @message_id = 825, @severity = 0, @include_event_description_in = 1; -- Then link each alert to an operator, for example: EXEC msdb.dbo.sp_add_notification @alert_name = N'Error 824', @operator_name = N'DBA Team', @notification_method = 1; Alerts on severities 19 to 25 are also worth adding, because they cover other fatal errors. ## Azure SQL Database and Managed Instance Microsoft runs integrity checks and handles storage-level corruption for Azure SQL Database, so you do not schedule CHECKDB there yourself (you can still run it). On SQL Server on Azure VMs, everything in this post applies exactly as on-premises. ## Quick checklist * Use PAGE_VERIFY CHECKSUM on every database. * Run CHECKDB at least weekly; offload to a restored backup where possible. * Alert on errors 823, 824 and 825. * Rebuild damaged nonclustered indexes; restore damaged data pages. * Use REPAIR_ALLOW_DATA_LOSS only when no backup exists. * Investigate the storage after any corruption. **Related reading:** * How to Monitor SQL Server Agent Job Failures * How to Find Slow Queries in SQL Server * SQL Server Index Tuning for Beginners _More SQL Server guides on my blog, SQL & Cloud Lab._

Learn how to spot data skew in Synapse SQL pools using DMVs and DBCC, and when to change your hash key or switch to round-robin distribution.

Independent proposals to increase councillor pay are necessary to stop the rich dominating public service as they do in the US according to a Flintshire Council committee.

Independent proposals to increase councillor pay are necessary to stop the rich dominating public service as they do in the US according to a Flintshire Council committee.

The independent body responsible for setting councillor pay has recommended the leaders of Wrexham and Flintshire Councils are given a £2,131 pay rise next year. The recommendations – the second proposed by the Democracy and Boundary Commission Cymru (DBCC) since it took responsibility for remuneration last year – would see the leaders pay increase 3% from £71,025 to £73,156. If approved, the increase would mean that since 2022/23, pay for the role of Leader of the Council will have gone up by 29% – 4.7% more than wage growth for the average worker during the same time period according to the Consumer Price Index (CPI).

A monitoring tool asks for sysadmin on every instance it watches. You ask why, and part of the answer is a trace flag that the tool turns on at startup. `DBCC TRACEON` needs sysadmin, so the tool needs sysadmin.[1] I ran into this with SolarWinds Database Performance Analyzer (DPA), which turns on trace flag 2861. A trace flag is a switch that changes how SQL Server behaves. The flag isn’t in Microsoft’s list of documented trace flags.[2] One person who says he works with SQL Server at Microsoft wrote that it did nothing after the SQL Server 2000 service packs. I wanted to know whether it changes anything on current versions, so I tested it. ## What SolarWinds Says the Flag Does A SolarWinds blog post from 2016 explains the idea.[3] DPA samples sessions every second by default. It only retrieves statement text (with `fn_get_sql`, using the SQL handle) about every two minutes. The plan cache is the memory where SQL Server keeps compiled query plans. A zero-cost plan is a cached plan with no compile cost, such as one for an ad-hoc statement, a `SET` statement or a `COMMIT TRAN`. Under memory pressure, SQL Server can evict these plans first. If that happens before DPA asks for the text, the text is gone. The post says DPA turns on trace flag 2861 by default to keep those plans in the cache longer. It also says the flag can add plan cache use on busy ad-hoc workloads, such as those from an object-relational mapper (ORM). SolarWinds recommends turning it off for those systems. ## What the Other Side Says Sean Gallardy, who says in his post that he works with SQL Server at Microsoft, wrote that the flag existed only in SQL Server 2000 SP3 and SP4. He wrote that Microsoft removed it before SQL Server 2005 shipped.[4] He says he reached that conclusion by reading the source code from 2005 RTM through 2019. His 2021 update says DPA still runs a command to turn it on. I can’t read SQL Server source code. What I can do is measure what happens to zero-cost plans with the flag off and on. ## The Test I used two instances on one workstation: SQL Server 2019 (15.0.4500.2, Developer) and SQL Server 2025 (17.0.5015.4, Enterprise Developer). Each ran with `max server memory` at 1024 MB, which I raised back to 2048 MB afterward. Each test used a scratch database, and I dropped it at the end. Each run followed the same steps: 1. Set the flag to the state under test with `DBCC TRACEOFF` or `DBCC TRACEON(2861, -1)`. 2. Clear the plan cache with `DBCC FREESYSTEMCACHE(N'SQL Plans')`. 3. Run 1,000 “sentinel” statements of each of three kinds through `EXEC`: a variable assignment, `SET ROWCOUNT 0`, and `BEGIN TRAN; COMMIT TRAN;`. Each carries a unique comment tag, so each gets its own plan. 4. Count how many sentinel plans are in `sys.dm_exec_cached_plans`. 5. Apply memory pressure by running more distinct statements of the same three kinds, with a different tag. 6. Count the sentinel plans again, and record `DBCC TRACESTATUS(2861)` before and after. The core of the workload looks like this: Transact-SQL USE [tf2861_scratch]; DECLARE @i int = 1; DECLARE @s nvarchar(400); WHILE @i <= 1000 BEGIN SET @s = N'DECLARE @v int; SELECT @v = ' + CONVERT(nvarchar(12), @i) + N' OPTION (MAXDOP 1); /*S1:' + CONVERT(nvarchar(12), @i) + N'*/'; EXEC (@s); SET @s = N'SET ROWCOUNT 0 /*S2:' + CONVERT(nvarchar(12), @i) + N'*/'; EXEC (@s); SET @s = N'BEGIN TRAN; COMMIT TRAN; /*S3:' + CONVERT(nvarchar(12), @i) + N'*/'; EXEC (@s); SET @i += 1; END; SELECT SUBSTRING([t].[text], CHARINDEX(N'/*S', [t].[text]) + 2, 2) AS [kind], COUNT_BIG(*) AS [sentinel_plans] FROM [sys].[dm_exec_cached_plans] AS [cp] CROSS APPLY [sys].[dm_exec_sql_text]([cp].[plan_handle]) AS [t] WHERE [cp].[cacheobjtype] = N'Compiled Plan' AND [t].[text] LIKE N'%/*S[123]:%' GROUP BY SUBSTRING([t].[text], CHARINDEX(N'/*S', [t].[text]) + 2, 2); 1234567891011121314151617181920212223242526272829303132 | USE [tf2861_scratch]; DECLARE @i int = 1;DECLARE @s nvarchar(400); WHILE @i <= 1000BEGIN SET @s = N'DECLARE @v int; SELECT @v = ' + CONVERT(nvarchar(12), @i) + N' OPTION (MAXDOP 1); /*S1:' + CONVERT(nvarchar(12), @i) + N'*/'; EXEC (@s); SET @s = N'SET ROWCOUNT 0 /*S2:' + CONVERT(nvarchar(12), @i) + N'*/'; EXEC (@s); SET @s = N'BEGIN TRAN; COMMIT TRAN; /*S3:' + CONVERT(nvarchar(12), @i) + N'*/'; EXEC (@s); SET @i += 1;END; SELECT SUBSTRING([t].[text], CHARINDEX(N'/*S', [t].[text]) + 2, 2) AS [kind], COUNT_BIG(*) AS [sentinel_plans]FROM [sys].[dm_exec_cached_plans] AS [cp]CROSS APPLY [sys].[dm_exec_sql_text]([cp].[plan_handle]) AS [t]WHERE [cp].[cacheobjtype] = N'Compiled Plan' AND [t].[text] LIKE N'%/*S[123]:%'GROUP BY SUBSTRING([t].[text], CHARINDEX(N'/*S', [t].[text]) + 2, 2); ---|--- I ran 240 runs in three sets. Every run recorded the flag state, and `DBCC TRACESTATUS(2861)` reported 1 during every flag-on run and 0 during every flag-off run. So the flag was set during the flag-on runs. That doesn’t show it does anything. ## Results **Heavy pressure.** With 120,000 pressure statements per run, no sentinel plan survived in any run, on either version, with the flag on or off. That’s 10 runs per setting per instance. **Graded pressure.** Next I ran five alternating pairs at each of four pressure levels. The table shows the average number of surviving sentinel plans out of 1,000 per statement kind. “n” is the number of loop iterations, and each iteration runs three pressure statements. Instance | n | Flag off | Flag on ---|---|---|--- SQL Server 2019 | 2,000 | 800 | 812 SQL Server 2019 | 5,000 | 0 | 95 SQL Server 2019 | 10,000 | 0 | 0 SQL Server 2019 | 20,000 | 0 | 0 SQL Server 2025 | 2,000 | 1,000 | 984 SQL Server 2025 | 5,000 | 942 | 866 SQL Server 2025 | 10,000 | 0 | 0 SQL Server 2025 | 20,000 | 0 | 0 On SQL Server 2019 at n = 5,000, the flag-on runs retained plans and the flag-off runs didn’t. Only two of the five flag-on runs kept any plans (334 and 140 for the first statement kind). The other three kept none, and the flag-off runs kept none. Five runs per setting can’t tell a real effect from variation, so I repeated this pressure level with more runs. **Randomized order.** To remove that pattern I ran n = 5,000 again, 30 runs per setting per instance, in random order. The table shows how many of the 30 runs kept at least one sentinel plan, and the average number kept per 1,000. Instance | Flag | Runs with survivors | Average kept ---|---|---|--- SQL Server 2019 | off | 9 of 30 | 130 SQL Server 2019 | on | 5 of 30 | 86 SQL Server 2025 | off | 22 of 30 | 662 SQL Server 2025 | on | 25 of 30 | 682 The counts vary a lot from run to run, and the two settings overlap on both versions. Flag-on was lower on 2019 and higher on 2025. I can’t call either difference real. ## What This Does and Doesn’t Show On these two builds, with this workload, I didn’t see a reliable difference. Zero-cost plans stayed in the plan cache about as often with the flag on as with it off. That fits what Gallardy says, but my test can’t confirm his claim about the source code. One workload can’t prove a negative. Some limits to keep in mind: * I tested one build of each version, on one workstation, with a synthetic workload. * I didn’t run DPA. I tested the flag, not DPA’s behaviour. I can’t say what DPA does on your instances, or which version of DPA you run. * Eviction depends on the memory state at the time. I only cleared the plan cache before each run. * The SolarWinds post is from 2016. I tested SQL Server 2019 and 2025 only, so I can’t say anything about the versions that were current then. * I counted surviving plans. I didn’t measure how much statement text DPA would lose, or how often. ## What This Means for Permissions If DPA turns the flag on with `DBCC TRACEON`, the login it uses needs sysadmin. If sysadmin is requested only so the tool can set this flag, ask the vendor what else needs it. I’d also ask which DPA features rely on the flag before touching the login, and test narrower permissions on a non-production instance first. The SolarWinds post says you can turn the flag off if plan cache growth is a problem. It says the cost is that DPA won’t capture the text of some statements. A quick way to see what a monitored instance is running: Transact-SQL DBCC TRACESTATUS(-1); 1 | DBCC TRACESTATUS(-1); ---|--- If 2861 shows up in the output, something turned it on. I’d check whether it’s a startup parameter, or something a monitoring tool runs after it connects. ## Try It Yourself Run the test on a non-production instance. Set `max server memory` low enough to see eviction, but not so low that you lock yourself out. I used 1024 MB on a machine with 31 GB of RAM. Note your current value first. A setting that’s too low can lock you out, and recovery then means starting the instance in single-user mode. Write down your build number, and run the flag-on and flag-off cases in random order. If your numbers differ from mine, I’d like to see them. If you’ve tested this flag, or run DPA with narrower permissions, I’d like to hear what you found. Find me on Bluesky or LinkedIn. ## References 1. DBCC TRACEON (Transact-SQL) - Microsoft Learn. States that the command requires membership in the sysadmin fixed server role. ↩ 2. DBCC TRACEON - Trace Flags (Transact-SQL) - Microsoft Learn. The list of documented trace flags; it has no entry for 2861. ↩ 3. Why DPA Uses SQL Server Trace Flag 2861 and Zero Cost Plans - Thomas LaRock, SolarWinds blog, December 7, 2016. The explanation of why DPA turns the flag on and when to turn it off. ↩ 4. Trace Flag 2861: The Trace Flag, The Myth, The Legend - Sean Gallardy. The claim that the flag existed only in SQL Server 2000 SP3 and SP4, with notes dated April 30 and July 4, 2021. ↩ ### Share this: * * Share on Bluesky (Opens in new window) Bluesky * Share on Mastodon (Opens in new window) Mastodon * Email a link to a friend (Opens in new window) Email * More * * Share on Threads (Opens in new window) Threads * Share on Reddit (Opens in new window) Reddit * * Share on WhatsApp (Opens in new window) WhatsApp * Print (Opens in new window) Print * ### _Related_