Debugging Occasionally Error with SQL Trace

By : Kasim Wirama, MCDBA

You have created a stored procedure and you tested your stored procedure works fine in testing environment. After deployment, your stored procedure sometimes doesn’t work as expected, let’s say the stored procedure sometimes doesn’t insert new record and you need to know the data why it doesn’t work occasionally. To reproduce the problem it is difficult because it intermittent behavior. The way to debugging the stored procedure is by using Profiler with Stored Procedures:RPC:Starting, TSQL:BatchStarting, and UserConfigurable event (let’s say use UserConfigurable:0). UserConfigurable event is triggered under sp_trace_generateevent system stored procedure, which should have appropriate  ALTER TRACE permission.

It is not good practice to assign user’s login with ALTER TRACE permission regarding to sp_trace_generateevent. The trick to bridge server level permission and database level permission is to create wrapper stored procedure for sp_trace_generateevent and sign the wrapper stored procedure with certificate. Besides that you need to create a login based on certificate in master database and backup-restore the certificate together its private key to target database.

First, create certificate in master database. In this example I create certificate with name mycert, subject=’testing certificate’ with start date and expiry date between 1 Jan 2008 and 31 Dec 2008.

CREATE CERTIFICATE mycert ENCRYPTION BY PASSWORD=’123’ WITH SUBJECT=’testing certificate’, START_DATE=’20080101’, EXPIRY_DATE=’20081231’;

Then you create login based on the certificate and grant ALTER TRACE and AUTHENTICATE SERVER permission to it.

CREATE LOGIN mylogin FROM CERTIFICATE mycert;
GO
GRANT ALTER TRACE TO mylogin
GO
GRANT AUTHENTICATE SERVER TO mylogin
GO

Backup your certificate together with its private key and restore to target database (in my sample here I use AdventureWorks database) where your problematic stored procedure resides.

BACKUP CERTIFICATE mycert TO FILE=’C:\mycert.cer’ WITH PRIVATE KEY (FILE=’C:\mycert.pvt’, ENCRYPTION BY PASSWORD=’123’, DECRYPTION BY PASSWORD=’123’);

USE AdventureWorks;
GO

RESTORE CERTIFICATE mycert FROM FILE=’C:\mycert.cer’ WITH PRIVATE KEY (FILE=’C:\mycert.pvt’, ENCRYPTION BY PASSWORD=’123’, DECRYPTION BY PASSWORD=’123’);

Next you create stored procedure wrapper for sp_trace_generateevent system stored procedure and sign it with the just-restored certificate.

CREATE PROCEDURE trace_debug
(
@eventid INT,
@userinfo NVARCHAR(128),
@userdata VARBINARY(8000)
) AS
EXECUTE sp_trace_generateevent @eventid = @eventid, @userinfo = @userinfo, @userdata = @userdata;
GO

ADD SIGNATURE TO trace_debug BY CERTIFICATE mycert WITH PASSWORD=’123’;

Give execute privilege on trace_debug stored procedure to public database role.

GRANT EXECUTE ON trace_debug TO [public];

You alter the problematic stored procedure by adding trace_debug execution after the line of problem suspect, for example after insert statement with the following code :

IF @@ROWCOUNT = 0
                EXEC trace_debug 82, ‘insert failed’,0x0000;

When trace_debug is executed, it will trigger UserConfigurable:0 event (under User configurable event category) because I assign value 82. If you assign value 83, it will trigger UserConfigurable:1 event and so forth.

You monitor the problematic stored procedure by installing the following SQL trace events : StoredProcedures:RPC starting, TSQL:Batch Starting and User Configurable:UserConfigurable:0.