INF: Troubleshooting SQL Executive and Task Scheduling

ID: Q155283

The information in this article applies to:

Microsoft SQL Server versions 6.0, 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:

Q140683 : INF: Run Task May Cause 1722 Error

To find out what the SQL Server name is, either view the server name in the SQL Server error log (located in the Sql\Log directory) or run the following command:

SELECT @@SERVERNAME

The SQL Server name must also conform to the rules for identifiers.

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

A frequent cause of failure in this situation is that the account that the SQL Executive is using has a password that has either expired or been changed. If this is the case, enter the correct password information for SQL Executive in the Services Control Panel, and then stop and restart the SQL Executive service.

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

If you are still seeing errors like 109 or 1069 when you try to start the SQL Executive service, you can try rebuilding the registry for SQL Server. Make sure that you are logged on to the computer running Windows NT Server as the local administrator (or someone with local administrative privileges), and follow the steps listed in the following article in the Microsoft Knowledge Base:

Q119401 : 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

For example, if you have set up SQL Server 6.0 to listen on \\.\pipe\sql60\query, enter that value for the ServerHost value. Stop and restart SQL Server for the changes (particularly the network protocol) to take effect.

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:

Q134937 : 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:

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

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

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.

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

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

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.

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

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:

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

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

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.

II. Modify the Registry

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:

Run Regedt32.exe and go to the following location:


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

Add a value named VerboseFile with a type of REG_SZ.

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
  
```

Stop and restart the SQL Executive service.

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

These steps should solve the vast majority of problems encountered with SQL Executive not running. If, after checking all of the above, the SQL Executive service still does not start, please contact your primary support provider for more assistance.
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

This command is case-sensitive, and you should enter the switches in lowercase only. When SQL Executive is started this way, you will see a great deal of information, including any failure or error messages the service encountered when starting. For additional output, you can try to run a task while SQL Executive is running in debug mode. You need to know the ID of the task that isn't running. To find the task ID, go to a query window and run the following query:


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

Next, go to a query window and run sp_schedulersignal 'O', <ID of task>. NOTE: The 'O' is the capital letter O.

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

When the service is started in this way, nothing will be echoed to the screen. Wait for approximately 1 to 2 minutes for the service to start, press CTRL+C and then type 'y' to stop the service. You will not see your commands echoed to the screen. Once the service is stopped, you can edit the Sqlexec.out file and see the output generated by the debugging switches.

Additional query words: security scheduled tasks

Keywords : kbnetwork SSrvLAN SSrvNet_Lib
Version : 6.0 6.5
Platform : WINDOWS
Issue type :