Using a startup Stored Procedure in SQL Server

Using a startup Stored Procedure in SQL Server

3 Comments / Database Maintenance, Stored Procedures / By

SQL Server allows marking any stored procedure as a “startup” procedure which means it will get executed upon a service restart. A “startup” procedure has to reside in the master database and cannot accept input parameters – cool, this is something we can live with.

I find this option to be very simple and practical when maintaining and supporting sql server instances as it allows for a single focal point to carry out all the tasks that are needed when the service goes up and have the advantage of being portable.

My standard template that you will find in all instances we maintain would carry out the following tasks:

  1. Start trace flag 1222 to enable writing of deadlock events into the the errorlog using the DBCC TRACEON command
  2. Start trace flag 3226 to disable the default behavior of the service that writes the backup information to the errorlog
  3. Start various SQL Traces such a trace that captures all exceptions that occur in the database engine
  4. Start a perfmon Data Collector

The option of starting a trace flag using the DBCC TRACEON command is so much convenient than passing the trace flag as an argument to the service using the SQL Server Configuration Manager that offers a UI for saving that data to the system’s registry plus no service restart required to take effect.

 

USE master;
EXEC sp_configure 'show advanced option', 1;  RECONFIGURE;
EXEC sp_configure 'scan for startup procs', 1; RECONFIGURE;
GO
EXEC sp_configure 'default trace enabled', 0; RECONFIGURE;
GO


USE [master];
SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON;
GO
IF  EXISTS (SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID(N'[dbo].[Initialize]') AND type IN (N'P', N'PC'))
	DROP PROCEDURE [dbo].Initialize;
GO

CREATE PROCEDURE Initialize  
AS  
SET NOCOUNT ON;  
  
  
-- Write deadlock information to the errorlog.  
DBCC TRACEON (1222, -1);  
-- DBCC TRACESTATUS (-1)  
  
-- Prevent the backup completed information msg being written to the errorlog  
DBCC TRACEON (3226, -1);  

-- Identity behaviour like 2008R2
--DBCC TRACEON (272, -1);    

-- Improve auoto stats
--DBCC TRACEON (2371, -1);

-- Tempdb allocations (prevents waits of type PAGELATCH_EX)
--DBCC TRACEON (1118, -1);


--EXEC DBA.dbo.TraceDuration @MaxFileSize = 50, @FileCount = 20, @Path = 'F:\Traces\TraceDuration';  

EXEC DBA.dbo.TraceErrors @MaxFileSize = 50, @FileCount = 10, @Path = 'F:\Traces\TraceErrors';  

EXEC DBA.dbo.TraceExceptions @MaxFileSize = 50, @FileCount = 10, @Path = 'F:\Traces\TraceExceptions'; 

EXEC DBA.dbo.TraceBlockedProcessReport @MaxFileSize = 50, @FileCount = 10, @Path = N'F:\Traces\TraceBlockedProcessReport';

-- Start perfmon counters collection
EXEC xp_cmdshell 'logman start baseline', no_output ;  
  
/*  
-- Start BlackBox trace  
DECLARE @TraceId int, @maxfilesize bigint;  
SELECT @maxfilesize = 50;  
  
EXEC sp_trace_create  
   @TraceId OUTPUT  
  ,@options  = 8  
  ,@tracefile = NULL  
  ,@maxfilesize = @maxfilesize ;  
  
EXEC sp_trace_setstatus @TraceId, 1 ;  
*/  
GO

EXEC sp_procoption N'Initialize', 'startup', 'on';
GO
The following two tabs change content below.
My LinkedIn profile

Yaniv Etrogi

Yaniv Etrogi is an SQL Server consultant. He loves SQL Server and he is passionate about Performance Tuning, Automation and Monitoring.

3 thoughts on “Using a startup Stored Procedure in SQL Server”

  1. Pingback: SQL Server Utilities | Use SQL Trace to capture database exceptions - SQL Server Utilities

    1. Yaniv

      HI
      In the same folder I have these 2 files:
      1. PerformanceCounters.txt
      2. StartHealthCheck.bat

      This is the contents of the first file, PerformanceCounters.txt:

      \Network Interface(*)\Bytes Total/sec
      \Network Interface(*)\Bytes Received/sec
      \Network Interface(*)\Bytes Sent/sec
      \Network Interface(*)\Output Queue Length

      \Memory\Available MBytes
      \Memory\Pages Input/sec
      \Paging File\%Usage

      \Processor(_Total)\% Processor Time
      \Process(sqlservr)\% Processor Time

      \System\Processor Queue Length
      \System\Context Switches/sec

      \PhysicalDisk(*)\Avg. Disk Sec/Read
      \PhysicalDisk(*)\Avg. Disk Sec/Write
      \PhysicalDisk(*)\Disk Read Bytes/sec
      \PhysicalDisk(*)\Disk Write Bytes/sec
      \PhysicalDisk(*)\Current Disk Queue Length
      \PhysicalDisk(*)\Disk Reads/sec
      \PhysicalDisk(*)\Disk Writes/sec

      \SQLServer:Memory Manager\Memory Grants Pending
      \SQLServer:Memory Manager\Target Server Memory (KB)
      \SQLServer:Memory Manager\Total Server Memory (KB)

      \SQLServer:Buffer Manager\Page life expectancy
      \SQLServer:Buffer Manager\Lazy Writes/Sec
      \SQLServer:Buffer Manager\Checkpoint pages/sec
      \SQLServer:Buffer Manager\Free List Stalls/sec
      \SQLServer:Buffer Manager\Buffer Cache Hit Ratio

      \SQLServer:Plan Cache(Object Plans)\Cache Object Counts
      \SQLServer:Plan Cache(Object Plans)\Cache Pages
      \SQLServer:Plan Cache(SQL Plans)\Cache Object Counts
      \SQLServer:Plan Cache(SQL Plans)\Cache Pages
      \SQLServer:Plan Cache(SQL Plans)\Cache Hit Ratio

      \SQLServer:SQL Statistics\Batch Requests/sec
      \SQLServer:SQL Statistics\SQL Compilations/sec
      \SQLServer:SQL Statistics\SQL Re-Compilations/sec

      \SQLServer:General Statistics\User Connections
      \SQLServer:General Statistics\Logins/sec

      \SQLServer:Access Methods\Full Scans/sec
      \SQLServer:Access Methods\Index Searches/sec
      \SQLServer:Access Methods\Workfiles Created/sec
      \SQLServer:Access Methods\Worktables Created/sec

      \SQLServer:Databases(*)\Data File(s) Size (KB)
      \SQLServer:Databases(*)\Log File(s) Size (KB)
      \SQLServer:Databases(*)\Percent Log Used
      \SQLServer:Databases(*)\Log Growths
      \SQLServer:Databases(*)\Log Truncations
      \SQLServer:Databases(*)\Log Flushes/sec
      \SQLServer:Databases(*)\Log Flush Waits/sec

      \SQLServer:SQL Statistics\SQL Attention Rate/sec

      # \SQLServer:Locks(_Total)\Lock Requests/sec
      # \SQLServer:Locks(_Total)\Lock Waits/sec
      # \SQLServer:Locks(_Total)\Average Wait Time (ms)
      # \SQLServer:Locks(_Total)\Number of Deadlocks/sec
      # \SQLServer:Latches\Latch Waits/sec
      # \SQLServer:Latches\Average Latch Wait Time (ms)

      # \SQLServer:Cursor Manager by Type\Active Cursors

      This is the content of the second file, StartHealthCheck.bat:
      logman create counter HealthCheck -cf PerformanceCounters.txt -si 00:00:15 -f bin -b 00:00:00AM -e 23:59:00PM -r -v mmddhhmm -o HealthCheck

      logman start HealthCheck

      The first logman command creates a Perfmon Data Collection using the counters listed in the first file.
      The second logman command just starts the Perfmon Data Collection.

      Following that I use the UI (start–>run–>perfmon.msc) to modify the stop condition to 1 day and check the box that states “Restart the data collection set at limits”
      When I created this years back I did not find how to achieve that using the logman command and ended up doing this bit manually.

      Reply

Leave a Comment

Your email address will not be published. Required fields are marked *