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/