How to Recover Parameters for Stored Procedure from System Logs in SQL Server

sql serverssmst-sql

I'm trying to debug an app, and I'd like to rule out database errors before tackling the code.

I know which stored procedure is responsible to handling the data I'm looking at, but there is no logging nor any traces set up on this DB. Is there a way to recover the parameters passed to that procedure, using existing system logs?

I've looked at:
dm_exec_procedure_stats and fn_dblog but they don't seem to have what I need.

I'm setting up logging moving forward.

Best Answer

There is no built-in logging mechanism that runs by default out-of-the-box to capture stored procedure parameter values.

Depending on the version of SQL Server you're using, the best way to capture that detail is via Extended Events (actual execution plans and looking at parameter runtime values. Pretty expensive though, and a bear to parse out via XML).

I suppose "best" is very subjective. I don't think there is really a great, low-impact, way to get the actual parameter values for every execution of a stored proc through some SQL Server built-in mechanism. It's probably easier to log that through the client, or via logging built into the stored proc.

Related Solutions

Sql-server – Stored procedure parameters using tsql functions

SQL Rockstar's answer is how to fix it: I'll add an explanation.

A stored procedure parameter can be either a constant or variable: not an expression. CAST is an expression. (This is different to udf parameters that can accept expressions)

EXEC on MSDN states

... @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] }

Sql-server – Is it possible to record incoming parameter values in a procedure call while tracing in SQL Server Profiler

I'll bite the bullet and tell you that such a trace cannot be set up, because it is not the [perceived] purpose of traces. I have always done it this way:

WHILE (@@FETCH_STATUS = 0) AND
( @About80MoreBooleanExpressionsHere)
BEGIN /* 1 */
-- about 700 lines of logic, math and if-parameter-this-then-that
-- stuff omitted
INSERT InnerProcedureCallWithinLoop__TraceTable
VALUES (@from_locn, @About53PARAMSOMITTED
EXEC @ConsiderItem =
InnerProcedureCallWithinLoop
@from_locn,
@About53PARAMSOMITTED,
...

If I know that it is only ever called from one location. Otherwise, I do it in the callee instead of the caller.

ALTER PROC InnerProcedureCallWithinLoop
    @from_locn int,
    @About53PARAMSOMITTED ...
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;
INSERT InnerProcedureCallWithinLoop__TraceTable VALUES (@from_locn, @prm2, @prm3....
--- rest of proc

This is obviously different from using a trace, which is able to capture events even if they started and never finished (faulty parameters, rolled back transactions). If that is your problem, you need to look at CLR or email methods to externalise the captured output.

Best Answer

Related Solutions

Sql-server – Stored procedure parameters using tsql functions

Sql-server – Is it possible to record incoming parameter values in a procedure call while tracing in SQL Server Profiler

Related Question