Database Administration with a Side of Irony
Many of you know I am in the process of writing a book on SQL Server Dynamic Management Views for Performance Tuning along with Louis Davidson. Those of you who know me personally also know that once I get something under my mind’s fingernail I dig at it until I’m bloody, in my mind’s eye I rub at it until I’m bloodshot, wedged up my mind’s nose I pick at it until I’m brain damaged, fixated on something I research it to death until I find a solution, whether it’s important or not. So this is the case with the worthless DMV, sys.dm_tran_active_transactions. I planned on using this DMV in my chapter on Transaction DMVs to highlight how to isolate long running transactions impacting tempdb. The scope of the DMV is the SQL instance – great. All the other relevant DMVs in the transaction sphere are either session, database, or current transaction in scope. However, there is no proper foreign key to join to another DMV or system view with the same instance-wide scope. The information offered by the DMV is not detailed enough to really tell you anything other than “yep, there are transactions in your instance, they started at this time, and these are their statuses.” No sql handle to return their text, no database id or spid to join to a relevant DMV or system view. Worthless.
I told you that story to tell you this one.
As part of my digging to see what I may be missing <SARCASM>because Microsoft’s SQL Server Development Team would not dare put something so completely worthless into their product</SARCASM> I Googled (sorry Microsoft, but you owe me for my lost productivity on this one Ray) and ran across a possible hit on mydatabasesupport.com. I was jaded going in, but this made my day.
One suggestion, if you’re going to run a website with the word database in the URL I strongly suggest you make sure you keep your DBAs happy or else something like this may just happen. And you will be ridiculed by at least one smartass.
Any of you who know me know, I am that Smartass.