Looking at SQL Server Execution logs ideally verbose for debugging

Question

Hi,

I was wondering if there is any way to check the execution logs of SQL server. I am not talking about the proprietary transactions logs or the error logs. But just the recently executed logs in a verbose fashion for debugging.

For example, I have a web application that calls stored procedures, passes in parameters and populates gridviews etc. In the SQL management studio, I want to see (either through a log file or by running a T-SQL query) what stored procedures or sql statements were executed, what the value of the passed in parameters were and what the returned values were etc.

Thanks in advance for your help.

Answer 1

Typically this is achieved using the Profiler application:

Monitoring Transact-SQL activity per user.

You can create a trace that captures events relating to the Sessions, ExistingConnection, and TSQL event classes. Include all data columns in the trace, do not specify any event criteria, and group the captured events by DBUserName.

Monitoring for the SQL:BatchCompleted and SP:Completed events gives exactly what the user has executed. Adding SP:StmtCompleted gives a trace of execution statement by statement, inside the procedures invoked, but is very expensive and will slow down the server. You can get the values of parameters passed in, but you won't be able to get the values and rowsets returned to the client.

Monitoring using SQL Profiler is something to be done only on test environments, as the impact on a production machine is significant. Server side traces done with sp_trace_xxx procedures are better, but they still have a serious impact if frequently generated events are traced (like every statement).