Product:
Microsoft SQL server 2016
Issue:
How log start and stop times of SP in your database?
Suggested solution:
Create a table to store time values, and create a SP that you call from other SP to update the table.
Create the log table:
CREATE TABLE [dbo].[_log_logged](
[LogID] [BIGINT] NULL,
[starttime] [DATETIME] NULL,
[endtime] [DATETIME] NULL,
[iscomplete] [INT] NULL,
[runstatus] [INT] NULL,
[durationInSeconds] [INT] NULL,
[whatProcess] [VARCHAR](100) NULL
) ON [PRIMARY]
GO
Create the SP to call to create log values:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[_logger] @LogID [INT], @StartTime [DATETIME],@EndTime [DATETIME], @WhatProcess [VARCHAR](100), @NewLogID [INT] OUT
AS BEGIN
SET NOCOUNT ON;
DECLARE @RunTimeSeconds INT,
@RunStatus INT,
@IsComplete INT,
@NewLogRowID INT,
@myDuration NUMERIC(20,10)
IF @LogID IS NULL
SET @LogID = 0
IF @LogID = 0
BEGIN
SET @RunStatus = 1
SET @IsComplete = 0
SET @LogID = (SELECT ISNULL((MAX(LogID)),0) + 1 AS maxID FROM _log_logged)
INSERT INTO _log_logged(LogID
,StartTime
,runStatus
,IsComplete
,WhatProcess
)
SELECT @LogID,
@StartTime,
@RunStatus,
@IsComplete,
@WhatProcess
END
ELSE IF @LogID != 0
BEGIN
SET @RunStatus = 0
SET @IsComplete = 1
UPDATE a
SET EndTime = @EndTime,
IsComplete = @IsComplete,
RunStatus = @RunStatus,
whatProcess = @WhatProcess,
DurationInSeconds= DATEDIFF(SECOND,@StartTime, @EndTime)
FROM _log_logged a
WHERE a.LogID = @LogID
END
SET @NewLogID = @LogID
END
GO
Add below code to your SP, to enable them to log values to the above table.
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[USP_ThisIsTheTest]
AS
BEGIN
SET NOCOUNT ON;
declare @StartTime datetime = GETDATE();
declare @EndTime datetime
declare @logid int
DECLARE @NewLogID INT
DECLARE @whatProcessText VARCHAR(100) = 'Your name of the sp to be logged';
EXEC [dbo].[_logger] @LogID = 0
,@StartTime = @StartTime
,@EndTime = NULL
,@whatProcess = @whatProcessText
,@NewLogID = @NewLogID OUTPUT
-- =============================================
-- Here should your code be for the process to execute
WAITFOR DELAY '00:00:10.000'
-- =============================================
-- Log the end of the sp process with this call:
SET @EndTime = GETDATE()
EXEC [dbo].[_logger] @LogID = @NewLogID, @StartTime = @StartTime ,@EndTime = @EndTime ,@whatProcess = @whatProcessText ,@NewLogID = @NewLogID OUTPUT
END
Run the SP to test the log table.
The result can look like this:
More information:
SLEEP Command in Sql Server
https://www.mssqltips.com/sqlservertip/2003/simple-process-to-track-and-log-sql-server-stored-procedure-use/
https://www.sqlservercentral.com/articles/logging-and-error-handling-for-sql-stored-procedures
https://www.tech-recipes.com/database/sql-server-error-logging-and-reporting-within-a-stored-procedure/
