Ejecutar un procedimiento al iniciar o reiniciar la instancia en Sql Server 2008

¿Alguna vez has necesitado que se ejecute un procedimiento almacenado apenas se inicie o reinicie la instancia?

Esta solución la necesité cuando tenía algunas tablas en la base de datos tempdb, que como saben se borran cada vez que se reinicia la instancia, por lo cual necesité garantizar que cada vez que se reiniciaba la instancia se crearan las tablas. 

¡Espero sea de utilidad! 

Nota: Debemos tener claro que solo podemos hacer que se ejecute un procedimiento almacenado, el cual no debe recibir parámetros y debe estar creado en la base de datos Master. Sin embargo este procedimiento almacenado puede realizar internamente operaciones sobre otras bases de datos. 

1. Debemos verificar que el parámetro 'scan for startup procs' debe tener el valor '1', ¿Como verificamos el valor de este parámetro?

USE MASTER
GO
SELECT VALUE, VALUE_IN_USE, DESCRIPTION 
FROM SYS.CONFIGURATIONS 
WHERE NAME = 'scan for startup procs'
GO

Si el resultado de la consulta es diferente a '1', no habrá problema, en el paso tres lo solucionaremos.

2. En este paso haremos el procedimiento almacenado que se ejecutará cuando se inicie/reinicie la instancia. Para este ejemplo crearemos un procedimiento que crea una tabla en la base de datos temporal.

USE MASTER
GO
CREATE  PROCEDURE Usp_Create_Dummy as
DECLARE @sql VARCHAR(4000) =  ' '
SET @sql  = @sql +  'USE tempdb  '
SET @sql  = @sql + CHAR(13)
SET @sql =  @sql +  'SET ANSI_NULLS ON  '
SET @sql  = @sql + CHAR(13)
SET @sql =  @sql +  'SET QUOTED_IDENTIFIER ON  '
SET @sql  = @sql + CHAR(13)
SET @sql =  @sql +  'CREATE TABLE [dbo].[TablaDummy]( '
SET @sql =  @sql +  ' [llave] [uniqueidentifier] NOT NULL, '
SET @sql =  @sql +  ' [Valor] [ntext] NULL'
SET @sql =  @sql +  ')'
SET @sql  = @sql + CHAR(13)
EXEC (@sql)

3. Ahora asociaremos el procedimiento para que se ejecute cuando se inicie/reinicie la instancia:

USE MASTER
GO
EXEC sp_procoption @ProcName = 'Usp_Create_Dummy' , @OptionName =  'startup' , @OptionValue = 'on'; 
GO

Al ejecutar el paso tres automáticamente el valor del parámetro 'scan for startup procs' cambiará a '1' solo se debe reiniciar el servicio de Sql Server. 

4. El último punto es, ¿Como verificamos que nuestro procedimiento almacenado está en la lista de ejecución?, es muy sencillo con la siguiente consulta lo verificamos:

SELECT name,create_date,modify_date
FROM sys.procedures
WHERE OBJECTPROPERTY(OBJECT_ID, 'ExecIsStartup') = 1

Espero sea de utilidad.