How to log all SQL Server and SQL Agent starts

SQL Server

To log all SQL Server starts, simply execute the T-SQL code provided below to create a new table and Stored Procedure (SP) to log this event:

USE LoggingDB
GO
CREATE TABLE dbo.SQLServerStartTimes
(
LogId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
StartTime DATETIME NOT NULL
CONSTRAINT DF_ServerStartTimes_StartTime DEFAULT GETDATE()
)

USE master
GO
CREATE PROCEDURE dbo.sp_LogSQLServerStart
AS
BEGIN

SET NOCOUNT ON

PRINT '*** LOGGING SERVER STARTUP TIME ***'
INSERT INTO LoggingDB.dbo.SQLServerStartTimes DEFAULT VALUES
END
GO

To trigger/execute this SP once SQL Server starts, use this statement (more on MSDN about this built-in Stored Procedure)
EXEC SP_PROCOPTION sp_LogSQLServerStart, 'STARTUP', 'ON'
GO

Result:

SQL Agent

For SQL Agent starts, simply execute the T-SQL code provided below to create a new table and SP to log this event:


USE LoggingDB
GO
CREATE TABLE dbo.SQLAgentStartTimes
(
LogId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
StartTime DATETIME NOT NULL
CONSTRAINT DF_SQLAgentStartTimes_StartTime DEFAULT GETDATE()
)

USE master
GO
CREATE PROCEDURE dbo.sp_LogSQLAgentStart
AS
BEGIN

SET NOCOUNT ON

PRINT '*** LOGGING SERVER STARTUP TIME ***'
INSERT INTO LoggingDB.dbo.SQLAgentStartTimes DEFAULT VALUES
END

Now, since all coding is done…we still need to setup a new SQL Agent job to execute this SP once SQL Agent starts.
Create a new job in SQL Agent and click on Schedules, select

Result:

Special thanks to Armando Prato for the inspiration!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s