Chapter 4: Optimizing and Automating SQL Server Administration

In the previous chapters, we covered some of the common administration issues with SQL Server. Now, we’ll discuss some of the ways you can optimize your administration and become a more productive DBA. In this chapter, we’ll explore some common administration issues, SQL Server Agent, multiserver administration, and DTS.

Startup Stored Procedures

Administrators often need to write stored procedures that execute when SQL Server begins. Some of the common reasons for writing a stored procedure are to have the following tasks execute at startup:

  • Write events to an audit log

  • Create global temporary tables (##temptablename) that you use in your applications

  • Perform general housekeeping, such as cleaning up old records and error logs

When these needs arise, you can use the sp_procoption system stored procedure to set your stored procedure to execute on the start of the SQL Server instance. You can perform the same action in Enterprise Manager by checking the Execute Whenever SQL Server Starts option in a stored procedure’s properties. Stored procedures that execute on the startup of your server can only exist in the master database, and you must be a sysadmin to turn on the option. To use the sp_procoption stored procedure, use the following syntax:

EXEC sp_procoption 'sp_procedurename', 'startup', 'true'

This enables the sp_procedurename stored procedure to execute every time your SQL Server is started. In order for this option to work, the Scan For Startup Procs server option must be enabled by using sp_configure. This server option is automatically set to 1 when you set your first stored procedure to execute on startup, and it is set back to 0 when the stored procedure is unmarked for startup.

If you notice that your stored procedures aren’t executing at startup, the first thing to do is check the Scan For Startup Procs option to determine if it has been set manually to 0.

Once executed, your stored procedure can call other stored procedures and execute triggers. By nesting stored procedures, you can work around the limitation that the stored procedure must be in the master database. If you have a problem with one of the startup stored procedures, you can start SQL Server with the -f switch to bypass them. Then correct the problem or the problem procedures from the startup list and start SQL Server in its original state.

You can also start SQL Server with the trace flag of 4022. For example, the full syntax to start SQL Server and bypass the startup stored procedures is:

NET START MSSQLSERVER -T4022




SQL Server 2000 for Experienced DBAs
ppk on JavaScript, 1/e
ISBN: 72227885
EAN: 2147483647
Year: 2005
Pages: 126

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net