Cannot Kill Process in SQL Server 6.5

[gimmegimme]

Database lock caused by a script that was run from the SA account has resulted in a process which can't be stopped using the KILL command. Reboot does nothing. MSDTC is in an indeterminate state. Any suggestions appreciated.

 

[tlbroadbent]

How can reboot do nothing? Are you saying you cannot reboot the server?

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[gimmegimme]

Sorry Terry, I should be more clear...I have rebooted the server several times, but the locks remain. SA owns the process that is causing the locks, but cannot use the KILL command to kill itself, so I need a way around this to terminate the process causing the locks.

 

[tlbroadbent]

Does the script run automatically when the server restarts? Can you prevent that from happening? Can you stop the MSDTC service?

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[gimmegimme]

No - the script was loaded into the SQL Query Tool from file and run from there. The MSDTC service is in an indeterminate state (currently not displaying on the services list) and can neither be stopped nor started through SQL Service Manager.

 

[tlbroadbent]

If you are rebooting the server where SQL Server runs then the locks must be cleared because SQL Server service stops. The same is true of MSDTC if it is running on the same server. It is just not possible for a rebooted server to retain locks in SQL Server.

Does the script access a remote (linked) server? Is the locking occurring on a remote server?

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[gimmegimme]

I believe the problem lies with the DTC service, but I can't get it out of the "indeterminate state" it's in. The locks which are displayed (through Server-->Current Activity-->Object Locks tab) are associated with tempdb and master database objects. I understand that they shouldn't be there, but they are there after each boot of the server. How can I kickstart MSDTC from an indeterminate state and get it started again??

 

[tlbroadbent]

You should be able to stop the MSDTC service from the service manager.

I still don't understand how the locks could exist after reboot unless the script you mentioned is fired off immediately upon restart or something else immediately causes locks when the server restarts. Are you sure it is the script executed from QA that is causing the locks?

Is the script run on a client or on the server? If on a client, have you rebooted the client?

Have you looked in the SQL Server error log for anything that looks out of the ordinary? Perhaps SQL Server is attempting to recover transactions. I haven't worked with SQL Server 6.5 for several years so I probably can't be of much help beyond what I've already posted.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[gimmegimme]

The script was run from a client using the SA account. I have rebooted the client but this did not appear to have any effect.

MSDTC shows no status light (red, green or yellow) in the Service Manager - it simply states that it is in an indeterminate state.

The error log shows it is attempting to initialize DTC, but it
"Failed to obtain TransactionDispenserInterface: XACT_E_TMNOTAVAILABLE"
Also looks like it might be trying to recover transactions, for example:
"Recovery dbid 1 ckpt (6982,0) oldest tran=(6983,31)"
"1 transactions rolled forward"
etc.

 

[tlbroadbent]

"Failed to obtain TransactionDispenserInterface: XACT_E_TMNOTAVAILABLE" means SQL Server could not initialize the DTC interface because "Microsoft Distributed Transaction Coordinator (MS DTC) service either is currently not running on the server or is currently unavailable."

http://msdn.microsoft.com/library/en-us/trblsql/tr_reslsyserr_2_4egj.asp

You should look in the server application event log for MSDTC messages. The MSDTC service should be restarted when the server is booted. It is obviously not being started in a usable condition, the event log should provide more information.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[gimmegimme]

I checked the event logs, but didn't find anything of use so
I did a reinstall of MSDTC:
- Disabled the IIS service
- Booted the server
- Reinstalled MSDTC using C:\Winnt\System32\Dtcsetup.exe
- Enabled the IIS service
- Booted the server

Everything looks normal again.
Thanks for all of your input and help Terry, I really appreciate it!

 

[tlbroadbent]

Glad you were able to find a solution.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm