Real world story of DBCC PAGE saving the day

Last week I answered a question on Twitter about DBCC PAGE and asked if I could be sent more details about the problem. The person was gracious enough to send me a detailed explanation, and it’s such an interesting scenario that I asked for and received permission to share this cut-down, anonymous version of the story with you.

Basically, it’s a real-world application of using DBCC PAGE to solve a nasty business problem that couldn’t be solved any other way.

The company involved produces government-regulated medical products, where tracking products and their ingredients is absolutely critical to make sure that any problems with materials leads to the affected products being withdrawn to prevent possible loss of life.

The company has an electronic document management application that stores all the tracking documents about all the ingredients, processes, and products. Earlier this year the application started to suffer performance problems, which turned out to be running out of threads in the connection pool. The vendor fix was to reboot. This cleared the problem, but then it started again. Rinse and repeat. After a few cycles of this, the performance problems started appearing more and more frequently after the reboot, leading to lots of complaints.

The DBA got involved and noticed a bunch of blocked sessions, each blocked by unresolved DTC transactions, holding locks on table records. As more records became locked on each reboot, blocking got worse and worse.

The solution? Rollback the DTC transactions.

The problem? These DTC transactions in some cases were days old, and some documents tied to those transactions had already been destroyed. Rolling back the DTC transactions would have meant questionable integrity of large amounts of product inventory, thus endangering patients. It might have been possible to check through everything by hand, but that would have been a prohibitively expensive effort.

DBCC PAGE to the rescue. Using the DMVs, the DBA figured out all the X-locked records and dumped all the associated pages (about 50) with DBCC PAGE dump style 3. By working with the application vendor and the application admin, the information in the records allowed the exact documents involved to be determined, and further querying of the application database confirmed that the documents had indeed been successfully imported. This meant all the hung transactions could be safely terminated.

The ability to look into the pages and figure out which documents were involved saved the company from having to write off a ton of inventory.

Bottom line: undocumented commands are *NOT* just for the intellectual interest of poking about in the internals or for dealing with corruption.