Post reply

KILL SPID not working while running stored procedure

  • November 28, 2006 at 4:14 pm

    #175477

    Hi,

    I have a job that runs a stored procedure that accesses a linked server.  We changed the linked server and ran the job and it usually runs for 2 minutes, but it ran for approx 30mins, so I clicked stop job.  The job is showing as cancelled, but the spid is still running with over 1600 locks. Kill spid returns:

    SPID 74: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 3 seconds.

    It has been like this for 26 hours. 

    Both servers are production data and I don't want to lose anything.

    DBCC OPENTRAN returns:

    Oldest active transaction:

        SPID (server process ID) : 74

        UID (user ID) : -1

        Name          : trnSAFRAFinanceOneTransfer

        LSN           : (8303:84707:2)

        Start time    : Nov 28 2006  8:45:49:583AM

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Can you please tell me how I could possibly stop this spid and hopefully not loose any data?

    Thanks.

  • November 28, 2006 at 11:16 pm

    #674582

    Did you check blocking? Run sp_who to check it.

    Pankaj Khanna
    Database Administrator - SQL Server 2000

    Keep hope to keep you intact...

  • November 29, 2006 at 8:19 am

    #674675

    Unfortunately, I think the only solution is to restart the SQL Service. I'm struggling with this problem as well. The following link shows a disscussion about this. Apprently, it's a problem with SS2k even through sp4 and the latest build (2187).

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=622876&SiteID=1&PageID=0

    If anyone knows how to solve this problem with linked servers, please help. Thanks.

     


    smv929

  • November 29, 2006 at 3:05 pm

    #674814

    Thanks for your help.  I ended up only having to restart the DTC service and this SPID was killed.

    It seems to be a DTC issue on the new linked server.  It is running Windows 2003 service pack 1 which apparently introduces new security features. 

    I'm able to get an error message from my test machine:

    OLE DB provider "SQLNCLI" for linked server "<serverName>" returned message "The transaction manager has disabled its support for remote/network transactions.".

    The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "<serverName>" was unable to begin a distributed transaction.

    If you have any suggestions....

  • November 30, 2006 at 9:40 am

    #674981

    I believe this can be fixed by going into control panel =>add/remove programs => Windows components=>Application server and adding the "enable network DTC'

    Thanks,

    Rich

  • November 30, 2006 at 9:45 am

    #674984

    Do you do this on the linked server or the sql server from which you are running the query?


    smv929

  • November 30, 2006 at 9:49 am

    #674987

    the linked server

  • November 30, 2006 at 3:22 pm

    #675047

    Thanks,

    The linked server already had it enabled.  I've got a scheduled reboot on Sunday, that will hopefully apply the security settings ive changed.

  • December 1, 2006 at 6:45 am

    #675120

    Well my dts jobs all of the sudden are running fine since the source server (a Progress database server) was rebooted a couple of days ago. It hadn't been rebooted for several weeks. It's running Win2003 but does not have sp1! A different team manages that server. Whatever happened straightened it out for now. It will certainly happen again as the server runs without rebooting for a while, so I'll watch and apply the suggestions from this post because I do think it's related to MSDTC on that server. Thanks!


    smv929

  • December 2, 2006 at 6:31 pm

    #675356

    When you kill the linked server query somet times it will say 100% complete and rollbck 0% ... which means

    You killed the query on source server but it is still active on destination server. In this case you should check the destination server and kille the connection there OR you should stop and start MSDTC to resolve the issue.

    If you stop MSDTC you are effecting all linked server calls, so it is better to kill the query at destination server.

     

     

    MohammedU
    Microsoft SQL Server MVP

  • December 4, 2006 at 7:55 pm

    #675699

    Thanks - now I understand what would have been happening.

    I have finally resolved the problem.  Rebooting the server fixed the issue.  I still get the error however from my xp proffessional service pack 2 machine(which I was using for testing - bad idea!)

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply