I stumbled across something really cool when I was reading through the BOL entry on sp_trace_setevent*, which gives a list of all events and columns available to trace. This is, by the way, an extremely useful entry to have bookmarked.
There are two events I hadn't really noticed before: number 39, Deprecated, and 125, Deprecation Announcement. "Deprecated" clearly tells you when a deprecated (that means phased out, kids) feature is used on the instance. And Deprecation Announcement "Occurs when you use a feature that will be removed from a future version of SQL Server."
That is SO insanely useful, I might throw a deprecation party. We'd all have to drink TAB and wear really old clothes, or something. (Get it? Because they've gone out of style? Ha! I made a funny!)
When you begin to look at upgrading SQL Server, this is a great way to start to track down code that will break after the upgrade. Set up a small server side trace, with just the Deprecated, Deprecation Announcement, and Existing Connections events, and let it run for a while – maybe a day, or a week, depending on how fast the trace file grows. Then start your lists of code to update.
* By the way, you use sp_trace_setevent when you set up a server side trace. Definitely see my SQLServerPedia article on server side trace, and the accompanying video on MidnightDBA.com.
-Jen McCown
Pingback: SQL Monitoring with SQL Profiler