In this post I cover how to use one of SQL Server’s lesser-known features, startup procedures, to automate sp_BlitzWho in order to capture ongoing session activity as soon as the instance starts up.
What are Startup Procedures
Startup Procedures are stored procedures that reside in the master database and are executed every time the instance starts up.
Their execution is triggered when all databases are recovered and the “Recovery is completed” message is logged at startup.
Members of the sysadmin fixed server role can use the sp_procoption system stored procedure to mark a stored procedure as a startup procedure.
Requirements
In order to set a stored procedure to be marked as a startup procedure, the following requirements must be met:
- you must be a member of the sysadmin fixed server role
- the stored procedure must be in the dbo schema of the master database
- the stored procedure must not contain INPUT or OUTPUT parameters
Use cases
Like in this example, it can be used to set up a lightweight activity logging by running sp_BlitzWho in a WHILE loop.
But it’s not limited to that, you can use it for various other tasks, such as:
- archive or delete old data from logging tables on instance startup
- log the contents of sys.configurations and/or check for configuration drift
- use Database Mail to notify the DBA team of an instance restart
Why startup procedures and not SQL Server Agent jobs?
Startup Procedures aren’t a replacement for SQL Server Agent jobs, since they lack the robust error handling and scheduling that the latter offers.
But they are perfect for startup related tasks that can be automated on instances that do not have the SQL Server Agent service enabled.
Configuring a SQL Server startup procedure
I already have a database named DBATools in which I have the sp_Blitz stored procedures, which I keep updated using the method I’ve demoed in this post.
Now I want to add an error logging table.
| 1 2 3 4 5 6 7 8 9 10 11 | CREATE TABLE DBATools.dbo.Errors ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED, ErrorSource VARCHAR(200), ErrorNumber INT, ErrorState INT, ErrorSeverity INT, ErrorLine INT, ErrorMessage NVARCHAR(4000), ErrorDate DATETIME ); |
Next, I create the stored procedure that will act as a wrapper for sp_BlitzWho.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | USE [master] GO CREATE PROCEDURE sp_AutoBlitzWho AS /*The insert to the AutoBlitzWhoOut table will fail if these session-level options aren't set to ON*/ SET CONCAT_NULL_YIELDS_NULL, ANSI_PADDING, ANSI_WARNINGS ON; BEGIN WHILE 1 = 1 BEGIN BEGIN TRY /*Capture active sessions to table*/ EXEC DBATools.dbo.sp_BlitzWho @OutputDatabaseName = N'DBATools', @OutputSchemaName = N'dbo', @OutputTableName = N'AutoBlitzWhoOut', /*Keep only 3 days worth of data*/ @OutputTableRetentionDays = 3; /*Wait 10 seconds*/ WAITFOR DELAY '00:00:10'; END TRY BEGIN CATCH /*Log error message to table*/ DECLARE @ErrorMsg NVARCHAR(4000), @ErrorLine INT, @ErrorNumber INT, @ErrorSev INT, @ErrorState INT; SELECT @ErrorMsg = ERROR_MESSAGE(), @ErrorLine = ERROR_LINE(), @ErrorNumber = ERROR_NUMBER(), @ErrorState = ERROR_STATE(), @ErrorSev = ERROR_SEVERITY(); INSERT INTO DBATools.dbo.Errors (ErrorSource, ErrorNumber, ErrorState, ErrorSeverity, ErrorLine, ErrorMessage, ErrorDate) VALUES ('sp_AutoBlitzWho', @ErrorNumber, @ErrorState, @ErrorSev, @ErrorLine, @ErrorMsg, GETDATE()) END CATCH; END; END; |
Since 1 is always equal to 1, sp_BlitzWho will be executed in a loop, and WAITFOR tells SQL Server to wait 10 seconds after each execution
Now, to enable automatic execution at startup for sp_AutoBlitzWho:
| 1 2 3 4 5 | USE [master] GO EXEC sp_procoption @ProcName = N'dbo.sp_AutoBlitzWho' ,@OptionName = 'startup' ,@OptionValue = 'on'; |
Testing it
After everything is in place, all I have to do is restart the instance.
Once the instance starts up, I can confirm that the procedure was executed by checking the instance log.
Note that sp_BlitzWho outputs an informational message on every execution that will also get logged.
But the Checking for Read Intent databases to exclude message can be suppressed by commenting out the following line in sp_BlitzWho.
| 1 | RAISERROR('Checking for Read intent databases to exclude',0,0) WITH NOWAIT; |
Now, to check if the procedure is actually capturing any activity from my test workload:
| 1 2 | SELECT * FROM DBATools.dbo.AutoBlitzWhoOut WITH(NOLOCK); |
And, to check if any execution in the loop error-ed out:
| 1 2 | SELECT * FROM DBATools.dbo.Errors WITH(NOLOCK); |
To disable startup execution of the procedure:
| 1 2 3 4 5 | USE [master] GO EXEC sp_procoption @ProcName = N'dbo.sp_AutoBlitzWho' ,@OptionName = 'startup' ,@OptionValue = 'off'; |
Note that, even if the above command is executed, the execution loop will run until the next instance restart or until the SPID executing the stored procedure is killed.
In my case, it would be SPID 45, as seen in the second screenshot from the instance’s log.
To check for startup stored procedures, you can use the following query:
| 1 2 3 4 5 6 | USE [master] GO SELECT [name], [is_auto_executed] FROM sys.procedures WHERE [is_auto_executed] = 1; |
Note about the session-level options used in my procedure
In my initial test run nothing was captured to the output table, and the Errors table was full of:
| 1 2 3 4 5 | INSERT failed because the following SET options have incorrect settings: 'CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. |
Hence setting CONCAT_NULL_YIELDS_NULL, ANSI_PADDING, and ANSI_WARNINGS to ON.
Conclusion
Startup Procedure is a less-talked about, but very useful feature of SQL Server.
It can come in very handy on an instance where you don’t have other ways of ensuring specific actions are taken/started at instance startup.