INF: Troubleshooting SQL Executive and Task Scheduling

• Microsoft SQL Server, versions 6.0 and 6.5

SUMMARY

The SQL Executive component of SQL Server is responsible for running various aspects of server operations, such as replication and task scheduling. The information in this article helps troubleshoot why SQL Executive may stop responding or running tasks.

MORE INFORMATION

SQL-DMO ERROR MESSAGE - 1722

If you have changed the Windows NT Server computer name, but not the SQL Server name, and the @@SERVERNAME variable is neither NULL nor matches the Windows NT Server computer name, you must run the sp_drop server and sp_addserver <server name>, LOCAL commands to alter the SQL Server name. For additional information, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q140683
   TITLE     : INF: Run Task May Cause 1722 Error

   SELECT @@SERVERNAME

Stop and restart the MSSQLSERVER service for the LOCAL parameter to take effect.

LOGON ACCOUNT

The SQL Executive can log on as either the System Account or This Account. The general recommendation is to create a user account in the domain/administrators group with the following attributes:

   Account Information

   User Name   : SQLServer
   Full Name   : SQL Server admin account
   Description : SQL Server administration account
   Password    : <enter password for account>
   Confirm Pass: <enter password again>

   Account Attributes

   x User cannot change password
   x Password never expires

   Member of

   Domain Administrators

LOCAL GROUP MEMBERSHIP

If SQL Executive is logging on as This Account, you should verify that the account is a member of the local computer's Administrator group. If the account you are using is not a member of the local computer's Administrator group, add it. This is important, and if not done, SQL Executive cannot log on properly.

If you are still having problems getting SQL Executive to log on properly, there is probably a permissions restriction in the registry. Check to see that the Administrator group and local system account have full control of the SQL Executive folders in the following registry locations:

   HKEY_LOCAL_MACHINE on Local Machine
      \SOFTWARE
         \MICROSOFT
            \MSSQLServer
               \SQLExecutive

   -and-

   HKEY_LOCAL_MACHINE on Local Machine
      \SYSTEM
         \CurrentControlSet
            \Services
               \SQLExecutive

   ARTICLE-ID: Q119401
   TITLE     : INF: Rebuilding SQL Server Entries After Reinstalling Win NT

NETWORK PROTOCOL ISSUES

The SQL Executive service requires a Net-Library that allows a trusted connection. The only Net-Libraries that support a trusted connection are named pipes and multi-protocol.

Additionally, if you have ever had a parallel installation of SQL Server with both versions 4.21 and 6.0 running on the same computer, and SQL 6.0 or 6.5 is set up to listen on an alternate named pipe, you must change the registry entry for the ServerHost value for the SQL Executive service so that it connects to the correct pipe.

NOTE: Incorrect changes or deletions made to the registry may damage Windows NT Server and prevent the system from running normally. Review the Windows NT Books OnLine for information on backing up the registry. Changes to the ServerHost value can be found in the following registry location:

HKEY_LOCAL_MACHINE on Local Machine

   \SOFTWARE
      \MICROSOFT
        \MSSQLServer
           \SQLExecutive
              ServerHost

If SQL Server is set up to listen on multi-protocol and NOT named pipes, the SQL Client Configuration utility also needs to have multi-protocol as the default Net-Library. In addition, the Advanced tab of the SQL Client Configuration utility needs to include the Windows NT Server machine name in the server box and multi-protocol in the DLL box. Click Add/Modify to add this advanced configuration if it is not already present. You also need to add the server name to the ServerHost key in the registry as listed above.

For additional information on parallel installations of SQL Server, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q134937
   TITLE     : INF: Running SQL Versions 6.0 and 4.21 Side by Side

TEST TASK

It is often helpful to try a simple task that is separate and apart from any tasks you may have created. The following task may be useful in debugging issues with the SQL Executive service:

1. From the Task Scheduler, create a new task with the following attributes:

         Name    : Testdump
         Type    : TSQL
         Database: Pubs
         Command : dump database pubs to diskdump
         Schedule: On demand
   
   

2. After creating the task, attempt to run it. You may need to click Refresh Task once or twice to get the display to update properly, but if it was successful, you should see an indication telling you so. You may also want to try this command in ISQL/w, to verify that you have entered it correctly and that it works as expected. This command takes about four seconds to run (at most), so the response between start and completion (success or failure) should be seen within a few seconds. Again, you may need to click Refresh Task once or twice to properly update the display.

IF ONLY SOME OF THE TASKS AREN'T RUNNING

1. Make sure the tasks are enabled. You can verify this by double-clicking the task that is not running in the Manage Scheduled Tasks window and making sure there is a check in the Enabled check box.

2. Make sure that the next time the task is scheduled to run is correct. You can verify this by going to a query window and running the following commands:

         use msdb
         go
         select id, name, nextrundate, nextruntime from systasks
   

   Another useful query is to look at the length of time a task actually took to run. If the task took so long to run that it was still running at the next time the task was scheduled to run, you may observe unexpected behavior. You can look at the run time (in seconds) from the syshistory table, using the following statements:

         use msdb
         go
         select runtime from syshistory where taskid = <id of the task from
           systasks>
   
   

IF NONE OF THE TASKS ARE RUNNING

1. Make sure SQL Executive is using a Net-Library that allows a trusted connection. See the NETWORK PROTOCOL ISSUES section earlier in this article.

2. Make sure SQL Server is listening on named pipes and/or multi-protocol. You can check this by going to the SQL Setup icon. Click Continue twice, select Change Network Support, and click Continue. Place a check in named pipes and/or multi-protocol, and click OK. The changes will take effect once you stop and restart the mssqlserver service.

3. Make sure SQL Executive has the appropriate permissions in the registry. See the LOCAL GROUP MEMBERSHIP section earlier in this article.

4. If you are using named pipes as the default Net-Library (shown in the SQL Client Configuration utility), make sure the ServerHost key in following registry entry does not contain the server name (if it does, double-click the ServerHost key and delete the entry in the string field):

         HKEY_LOCAL_MACHINE on Local Machine
            \SOFTWARE
               \MICROSOFT
                  \MSSQLServer
                     \SQLExecutive
   
   

DEBUGGING SQL EXECUTIVE

I. Use the SQL Executive Error Log

In SQL Server 6.5, you can now view the SQL Executive Error Log. To set up an error log for SQL Executive, do the following:

   1. From the Server Manager window of SQL Enterprise Manager, select the
      server you want to set configuration parameters for.

   2. Right-click the server's SQL Executive icon, and click Configure.

   3. Specify the path and the name of the file that error information for
      SQL Executive will be written to. An error log file adds substantial
      process overhead. Use the error log file only when directed to do so
      by Microsoft Technical Support or when you are attempting to
      troubleshoot a specific problem with the SQL Executive service.

You can add a registry key to the registry to assist troubleshooting when all other methods fail. To use the registry to troubleshoot problems with the SQL Executive service, do the following:

   1. Run Regedt32.exe and go to the following location:

         HKEY_LOCAL_MACHINE on Local Machine
            \SOFTWARE
               \MICROSOFT
                  \MSSQLServer
                     \SQLExecutive

   2. Add a value named VerboseFile with a type of REG_SZ.

   3. In the String box, give a full path and file name for a file that
      will hold the results of the output from SQL Executive. For example,
      type the following:

         c:\Sql60\Log\Sqlexec.log

   4. Stop and restart the SQL Executive service.

   5. Test out the tasks or alerts that you want to run, and view the
      output in the file you added to the registry.

III. Get Verbose Output from SQL Executive

Additionally, the following method can also get verbose output from SQL Executive.

A quick way to see what is actually happening is to use the debugging switches '-c' and '-v'. To do this, you must first stop SQL Executive. Go to a command prompt and change to either the Sql60\Binn or the Mssql\Binn directory. Type the following command:

   SQLEXEC -c -v

   use msdb
   go
   select id from systasks where name = '<name of the task>'

Go back to the command prompt and see if the task ran without any errors. To shut down SQL Executive from the command prompt, press CTRL+C and answer yes to shutting it down.

Alternatively, you can redirect output to a file by using the following command:

   SQLEXEC -c -v > Sqlexec.out