Tracking Stored Procedures
-
I would like track 1) the date a stored procedure is ran and 2) the application that calls the stored procedure. I would like the info stored in a table. How should I go about this? Trigger? Profiler? Modifying the stored procedure?
-
In the short term I would run a trace.
Other ways are to use the following to write to the Windows event log.
RAISERROR('Procedure name xxxxxx ran',10,1) WITH LOG,NOWAIT
You can have the procedure write to a specific table to track its use, say to record the GETDATE() and SUSER_SNAME() values.
-
Personally I would (and do) use profiler, and save the results to a table which I could (and can) query later.
HTH
Dave J
-
the problem with tracking the App that called the procedure is not an easy one because the connection string takes whatever you pass to it
including "BLANK"
* Noel -
What about server performance if profiler is running the trace continuously?
-
Run the trace without using the profiler. The profiler GUI slaughters performance.
-
David.Poole,
Could you provide sample code? This is what I have currently added to the stored procedure:
INSERT INTO Test
(StoredProcedure, UserName, ComputerName, Application, RunDate)
VALUES ('TESTasp_AddressDetail', SUSER_SNAME(), HOST_NAME(), APP_NAME (), GETDATE())
So naturally the Test table gets populated with this data each time it is run. I have not used trace without profiler or via T-SQL (just the GUI).
-
You can use server side traces. If you restrict the types of events and columns captured, you can really reduce the footprint. However, as another poster indicated, the application name can be forged, so it can't be seen as 100% reliable data from a security perspective.
EDIT: SQL Server Profiler can be used to build the trace script. In 2000's version, File | Script Trace. In 2005's version, File | Export | Script Trace Definition.
K. Brian Kelley
@kbriankelley -
This is how I do it; I can use it temporarily or use it as a production audit tool. I've even used it during troubleshooting in order to follow execution. It's been very helpful. Hope it helps you. There are 3 sections here: the table DDL, an SP and usage.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TransmissionLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TransmissionLog]
GO
CREATE TABLE [dbo].[TransmissionLog] (
[LogID] [int] IDENTITY (1, 1) NOT NULL ,
[LogTime] [datetime] NULL ,
[TransmissionID] [int] NULL ,
[ApplicationType] [varchar] (20) COLLATE SQL_Latin1_General_Pref_CP437_CI_AS NULL ,
[Application] [varchar] (100) COLLATE SQL_Latin1_General_Pref_CP437_CI_AS NULL ,
[DataCount] [int] NULL ,
[LogMessage] [varchar] (1024) COLLATE SQL_Latin1_General_Pref_CP437_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TransmissionLog] WITH NOCHECK ADD
CONSTRAINT [PK_TransmissionLog] PRIMARY KEY CLUSTERED
(
[LogID]
 
WITH FILLFACTOR = 90 ON [PRIMARY]GO
----------------------------------------------------------------------------------------------------------
CREATE PROC dbo.usp_LogMessage @ApplicationType varchar(20),
@Application varchar(50),
@TransmissionID int,
@DataCount int,
@LogMessage varchar(1024)
/***********************************************************************************
usp_LogMessage
Logs message to Process Log table.
-----------------------------------------------------------------------------------
CHANGE HISTORY
-----------------------------------------------------------------------------------
DATE WHO COMMENT
10/27/2005 Carl Start.
***********************************************************************************/
AS
SET NOCOUNT ON
INSERT TransmissionLog (LogTime, TransmissionID, ApplicationType, Application, DataCount, LogMessage)
values (Getdate(),@TransmissionID, @ApplicationType, @Application, @DataCount, @LogMessage)
if @@ROWCOUNT = 0 return -1
RETURN 0
grant all on dbo.usp_LogMessage to public
GO
----------------------------------------------------------------------------------------------------------
--
--The following is used in a DTS SQL task
--
insert into TransmissionLog (LogTime, TransmissionID, ApplicationType, Application, DataCount, LogMessage)
values (Getdate(), 11, 'DTS', 'CMDxxxxxx.EPS_QuEST_Update_Tables_a', 0, 'yyyyyy.EPSAsyncReports.dbo.EPSRequest_rpt rows transformed into CMDTECHSQL1.EOS.dbo.EPS_QuEST_1')
--
--The following is used in a stored proc
--
SELECT @Message = 'Error: No parameter combination satisfied.'
exec dbo.usp_LogMessage 'Stored Procedure', 'usp_RetentionSummaryRp', 0, 0, @Message
--or just
insert into TransmissionLog (LogTime, TransmissionID, ApplicationType, Application, DataCount, LogMessage)
values (Getdate(), @JVPID, 'Stored Procedure', @Application, @Count, @Message)
-
We created a table to hold the data and a small stored procedure we added in the first lines and the last lines of every procedure. When procedure XYZ was ran it fired off our procedure to get the start time and when it completed it ran the procedure again to track when it completed. We could use this to track the procedure over time to see how the procedure performs. We had all our procedures in SourceSafe and if we saw a procedure start taking a lot more time to complete then it did in the past we could find out what was changed and try to correct it.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply