Microsoft KB Archive/155283

= INF: Troubleshooting SQL Executive and Task Scheduling =

Article ID: 155283

Article Last Modified on 9/1/2006

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q155283



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.



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, LOCAL commands to alter the SQL Server name.

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   :  Confirm Pass:

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).

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.

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.</li>  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>

</li></ol>

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.</li> 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.</li> Make sure SQL Executive has the appropriate permissions in the registry. See the LOCAL GROUP MEMBERSHIP section earlier in this article.</li>  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):

<pre class="fixed_text">     HKEY_LOCAL_MACHINE on Local Machine \SOFTWARE \MICROSOFT \MSSQLServer \SQLExecutive

</li></ol>

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.

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:

<pre class="fixed_text">        HKEY_LOCAL_MACHINE on Local Machine \SOFTWARE \MICROSOFT \MSSQLServer \SQLExecutive

</li> Add a value named VerboseFile with a type of REG_SZ.</li>  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:

<pre class="fixed_text">        c:\Sql60\Log\Sqlexec.log </li> Stop and restart the SQL Executive service.</li> Test out the tasks or alerts that you want to run, and view the output in the file you added to the registry.</li></ol>

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 = ' '

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 KB155283

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.