Error Message Text Is Not an Alerting Contract
An alert that depends on the exact words in an error message can look fine in a test plan and miss the next version. I don’t mean a vague parser that searches for “backup failed” in a log file. I mean a rule that treats the sentence as the contract.
This post is part of the Undocumented Changes in SQL Server 2022 and 2025 series. The contents page lists every part in order.
For alert rules, I would start with the message ID rather than the text. I noticed this while writing the SQL Server 2022 message post, the SQL Server 2025 removals post, and the FIDO surface post. The inventory showed many message changes, so text-based alerting needs review.
sys.messages stores one row per message_id and language_id. For each message it includes the severity, whether SQL Server writes it to the event log, and the message text.[1] You can define SQL Server Agent alerts by message ID or by severity. The same sp_add_alert page also has @event_description_keyword, which filters on characters in the error description.[2] That keyword filter is useful for an object name. I would still keep the event identity on message ID or severity because the keyword text can change during an upgrade.
What changed between versions
For this post, I recomputed the numbers from the three sys.messages inventory exports. I compared English messages only (language_id = 1033). I compared message ID, severity and text case-sensitively first. Then I compared the same rows with text lowercased and trailing spaces removed.
From SQL Server 2019 CU32 to SQL Server 2022 CU25, the exact, case-sensitive comparison found 189 changed message IDs. After lowercasing and trimming trailing spaces, 181 changed message IDs remained. The other 8 changed only by case or trailing spaces. Of the 189 exact changes, 68 changed their placeholder list.
From SQL Server 2022 CU25 to SQL Server 2025 CU9, the exact, case-sensitive comparison found 298 changed message IDs. After lowercasing and trimming trailing spaces, 279 changed message IDs remained. The other 19 changed only by case or trailing spaces. Of the 298 exact changes, 43 changed their placeholder list. Five message IDs changed severity. Another 278 message IDs were present in SQL Server 2022 and absent from SQL Server 2025 CU9.
A placeholder list is the ordered list of tokens such as %d, %ls and %.*ls in the message template. If a parser expects a fixed sentence after SQL Server fills those placeholders, a new token can move the words it was matching.
The FIDO wording shows the pattern clearly. In the 22300 range, SQL Server 2019 had messages such as “DW FIDO mode is not enabled.” and “Fido thread failed to acquire a lock.” In SQL Server 2022, the same IDs used more general wording such as “The feature is not supported in this version of SQL Server.” and “Thread failed to acquire a lock.” I found the same pattern in the public error-message include files for SQL Server 2019 and 2022.[3]
Examples that could matter to alerting
These are not the only changed messages. They are examples I would expect a database administrator (DBA) to care about if an alert, log shipper, or ticket rule matched exact text.
| Version change | Message ID | Severity | Old text (excerpt) | New text (excerpt) |
|---|---|---|---|---|
| 2019 to 2022 | 5040 | 16 | MODIFY FILE failed. Size is greater than MAXSIZE. |
MODIFY FILE failed for database '%.*ls', file id %d. Size of file (%I64d KB) is greater than MAXSIZE (%I64d KB). |
| 2019 to 2022 | 9002 | 17 | The transaction log for database '%ls' is full due to '%ls'. |
The transaction log for database '%ls' is full due to '%ls' and the holdup lsn is %S_LSN. |
| 2019 to 2022 | 35294 | 16 | Log backup for database "%.*ls" on a secondary replica failed because a synchronization point could not be established on the primary database. |
%ls backup for database "%.*ls" on a secondary replica failed because a synchronization point could not be established on the primary database. |
| 2022 to 2025 | 5125 | 24 | File '%ls' appears to have been truncated by the operating system. Expected size is %I64d KB but actual size is %I64d KB. |
File '%ls', File ID '%d' was found to be shorter than expected during %ls. Expected size is %I64d KB but actual size is %I64d KB. Examine the disk system for potential failures. |
| 2022 to 2025 | 19419 | 16 | Windows Server Failover Cluster did not receive a process event signal from SQL Server hosting availability group '%.*ls' within the lease timeout period. |
Windows Server Failover Cluster did not receive a process event signal from SQL Server hosting availability group '%.*ls' within the lease timeout period. For more information, see https://go.microsoft.com/fwlink/?linkid=2322141. |
| 2022 to 2025 | 15063 | 16 | The login already has an account under a different user name. |
The login already has an account with the user name '%ls'. |
| 2022 to 2025 | 3417 | 21 | Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online. |
Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information, see https://go.microsoft.com/fwlink/?linkid=2322220. |
Some changes add useful detail. Message 5040 is better in 2022 because it names the database and file. Message 5125 is better in 2025 because it includes file ID and the operation. The edit itself is not the concern. The concern is relying on the current wording after an upgrade.
Severity changed less often, but it also changed. Microsoft defines severity 11 through 16 as errors that the user can correct. Severity 17 through 19 means software errors that the user cannot correct. Severity 20 through 24 means system problems and fatal errors.[4] The 2019 to 2022 comparison had one severity change. The 2022 to 2025 comparison had five, so I include severity changes in the same review list.
Export the messages before you upgrade
Here is the read-only query I used as the base export. Run it on the old instance and the new instance, and save each result as a tab-separated file.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SET NOCOUNT ON; SELECT [message_id] , [severity] , [is_event_logged] , [text] FROM [sys].[messages] WHERE [language_id] = 1033 ORDER BY [message_id]; |
Microsoft’s Database Engine events and errors page gives a similar query and says the descriptions come from the sys.messages catalog view.[5] I include is_event_logged because SQL Server Agent alerts depend on messages that are written to the Windows application log, unless another logged event path is involved.[6]
If you prefer command-line exports, escape the message text before you write a tab-separated file, and don’t trim trailing spaces. Replace the server names and file names with your own.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
function Export-SqlMessages { param([string] $Instance, [string] $Path) $conn = New-Object System.Data.SqlClient.SqlConnection "Data Source=$Instance;Integrated Security=true;Application Name=message-export" $conn.Open() try { $cmd = $conn.CreateCommand() $cmd.CommandText = 'SELECT message_id, severity, is_event_logged, [text] FROM sys.messages WHERE language_id = 1033 ORDER BY message_id;' $table = New-Object System.Data.DataTable $table.Load($cmd.ExecuteReader()) $table | Select-Object message_id, severity, is_event_logged, text | Export-Csv -Path $Path -NoTypeInformation -Encoding utf8 } finally { $conn.Close() } } Export-SqlMessages -Instance 'old-instance' -Path '.\messages-old.csv' Export-SqlMessages -Instance 'new-instance' -Path '.\messages-new.csv' |
Then compare the two exports. This PowerShell snippet reports removed, added, severity changes, exact text changes, normalized text changes, and placeholder-list changes.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
$old = Import-Csv .\messages-old.csv $new = Import-Csv .\messages-new.csv $oldById = @{} foreach ($row in $old) { $oldById[[int]$row.message_id] = $row } $newById = @{} foreach ($row in $new) { $newById[[int]$row.message_id] = $row } $placeholderPattern = '%(?!%)(?:\d+!|[-+#0 ]*)?(?:\*\.?|\.\*)?(?:\d+)?(?:I64|l|h)?[a-zA-Z]!?' foreach ($id in ($oldById.Keys + $newById.Keys | Sort-Object -Unique)) { $oldRow = $oldById[$id] $newRow = $newById[$id] if ($null -eq $oldRow) { [pscustomobject]@{ Change = 'Added'; MessageId = $id; OldSeverity = $null; NewSeverity = $newRow.severity; Detail = $newRow.text } continue } if ($null -eq $newRow) { [pscustomobject]@{ Change = 'Removed'; MessageId = $id; OldSeverity = $oldRow.severity; NewSeverity = $null; Detail = $oldRow.text } continue } $oldPlaceholders = ([regex]::Matches($oldRow.text, $placeholderPattern) | ForEach-Object Value) -join ',' $newPlaceholders = ([regex]::Matches($newRow.text, $placeholderPattern) | ForEach-Object Value) -join ',' if ($oldRow.severity -ne $newRow.severity) { [pscustomobject]@{ Change = 'Severity'; MessageId = $id; OldSeverity = $oldRow.severity; NewSeverity = $newRow.severity; Detail = $newRow.text } } if ($oldRow.text -cne $newRow.text) { $kind = if ($oldRow.text.ToLowerInvariant().TrimEnd() -eq $newRow.text.ToLowerInvariant().TrimEnd()) { 'CaseOrTrailingSpaceOnly' } else { 'Text' } [pscustomobject]@{ Change = $kind; MessageId = $id; OldSeverity = $oldRow.severity; NewSeverity = $newRow.severity; Detail = $newRow.text } } if ($oldPlaceholders -ne $newPlaceholders) { [pscustomobject]@{ Change = 'Placeholders'; MessageId = $id; OldSeverity = $oldRow.severity; NewSeverity = $newRow.severity; Detail = "$oldPlaceholders -> $newPlaceholders" } } } |
That script only gives you changes to review. I would join the output back to whatever defines your alerts, jobs, event sessions, log parser rules, and ticket routing rules.
How I would review alert rules
For SQL Server Agent alerts, I would start with the rules that use @message_id or @severity, then inspect any rule that also uses @event_description_keyword. Microsoft documents that @message_id defines an alert by message error number, and @severity defines an alert by severity. It documents @event_description_keyword as a character sequence that must appear in the error description.[7]
I would review the rules in four groups:
- Keep the identity of the event on
message_idwhere the message is specific enough. - Use severity alerts to catch a wider range of events, especially severity 19 and higher.
- Use text keywords as narrowing filters, not as the primary identity.
- After an upgrade, diff
sys.messagesand review any alert whose message ID changed, disappeared, changed severity, or changed placeholders.
I am not making claims about third-party monitoring tools here. I did not test them for this post. Test the rule definition in your own system. Do not rely on the vendor name as evidence.
If a message rule surprised you after an upgrade, send me the example on Bluesky or LinkedIn, and I’ll add it to the notes.
References
- sys.messages (Transact-SQL) - Microsoft Learn. Defines
message_id,severity,is_event_logged, andtextin the message catalog. ↩ - sp_add_alert (Transact-SQL) - Microsoft Learn. Documents SQL Server Agent alert arguments for
@message_id,@severity, and@event_description_keyword, and lists which logged errors can raise alerts. ↩ - SQL Server 2019 and SQL Server 2022 Database Engine events and errors 22000 to 22999 - MicrosoftDocs/sql-docs on GitHub. Shows the 22300 range wording change from FIDO-specific text to more general text. ↩
- Database Engine Error Severities - Microsoft Learn. Defines the severity ranges used to interpret changed messages. ↩
- Database Engine events and errors - Microsoft Learn. States that error descriptions come from
sys.messagesand gives a query against the catalog view. ↩ - sp_add_alert (Transact-SQL) - Microsoft Learn. Lists the logged SQL Server errors and events that can raise SQL Server Agent alerts. ↩
- sp_add_alert (Transact-SQL) - Microsoft Learn. Documents
@message_id,@severity, and@event_description_keywordfor reviewing alert definitions. ↩