Logging Stored Procedures - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Logging Stored Procedures

Author

Topic

Carat
Yak Posting Veteran

92 Posts

Posted - 2007-06-14 : 06:06:39

Hi

We have a database that has over a 1000 stored procedures. Most of them are used but not all of them. Because it would take to much time to investigate which are used or not, I want to log each execution of a stored procedure for a month. This way I can see which procedures are used and which are not.

Is there a way to do this???

I thought of using a DML trigger that fires after an 'exec' statement but it seems that it can be only triggered after a create, alter or drop statement.

Thanks!

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-14 : 07:55:43

Profiler is probably the easiest way but it will have a performance impact.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2007-06-14 : 09:31:34

I've come up against this before.

All our Sprocs "Phone Home" [insert a row in a Logging table] so I know which are used, how often, duration, etc.

I wonder whether it would be possible to rename ALL the SProcs, prefixed with, say, "xxx", and put a "wrapper" in place of each one; the "wrapper SProc" would rename the "original" back to itself, and execute it.

So for an SProc:


CREATE PROCEDURE MySproc
	@Param1 varchar(10),
	@Param3 varchar(10),
	@Param2 varchar(10)
AS
SELECT [1] = @Param1, [2] = @Param2, [3] = @Param3
GO

We would [mechanically generate code to] do:


EXEC sp_rename 'MySproc', 'xxx_MySproc'

CREATE PROCEDURE MySproc
	@Param1 varchar(10),
	@Param3 varchar(10),
	@Param2 varchar(10)
AS
EXEC sp_rename 'xxx_MySproc', 'MySproc'
EXEC MySproc @Param1, @Param3, @Param2
GO

and then after a week or so we see how many are left called "xxx_..."

Would that work?

Kristen

Carat
Yak Posting Veteran

92 Posts

Posted - 2007-06-19 : 04:14:26

That is an idea but I don't know whether that will work.

My first idea was to use Profiler and filter the textdata with '%usp%', which is the prefix that we use for our stored procedures.
But I thought it was impossible to let the trace running for about 30 days. Now I know that it is possible to script the trace and place it in a stored procedure so that you can start and stop the trace within a job. At the moment the trace starts every morning at 6 and ends at 22. After 30 days I will merge all the files into a table using the function 'fn_trace_gettable'.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources