Identifying Deprecated Feature Usage (Part 2)

In my previous post, I took a stab at monitoring deprecation events for SQL Server. It didn't go so well. A deprecation event occurred more than 5,000 times in a very short period of time, and I got one email for every occurrence. Not good. Here's what I kept seeing over and over:

USER_ID will be removed from a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use the feature. Use DATABASE_PRINCIPAL_ID instead.

It turns out the system stored proc msdb.dbo.sp_send_dbmail has a USER_ID() reference. I suspect an unrelated alert/email happened once, which executed sp_send_dbmail, which generated a DEPRECATION_FINAL_SUPPORT event, which ultimately led to another execution of sp_send_dbmail, which generated yet another DEPRECATION_FINAL_SUPPORT event, and round and round we go.

After figuring out the mess I'd gotten myself into, I tweaked my activation procedure to *not* send email if the EVENTDATA() included DatabaseName = 'msdb'. It was a simple fix. I recreated my QUEUE, SERVICE, EVENT NOTIFICATION, and activation PROCEDURE and waited. It wasn't long before a few dozen emails rolled in. I quickly dropped all of the objects to stop the flow of email. After my first experience, I didn't want my Inbox bloated with database mail spam. Here are three more examples of what I found:

It's odd that for all three, the EventType is DEPRECATION_FINAL_SUPPORT (the feature will be removed from the *next* major version of SQL Server), yet the TextData field says the offending feature "will be removed in a *future* version of SQL Server", indicating a DEPRECATION_ANNOUNCEMENT event. Perhaps that's a bug? I ran my tests on SQL Server 2014 with SP2. I'm quite certain none of those features is gone for SQL Server 2016.

When I started out, I had envisioned seeing mostly (if not all) DEPRECATION_ANNOUNCEMENT events and a small percentage of DEPRECATION_FINAL_SUPPORT events. Along with other testing strategies, this could be used to help determine if software is compatible with the "next version up" of SQL Server. Any occurrences of DEPRECATION_FINAL_SUPPORT events would tell me parts of the software would need to be rewritten to eradicate use of what's been deprecated. But since I haven't encountered *any* DEPRECATION_ANNOUNCEMENT events yet, I have reconsidered and no longer deem this a sound strategy.

But I still think there's some value here. I'm going to log these events for a while and take my findings back to the dev team. Knowing the realities of the SDLC, no fixes will be made until absolutely necessary. Nonetheless, the dev team should appreciate knowing what they're up against ahead of time.

I will be changing my monitoring strategy. With some hindsight, I now see that sending an email once per event is overkill. In fact, I don't want any email at all. The software is in production. It works. I can simply log to a table and query the info back at any time. Near-synchronous alerts simply don't make sense here. My CREATE EVENT NOTIFICATION statement was also ON SERVER. That is more than is needed. I should have narrowed it down to one or more specific user databases and left the system DBs out of it.

I'll have one more post after this with some T-SQL scripts for creating all the objects. More to come...