One of the most popular questions I received during my SQL Server Performance Tuning Practical Workshop is how users can run sets of procedure when their SQL Server starts automatically. Today, let us see a simple tutorial to run stored procedure at server startup.
Step 1: Enable Configuration
Change the server configuration to enable scan for startup processes in SQL Server. This option is available via SQL Server Management Studio as well, but we will see today how doing this via T-SQL only.
--Enable Scan for Startup Proc EXEC sys.sp_configure N'scan for startup procs', N'1' GO RECONFIGURE WITH OVERRIDE GO
Step 2: Demo Setup
We will first create a sample stored procedure which we want to run during startup and along with it, we will create a sample table where we will insert the data.
-- Create a sample table CREATE TABLE ServerLog (ID INT IDENTITY(1,1), LogTime DATETIME) GO -- Creat a Procedure CREATE PROCEDURE StartTest AS INSERT INTO ServerLog SELECT GETDATE() GO
Step 3: Configure SP to Run at Startup
In the following option to let us configure the stored procedure to run at startup.
-- Configure SP To run at Startup USE master GO EXEC SP_PROCOPTION StartTest, 'STARTUP', 'ON' GO
Step 4: Test Test Test
Now we will test if our Stored Procedure runs at startup or not. For the same, let us restart our server multiple times.
Step 5: Results – Success
Now we will retrieve data from our test table.
-- Test the Data in ServerLog Table SELECT * FROM ServerLog GO
You can see in our result set that every time SQL Server is restarted it is creating entries in the table.
Well, in this example we have very simple procedure, but now you can see the power of this feature and run any set of server startup processes when your SQL Server starts.
Reference: Pinal Dave (https://blog.sqlauthority.com)