Deprecated Events Trigger

There is always a lot of uncertainty when migrating a SQL Server instance to a newer version; one of the uncertainties can be the drivers used by the various applications connecting to a given instance and the way the SQL Server instance was configured; usually however, this is not as big an issue as it used to be. Nowadays the problem is more often in the compiled T-SQL that is in the various databases or even worse, the and this always used to be the biggest guess of them all, the T-SQL that is executed against the server itself. We all know the remarks; “works on my machine” when something does not work in production. Luckily there is a way to validate the T-SQL compiled in the applications and allows us to make sure that all is compatible with the new version of SQL Server you are planning to roll-out.

What does this script do

Microsoft has a solution for us, it’s called the deprecated event trigger, this trigger is fired every time some DDL or DML is executed on an instance and it can record the warning messages in an XML file on the server. There are several ways you could go about it, you make it a policy to have:

  1. a. the trigger run on your pre-production systems and only “certify” if for product if you are satisfied with the output after testing has completed, or
  2. b. have it run on all development servers and send the output to the program managers who then give it to the development teams for corrections, or
  3. c. run it in production, or in a copy of production using a replay trace file, as you would never really know if the users execute the same code as they did in their tests and if the code in pre-prod is actually a “mirror” of the production environment (service packs, code, drivers, settings etc..).

In an ideal world, you should ask yourself the question, what if I would have to pay for the damages if things go wrong. I guess, as a DBA (Default Blame Acceptor) you have a pretty good feel of your company’s culture and policies and perhaps you go for all 3 options, I know I would.

  • Parameters
  • The Script
Parameters in the script

The Script does not use parameters, unfortunately it's all hard coded.
To download the script click here.

Important to know

You can later the events SQL Server should respond to, valid events for your server can be found by queering the sys.dm_xe_objects view. Have a look at books online for information regarding interaction with the Event session object.

The amount of data that is collect depends a lot on the quality of the code executed on and against a server instance. However, I would not be surprised if you get a few GB of problematic code references in your log file. Make sure you monitor the disk space carefully, the server is going to write to this directly.

For this code to work, you need to be connected locally on the server. You can't connect from your workstation and execute the code, you will have to RDP to the server and execute it there.