Logo
CAR REVIEW
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
PREGNANCY
 
 
Windows Server

SQL Server 2008 R2 : Creating and Managing Stored Procedures - Startup Procedures

4/23/2013 4:22:22 PM
A SQL Server administrator can create stored procedures that are marked for execution automatically whenever SQL Server starts. They are often referred to as startup procedures. Startup procedures are useful for performing housekeeping-type tasks or starting up a background process when SQL Server starts. Some possible uses for startup procedures include the following:
  • Automatically perform system or maintenance tasks in tempdb, such as creating a global temporary table.

  • Enable custom SQL Server Profiler traces automatically whenever SQL Server is running.

  • Automatically start other external processes on the SQL Server machine, using xp_cmdshell.
  • Prime the data cache with the contents of your critical, frequently used tables.

  • Prime the plan cache by executing procedures or functions you want to have compiled and cached before applications start using them.

To create a startup procedure, you log in as a system administrator and create the procedure in the master database. Then you set the procedure startup option to true by using sp_procoption:

sp_procoption procedure_name, startup, true

If you no longer want the procedure to run at startup, remove the startup option by executing the same procedure and changing the value to false.

By default, a startup procedure runs in the context of the system administrator, but it can use SETUSER to impersonate another account, if necessary. If you need to reference objects in other databases from within a startup procedure, you need to fully qualify the object with the appropriate database and owner names.

Startup procedures are launched asynchronously; that is, SQL Server doesn’t wait for them to complete before continuing with additional startup tasks. This allows a startup procedure to execute in a loop for the duration of the SQL Server process, or it allows several startup procedures to be launched simultaneously. While a startup procedure is running, it runs as a separate worker thread.

Tip

If you need to execute a series of stored procedures in sequence during startup, you can nest the stored procedure calls within a single startup procedure. This approach consumes only a single worker thread.


Any error messages or print statements generated by a startup procedure are written to the SQL Server error log. For example, consider the following whimsical but utterly useless startup procedure:

use master
go
create procedure good_morning
as
print 'Good morning, Dave'
return
go
sp_procoption good_morning, startup, true
go

When SQL Server is restarted, the following entries would be displayed in the error log:

2009-06-12 13:21:00.04 spid5s      Recovery is complete. This is an
 informational message only. No user action is required.
2009-06-12 13:21:00.15 spid5s      Launched startup procedure 'good_morning'.
2009-06-12 13:21:00.15 spid51s     Good morning, Dave


					  

Any result sets generated by a startup procedure vanish into the infamous bit bucket. If you need to return result sets from a startup procedure, the procedure should be written to insert the results into a table. The table needs to be a permanent table and not a temporary table because a temporary table would be automatically dropped when the startup procedure finished executing.

The following example is a startup procedure that could preload all tables within the Sales and Purchasing schemas in the AdventureWorks database into data cache memory on SQL Server startup:

use master
go
create procedure prime_cache
as
declare @tablename varchar(128),
          @schemaname varchar(128)

declare c1 cursor for
select s.name, o.name
     from AdventureWorks.sys.objects o
     join AdventureWorks.sys.schemas s
          on o.schema_id = s.schema_id
     where type = 'U'
     and s.name in ('Sales', 'Purchasing')

open c1
fetch c1 into @schemaname, @tablename
while @@fetch_status = 0
begin
    print 'Loading ''' + @schemaname + '.' + @tablename + ''' into data cache'
    exec ('select * from AdventureWorks.' + @schemaname + '.' + @tablename)
    fetch c1 into @schemaname, @tablename
end
close c1
deallocate c1
return
go

sp_procoption prime_cache, startup, true
go


					  

The error log output from this startup procedure would be similar to the following:

2009-06-15 19:39:18.970 spid7s       Launched startup procedure 'prime_cache'.
2009-06-15 19:39:20.550 spid30s      Loading 'Sales.Store' into data cache
2009-06-15 19:39:20.870 spid30s      Loading 'Sales.StoreContact' into data cache
2009-06-15 19:39:20.870 spid30s      Loading 'Purchasing.ProductVendor' into data
 cache
2009-06-15 19:39:20.950 spid30s      Loading 'Purchasing.Vendor' into data cache
2009-06-15 19:39:21.010 spid30s      Loading 'Purchasing.PurchaseOrderDetail' into
 data cache
2009-06-15 19:39:21.140 spid30s      Loading 'Purchasing.VendorAddress' into data
 cache
2009-06-15 19:39:21.150 spid30s      Loading 'Purchasing.VendorContact' into data
 cache
2009-06-15 19:39:21.160 spid30s      Loading 'Purchasing.PurchaseOrderHeader' into
 data cache
2009-06-15 19:39:21.220 spid30s      Loading 'Sales.ContactCreditCard' into data
 cache
2009-06-15 19:39:21.310 spid30s      Loading 'Sales.CountryRegionCurrency' into
 data cache
2009-06-15 19:39:21.420 spid30s      Loading 'Sales.CreditCard' into data cache
2009-06-15 19:39:21.540 spid30s      Loading 'Sales.Currency' into data cache
2009-06-15 19:39:21.570 spid30s      Loading 'Sales.SalesOrderDetail' into data
 cache
2009-06-15 19:39:22.040 spid30s      Loading 'Sales.CurrencyRate' into data cache
2009-06-15 19:39:22.120 spid30s      Loading 'Sales.Customer' into data cache
2009-06-15 19:39:22.420 spid30s      Loading 'Sales.SalesOrderHeader' into data cache
2009-06-15 19:39:23.170 spid30s      Loading 'Sales.CustomerAddress' into data
 cache
2009-06-15 19:39:23.290 spid30s      Loading 'Sales.SalesOrderHeaderSalesReason'
 into data cache
2009-06-15 19:39:23.340 spid30s      Loading 'Sales.SalesPerson' into data cache
2009-06-15 19:39:23.360 spid30s      Loading 'Sales.SalesPersonQuotaHistory' into
 data cache
2009-06-15 19:39:23.380 spid30s      Loading 'Sales.SalesReason' into data cache
2009-06-15 19:39:23.380 spid30s      Loading 'Sales.Individual' into data cache
2009-06-15 19:39:23.950 spid30s      Loading 'Sales.SalesTaxRate' into data cache
2009-06-15 19:39:23.970 spid30s      Loading 'Sales.SalesTerritory' into data cache
2009-06-15 19:39:24.000 spid30s      Loading 'Sales.SalesTerritoryHistory' into
 data cache
2009-06-15 19:39:24.060 spid30s      Loading 'Purchasing.ShipMethod' into data
 cache
2009-06-15 19:39:24.090 spid30s      Loading 'Sales.ShoppingCartItem' into data
 cache
2009-06-15 19:39:24.100 spid30s      Loading 'Sales.SpecialOffer' into data cache
2009-06-15 19:39:24.110 spid30s      Loading 'Sales.SpecialOfferProduct' into data
 cache


					  

If you want to disable the automatic execution of all startup procedures, you can use sp_configure to disable the scan for startup procs configuration option. Setting this option to 0 disables the running of startup procedures on subsequent SQL Server restarts.

If SQL Server is not currently running and you want to skip running the startup procedures, you can specify Trace Flag 4022 as a startup parameter. You can set the trace flag for a SQL Server instance by using the SQL Server Configuration Manager. In SQL Server Configuration Manager, perform the following steps:

1.
Click on SQL Server 2008 Services.

2.
In the right pane, right-click the SQL Server instance you want to set the trace flag for and select Properties.

3.
Go to the Advanced tab and select the Startup Parameters box.

4.
Click the expand arrow to the right of the input field to expand the entire field.

5.
Place your cursor at the end of the value and type a semicolon (;).

6.
Type -T4022 (see Figure 1).

Figure 1. Setting Trace Flag 4022 to prevent startup procedures from executing.

7.
Click OK.

Also, if you start SQL Server with minimal configuration (by using the -f flag), the startup stored procedures are not executed.

Other -----------------
- SQL Server 2008 R2 : Creating and Managing Stored Procedures - Using System Stored Procedures
- Windows Server 2003 : Windows Firewall (part 3) - Service Pack Firewall Modifications - Modifying firewall behavior using the Windows Firewall INF file and unattend.txt
- Windows Server 2003 : Windows Firewall (part 2) - Service Pack Firewall Modifications - Modifications
- Windows Server 2003 : Windows Firewall (part 1) - Internet Connection Firewall
- Windows Server 2003 on HP ProLiant Servers : Server Placement (part 3) - Flexible Single Master Operations (FSMO) Placement
- Windows Server 2003 on HP ProLiant Servers : Server Placement (part 2) - DC Placement, GC Placement
- Windows Server 2003 on HP ProLiant Servers : Server Placement (part 1) - DNS Placement, Site Affinity
- Managing SharePoint 2010 with Windows PowerShell : Managing SharePoint 2010 Sites (part 2)
- Managing SharePoint 2010 with Windows PowerShell : Managing SharePoint 2010 Sites (part 1)
- System Center Configuration Manager 2007 : Reporting Configuration (part 3) - Console Reporting Links, Relational Database Concepts
- System Center Configuration Manager 2007 : Reporting Configuration (part 2) - Copying ConfigMgr Classic Reports to SQL Reporting Services, Report Categories
- System Center Configuration Manager 2007 : Reporting Configuration (part 1) - Configuring the Reporting Point for Classic Reporting, SRS Reporting
- System Center Configuration Manager 2007 : ConfigMgr Classic Reports Versus SQL Reporting Services
- Deploying the Client for Microsoft Exchange Server 2007 : Deploying with Microsoft Systems Management Server, Managing Postdeployment Tasks
- Deploying the Client for Microsoft Exchange Server 2007 : Installing the Exchange Client, Pushing Client Software with Windows Server 2003 Group Policies
- Integrating BizTalk Server 2010 and Microsoft Dynamics CRM : Communicating from Dynamics CRM to BizTalk Server (part 3) - Registering the plugin, Testing the plugin
- Integrating BizTalk Server 2010 and Microsoft Dynamics CRM : Communicating from Dynamics CRM to BizTalk Server (part 2) - Writing the Dynamics CRM plugin
- Integrating BizTalk Server 2010 and Microsoft Dynamics CRM : Communicating from Dynamics CRM to BizTalk Server (part 1) - Setup
- SharePoint 2010 : Farm Governance - Configuring a Managed account
- SharePoint 2010 : Farm Governance - Administering SharePoint Designer
 
 
Most view of day
- Windows Server 2012 Administration : Defining the Administrative Model
- SQL Server 2008 R2 : Performance Monitoring Tools (part 5) - Creating a Customized Data Collection Set
- Managing Digital Movies (part 3) - Watching and Managing Movies with Windows Media Player,
- SQL Server 2008 R2 : Performance Monitoring Tools (part 1)
- Creating DVD Movies with Windows DVD Maker (part 6) - Changing Other DVD Options - Customizing the Menu , Configuring the Photo Slide Show
- Administering an Exchange Server 2013 Environment (part 8) - Understanding Archiving
- Windows Server 2012 : Installing and Managing Hyper-V in Full or Server Core Mode - Verifying Hyper-V requirements
- SharePoint 2010 : Configuring Search Settings and the User Interface - Search Alerts Administration, Search Suggestions
- Microsoft Project 2010 : Creating a Budget for Your Project (part 1) - Creating and Assigning Budget Resources
- Protecting Windows from Viruses and Spyware : Antimalware Strategy: Defense in Depth (part 1) - Windows Action Center
Top 10
- Microsoft Project 2010 : Linking Tasks (part 8) - Auditing Task Links,Using the Task Inspector
- Microsoft Project 2010 : Linking Tasks (part 7) - Creating Links by Using the Mouse,Working with Automatic Linking Options
- Microsoft Project 2010 : Linking Tasks (part 6) - Creating Links by Using the Entry Table
- Microsoft Project 2010 : Linking Tasks (part 5) - Creating Links by Using the Task Information Dialog Box
- Microsoft Project 2010 : Linking Tasks (part 4) - Entering Leads and Lags, Creating Links by Using the Menu or Toolbar
- Microsoft Project 2010 : Linking Tasks (part 3) - Using the Start-to-Start Relationship,Using the Finish-to-Finish Relationship
- Microsoft Project 2010 : Linking Tasks (part 2) - Using the Start-to-Start Relationship,Using the Finish-to-Finish Relationship
- Microsoft Project 2010 : Linking Tasks (part 1) - Defining Dependency Links
- Microsoft Project 2010 : Defining Task Logic - Manipulating Your Schedule
- Microsoft Lync Server 2013 : Director Troubleshooting (part 3) - Synthetic Transactions,Telnet
 
 
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
2015 Camaro