InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 06:33:52 GMT20http://www.sqlservercentral.com/Forums/Topic1232291-149-1.aspxIn case this helps anyone, here is how I do in-SPROC logging. In addition to the normal stuff, I also log the input parameter values that are sent to the SPROC./* ========================================================================================== *//* Example: How to log SPROC execution *//* ========================================================================================== */CREATE TABLE DBActivityLog( DBActivityID int IDENTITY NOT NULL CONSTRAINT PK_DBActivityLog_On_DBActivityID PRIMARY KEY CLUSTERED, DBASPROC varchar(300) NULL, DBAMessage varchar(1000) NULL, DBAAppName varchar(150) NULL DEFAULT APP_NAME(), DBAHost varchar(50) NULL DEFAULT HOST_NAME(), DBAUser varchar(50) NULL DEFAULT USER, DBADate datetime NULL DEFAULT GETDATE())ON MyDatabase_dataGO/* ========================================================================================== */CREATE TABLE AppErrorLog( EID int IDENTITY NOT NULL CONSTRAINT PK_AppErrorLog_On_EID PRIMARY KEY CLUSTERED, EDate datetime NOT NULL, EUser varchar(50) NULL, EMessage varchar(1000) NULL, ESource varchar(150) NOT NULL, ENo int NOT NULL DEFAULT 0, ESeverity int NOT NULL DEFAULT 0, ELineNo int NOT NULL DEFAULT 0, EHost varchar(50) NULL)ON MyDatabase_dataGO/* ========================================================================================== *//* ========================================================================================== */-- Example Stored Procedure using both tables created aboveCREATE PROCEDURE dbo.xp_usp_Read_Account_Rec@acctid int,@spstat int OUTPUT,@errmsg varchar(200) OUTPUT,@recn int OUTPUTASDECLARE @numrecs intDECLARE @pvalue varchar(1000) --log db activitySET NOCOUNT ONSET @spstat = 1 -- go ahead and set to okSET @errmsg = '' -- go ahead and set to okSET @recn = 0 -- go ahead and set to okBEGIN TRY --log db activity SET @pvalue = CONVERT(varchar(100),@acctid) INSERT INTO DBActivityLog VALUES ('dbo.xp_usp_Read_Account_Rec',CONVERT(varchar(1000),'SPROC call - Params= ' + @pvalue),APP_NAME(),HOST_NAME(),USER,GETDATE()) --Select the desired information SELECT AcctID,CandidateID,CandidateStatusID,AcctOpen,AcctStatusID,AcctComment,CurrRec,LCHost,LCUser,LCDate FROM Account WHERE AcctID=@acctid SET @numrecs = @@rowcount if @numrecs=0 BEGIN SET @spstat = -1 SET @errmsg = 'No record selected' SET @recn = 0 END RETURN @spstatEND TRYBEGIN CATCH DECLARE @ErrorNo int, @Severity int, @State int, @LineNo int, @errmessage varchar(1000) SELECT @ErrorNo = ERROR_NUMBER(), @Severity = ERROR_SEVERITY(), @State = ERROR_STATE(), @LineNo = ERROR_LINE(), @errmessage = ERROR_MESSAGE() SET @errmsg = CONVERT(varchar(200), @errmessage) SET @spstat = 0 INSERT INTO AppErrorLog VALUES (GETDATE(), USER, @errmessage, 'dbo.xp_usp_Read_Account_Rec', @ErrorNo, @Severity, @LineNo, HOST_NAME())END CATCHGOWed, 11 Jan 2012 12:51:18 GMTvikingDBAhttp://www.sqlservercentral.com/Forums/Topic1232291-149-1.aspxYep. Glad you worked it out. Happy to be able to help.Tue, 10 Jan 2012 07:11:27 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1232291-149-1.aspxThanks G^2But I've managed to work it out with BOL on sp_trace_setevent I needed to track events SP:Starting and SP:Completed for calls by the application, and SQL:BatchStarting and SQL:BatchCompleted for calls from SSMS.Tue, 10 Jan 2012 04:31:17 GMTTom Brownhttp://www.sqlservercentral.com/Forums/Topic1232291-149-1.aspx[quote][b]azdzn (1/9/2012)[/b][hr]You can find a few information in DMVs (sys.dm_exec_query_stats) but there will not be that many details.You will only know how many times it has been called since last server restart.You can use profiler to gather following information :- who called the SP (hostname, loginname or ntusername depending on the authentication type)- when it was called- ...[/quote]sys.dm_exec_query_stats doesn't keep information since the last server restart. It keeps information on each query from the time that query enters cache until it leaves cache. When that query leaves the cache, all that data goes away. If that query goes into cache again, it starts over.Also, Profiler is not the tool I'd recommend. Instead I'd use a server side trace, which is a scripted mechanism for gathering the data. Profiler has additional overhead when hitting the system that should be avoided.Tue, 10 Jan 2012 04:22:50 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1232291-149-1.aspxProfiler is quite resource-hungry, but server-side traces barely impact the server at all.I haven't tried filtering by a specific proc name. I sometimes filter by a particular database.Can you post the script you used to create the trace? That would make helping you a bit easier.Mon, 09 Jan 2012 11:07:31 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1232291-149-1.aspxIs there a trick to setting up traces?I have a trace - all set up and working, and collecting data - with the filter set on textdata like '%MyProcName%'exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%MyProcName%'It detects calls from SSMS but not from the application.The call from the application is happening because the call count increases when looking at [code="sql"]SELECT TOP 100 T.*, P.*FROM sys.dm_exec_cached_plans AS PCROSS APPLY sys.dm_exec_sql_text(P.plan_handle) TWHERE text like '%MyProcName%'[/code]Mon, 09 Jan 2012 09:27:35 GMTTom Brownhttp://www.sqlservercentral.com/Forums/Topic1232291-149-1.aspx[quote][b]GSquared (1/9/2012)[/b][hr]Check out sp_trace_create, and fn_trace_getinfo, and fn_trace_gettable. Takes a little bit of study and usually a tiny bit of trial and error, but once you get how to use them and are comfortable with them, server-side traces are a wonderful tool.[/quote]You must be psychic, I was just asking for this :-DMon, 09 Jan 2012 06:47:20 GMTTom Brownhttp://www.sqlservercentral.com/Forums/Topic1232291-149-1.aspxThanks, The profiler creates exactly what I want.But on production I don't have access to profiler, only SSMS. Can I script the profiler actions to create a table for the output? Are profiler jobs resource-intensive? since I'm only logging one rarely-used stored procedure I hope it won't be.Mon, 09 Jan 2012 06:45:49 GMTTom Brownhttp://www.sqlservercentral.com/Forums/Topic1232291-149-1.aspxI use server-side traces to do that kind of thing. Won't necessarily tell you who called a proc, if it's being called by a web-based application for example, but will tell you how often, how long, et al. Capture the text data on it and you can even get parameter values so you can check for things like injection attempts, or common options (for optimization purposes).Very useful technique.Check out sp_trace_create, and fn_trace_getinfo, and fn_trace_gettable. Takes a little bit of study and usually a tiny bit of trial and error, but once you get how to use them and are comfortable with them, server-side traces are a wonderful tool.Mon, 09 Jan 2012 06:39:24 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1232291-149-1.aspxYou can find a few information in DMVs (sys.dm_exec_query_stats) but there will not be that many details.You will only know how many times it has been called since last server restart.You can use profiler to gather following information :- who called the SP (hostname, loginname or ntusername depending on the authentication type)- when it was called- ...Mon, 09 Jan 2012 05:46:35 GMTazdznhttp://www.sqlservercentral.com/Forums/Topic1232291-149-1.aspxI'd like to know how often a stored procedure is called, also useful would be the time, which user made the call, and how long it took.Before I go and write some extra code to record this data from inside the stored procedure it occurred to me that the information is probably already available somewhere.Does SQL Server already do this - perhaps in one of the sys.dm_ views or is it possible to search the transaction log for this data?Mon, 09 Jan 2012 03:23:54 GMTTom Brown