Across the SQL Server environments we review, the same pattern keeps repeating. The incidents that hurt most are rarely the loud ones. A blocked query pages someone. A full disk pages someone. A failed backup job, if it is monitored at all, produces an email. Those problems cost money, but they get noticed within hours, because something makes noise.
The expensive problems are the ones that never raise an error. A feature stops doing its job, nothing is thrown at the application, no alert fires, and the first sign is a person asking why something has not happened for weeks. By then the cost is no longer the fix. It is the time spent working out when the failure began, what was lost in between, and what else was quietly depending on the thing that stopped.
This post looks at three of these quiet failures: a Service Broker queue that has switched itself off, sensitive columns that nothing is protecting, and an Analysis Services server whose processing has stopped while its memory runs past its limits. Each one is easy to miss for a structural reason, and each one is now covered by a new report in Database Health Monitor 4.1626. We close with the other headline items in the release.
Why quiet failures last so long
A monitoring setup is usually built from the failures people have already been burned by. Wait statistics, blocking, job failures, disk space and backups all earn a place because someone, at some point, got a bad phone call. That is sensible, but it leaves a blind spot in everything that fails by doing nothing.
Quiet failures share three traits:
- No error reaches the caller. The sending application gets a success, because the message was accepted or the query ran. The failure happens later, somewhere the caller never looks.
- The damage is cumulative. Every hour the problem persists adds more waiting work, more exposed data or more stale numbers, so the cost of finding out late grows with time.
- Nobody owns the check. The feature sits between teams. Developers built it, the database administrator hosts it, and the security or reporting team relies on it. Each assumes another group is watching.
Quiet failure one: Service Broker stops and says nothing
Service Broker is the message queuing feature built into the SQL Server engine. Many teams do not realize they use it, because several features sit on top of it: Database Mail, event notifications and query notifications. Some applications use it directly for asynchronous work, such as processing orders, sending notices or moving data between databases and instances.
The trouble is how it fails. When a queue is disabled or messages cannot be delivered, nothing raises an error to the sender. The messages simply wait. An application keeps sending, the sends keep succeeding, and the work never happens.
A queue that disables itself
The best known version of this is poison message handling. If reading a message from a queue is rolled back five times in a row, SQL Server concludes that the message is poison and turns the queue off, so that one bad message cannot loop forever. That protective behavior is reasonable. The cost is that the queue is now disabled, every message behind the bad one is stuck, and the only signal is a line in the error log that no one is reading. Someone can also disable a queue by hand with ALTER QUEUE and forget to turn it back on.
There is a trap in the repair, too. If you simply switch the queue back on without looking at the message that kept failing, poison message handling will disable it again. The sensible order is to find the failing message first and then restore the queue.
Messages that never leave
The second form is the transmission queue. Messages sent from a database wait there until they are delivered, and when they cannot be delivered the transmission status says why: no route, an unknown service, or a security or certificate problem. Messages with no error at all can also sit and wait, which is why the age of the oldest message is worth watching.
The Database Mail connection
Database Mail is where this usually bites. It runs on Service Broker inside msdb. If the mail queue is stopped, which happens when the sysmail_stop_sp procedure has been run, mail waits until someone runs sysmail_start_sp. Jobs complete, alerts fire, and the emails that depend on them never arrive.
Conversations that never end
The third form is slower. Every Service Broker exchange is a conversation, and each side is supposed to end it. When one side never does, the endpoints accumulate. They grow the database and tempdb, and there is no obvious date on them to tell you when the leak began. Over months this turns into a space problem that looks unrelated to messaging at all.
What it costs
The business cost is rarely the queue itself. It is the work that did not happen: orders not processed, notices not sent, a data feed that stopped moving. It is the hours spent reconstructing which messages were lost and which can be replayed. And when the stopped feature is Database Mail, it is the period in which failures went unreported because the reporting channel was the thing that had failed.
Quiet failure two: sensitive columns with nothing protecting them
The second failure is not an outage. Nothing breaks. A column holding personal or payment data sits in a table in plain form, readable by anyone with access to the table, and the application works perfectly. The failure only becomes visible during an audit, a security review or a breach, which is the most expensive moment to find out.
Most organizations know encryption at rest matters, and they have a view on transparent data encryption for the whole database. Far fewer can answer a narrower question: for the specific columns that hold sensitive data, what is actually protecting them? SQL Server offers several column-level and row-level protections, and each can be present, absent or quietly undermined.
The ways protection goes missing
- No protection at all. A column that looks sensitive, such as one named for an email address, a card number or a national identifier, has no encryption, no masking and no classification. Nobody decided it needed protection, so it has none.
- Masking that does not mask. Dynamic data masking shows users a masked value unless they hold the UNMASK permission. When that permission has been granted widely, whether on the database, a schema, a table or a column, the mask is decoration. Members of db_owner always see masked data in the clear, which is worth knowing when you count how many people hold that role.
- Row-level security that is switched off. A security policy can exist, with its predicates and its table bindings, and still be set to STATE = OFF. A disabled policy filters nothing and blocks nothing, but anyone reading the list of policies sees that it exists.
- Keys held in one place. With Always Encrypted, a column master key can be a certificate in a Windows certificate store. Every client needs a copy of that certificate, and losing it means losing the data.
Each of these passes a casual review. The policy is there. The masking function is defined. The certificate exists. It takes a systematic look to see that the control is off, too broadly bypassed or dependent on a single copy of a key.
What it costs
The cost is risk that accumulates silently, plus the cost of finding out at the worst time. An auditor who asks which sensitive columns are protected is asking a reasonable question, and a team that cannot answer it in an afternoon faces a longer and more expensive exercise. A team that discovers a disabled policy after a breach has a harder conversation still. Catching these gaps in an ordinary review turns a potential incident into a ticket.
Quiet failure three: Analysis Services drifts out of sight
Most SQL Server shops that run Integration Services and Reporting Services also run SQL Server Analysis Services. It feeds the dashboards and cubes that management reads. It is also a separate server with its own engine, and processing failures, memory limit breaches and long running queries do not show up in anything SQL Server itself reports.
That separation is what makes it quiet. The relational engine looks healthy. The jobs that process the model may report success or may not be watched at all. Meanwhile the numbers in the reports gradually go stale, because a table was never processed or stopped being processed, and the people reading the dashboards have no way to tell. A stale report that looks current is worse than an obviously broken one, because decisions get made on it.
Memory is the second half
Analysis Services manages memory against limits: a Low limit, a Total limit and a Hard limit. Below the Low limit the server is comfortable. Above it, the server starts to be under pressure. Above the Total limit it is in trouble, and beyond the Hard limit it is worse again. A server that has been creeping upward for months can sit in the red for a long time before a user reports that queries are slow or that a processing run failed.
What it costs
The cost shows up as decisions made on old data, as reporting teams spending days chasing a number that is wrong, and as the time it takes to work out which table stopped processing and when. Because the failure is out of sight of the usual SQL Server tooling, it is often first noticed by a business user, which is exactly the wrong place for it to be noticed.
How Database Health Monitor 4.1626 covers each one
Database Health Monitor 4.1626 adds three new reports for these areas, with two instance level roll-ups for the database reports. All of them only read. They do not change, process or repair anything, and the fix scripts they offer are shown for you to review and are never run for you.
Service Broker
The Service Broker report covers a single database: whether the broker is enabled, every queue with its depth and activation settings, the transmission queue grouped by target and status, and the conversation endpoints counted by state and age. Its findings are ranked by severity.
| Finding | Severity | What it means |
|---|---|---|
| Queue disabled | Critical | RECEIVE or SEND is off, by poison message handling or by hand. |
| Messages cannot be delivered | Critical | The transmission queue holds messages whose status reports an error. |
| Messages waiting to be sent | Warning | Messages with no error have waited more than 5 minutes. |
| Database Mail is stopped | Warning | The mail queue in msdb is off because sysmail_stop_sp ran. |
| Queue backlog | Warning | 10,000 or more messages sit in one queue. |
| Leaked conversations | Warning | More than 10,000 open conversations began more than a day ago. |
The report also flags a broker that is disabled in a database with user queues, which is common on a restored or attached copy, an activation procedure that is missing, and a queue where activation was notified but no reader is running, which usually means the procedure is failing. It shows fix scripts such as the ALTER QUEUE statement, along with the query to look at the message that kept failing, and an END CONVERSATION template that carries a warning: CLEANUP removes a conversation and its messages without telling the other side.
Service Broker by Database rolls the same checks up across the instance. It lists every database that has the broker enabled or has something in it, with columns for disabled queues, queued messages, transmission queue rows, the age of the oldest unsent message and open conversations. Each database is read with the same query and rules as the database page, so the counts agree, and the read shows progress and can be cancelled.
Data Protection
The Data Protection report answers the narrower question described above. It reads Always Encrypted columns and keys, masked columns, row-level security policies, sensitivity classifications and, on SQL Server 2022, ledger tables. It decides which columns count as sensitive from three signals: the column is classified, it is already encrypted or masked, or its name matches a Sensitive Data rule. Each sensitive column is counted once, under its strongest protection.
| Protection | Meaning |
|---|---|
| Encrypted | Always Encrypted. The server never sees the plain value. |
| Masked | Dynamic data masking. Users without UNMASK see a masked value. |
| Classified only | Labeled with a classification, but not encrypted or masked. |
| Unprotected | Looks sensitive and is neither encrypted, masked nor classified. |
The findings map directly onto the failures above: a table with sensitive columns that have no protection, a disabled row-level security policy, a masked column readable because users hold UNMASK, and a column master key held only in a Windows certificate store. It offers scripts to mask a column, enable a policy or revoke an UNMASK grant, and again shows them without running them.
Analysis Services
The Analysis Services report connects to the SSAS instance beside a SQL Server and reads its mode (Tabular or Multidimensional), version and properties, along with memory against the Low, Total and Hard limits. It shows sessions and running commands, longest first, with a command running for more than a minute marked in red. It also shows memory by object and, most relevant to the stale data problem, processing freshness: when each table or cube was last processed, with failed, never processed and stale items sorted to the top. DirectQuery partitions hold no data, so they are never called stale.
Supporting changes: the Index Consolidation Plan and a web dashboard
Index Consolidation Plan
The other index reports each list one kind of finding: missing index suggestions, duplicates and unused indexes. Acting on each list separately is how a table ends up with nine overlapping indexes. The Index Consolidation Plan weighs a database’s existing indexes and its missing index suggestions together, table by table, to find the smallest set of changes.
| Action | What it means |
|---|---|
| CREATE | A suggestion that nothing serves becomes a new index. |
| WIDEN | An existing index takes the extra included columns instead of a new index being created. |
| MERGE | Two indexes where one’s keys lead the other’s are folded into the wider one. |
| DROP | An exact duplicate, or an index with no seek, scan or lookup since SQL Server started. |
| KEEP | An index that already covers one or more suggestions. |
The guardrails are what make it usable. Anything that backs a primary key, a unique constraint or the only index supporting a foreign key is blocked, as is an index named in an index hint or used by a forced Query Store plan. The plan writes one ordered script with a matching rollback, and the script stops before changing anything if an index changed after the plan was read. The page itself changes nothing.
An opt-in, read-only web dashboard
The monitoring service can now serve a read-only web dashboard, so health information is in front of people without opening the application. It shows an estate overview with the worst servers first, a page for each server with 24 hour CPU, page life expectancy and batch requests charts, and pages for alerts, deadlocks and blocking. The same data is available as JSON.
The security posture is the important part, and it is conservative. The dashboard is off by default. When it is enabled, it listens on localhost only until an administrator changes that on the new Web Dashboard tab of the tray application. Access uses Windows authentication with separate Viewer and Admin groups, and only GET and HEAD requests are accepted.
The rest of the release
The release also includes 278 bug fixes, among them 9 crashes and 30 fixes in Azure SQL Health Monitor. Most of the rest correct wrong or misleading results, reports that stayed blank or claimed success after a query timeout, leaked connections, and grids that did not fit the window at 1280 by 900. Several were aimed at overall performance and at reducing CPU on the DBHealthHistory database, and the procedures that collect table growth, chargeback usage, file size, index usage and deadlock history were tuned to run faster.
Where to start
If you want to find out whether any of these three failures exists in your environment today, the order we would suggest is simple. Start with Service Broker by Database, because a disabled queue or a stopped Database Mail queue is the quickest to fix and the quickest to hurt. Then run Data Protection by Database and look at the unprotected counts. If you run Analysis Services, open its report and look at processing freshness and the memory bar.
The common thread is that none of these problems announces itself. They are found by asking, and the cheapest time to ask is before someone else asks for you. If you would rather have an experienced team review the results with you, prioritize what turns up and help with the remediation, that is the work we do at Stedman Solutions, and we are happy to talk it through.
Try Database Health Monitor 4.1626
The Index Consolidation Plan, the Service Broker, Data Protection and Analysis Services reports, and the new web dashboard are all in this release. They only read from your servers and never change anything on their own, so you can point them at a real instance and see what they find.
Download Database Health Monitor and run it against your own server. There is nothing to configure first.