Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Location
    Mars
    Posts
    115

    Unanswered: Unable to Kill SPID

    Hi all,
    Greetings,

    I have a SPID 10 which running a query which is below is not getting killed
    and this SPID is blocking other SPID's. Is there any way to kill it pls help me

    "select A.*
    from ARO A where A.InvoiceNumber in
    (select B.InvoiceNumber from AR1 B
    where B.Division='20'
    /*UNION
    Select C.SOApplytoInvoiceNumber from AR1 C
    where C.Division='20'*/
    UNION
    Selec"

    Waiting for reply
    TIA
    ADIL

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You do know you're trying to kill a system spawned spid....

    Anything under 50 is created by sql server...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2003
    Location
    Mars
    Posts
    115
    Hi Thanks for your reply.

    Please tell me how do solve it and wht might be the reason. becas of this. my other SPID are getting blocked which are running Jobs.

    Wating for reply
    Regards
    Adil

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You sure?

    Do sp_who and post the output
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    To kill any spid numbered 50 or below, stop the SQL Server Service (a sysadmin can use the SHUTDOWN command).

    Note that this will also stop all of the spids, because it stops the server itself, but that is much safer than trying to kill one of the system threads, especially since spid 10 is what does the CHECKPOINT operations!

    -PatP

  6. #6
    Join Date
    Nov 2003
    Location
    Mars
    Posts
    115
    Hi I am on Sql Server 7
    below is output of sp_who2
    Please help me


    SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID
    ----- ------------------------------ ---------------------------------- ------------------------------ ----- -------------- ---------------- ------- ------- -------------- ------------------------------ -----
    1 sleeping sa . . master SIGNAL HANDLER 0 0 05/04 20:48:37 1
    2 BACKGROUND sa . . master LOCK MONITOR 0 0 05/04 20:48:37 2
    3 BACKGROUND sa . . master LAZY WRITER 0 0 05/04 20:48:37 3
    4 sleeping sa . . master LOG WRITER 0 0 05/04 20:48:37 4
    5 sleeping sa . . master CHECKPOINT SLEEP 0 922 05/04 20:48:37 5
    6 BACKGROUND sa . . master AWAITING COMMAND 0 4611 05/04 20:48:37 6
    7 sleeping USAWTI\vicksvapor USA03 . msdb AWAITING COMMAND 0 34 05/04 20:48:39 SQLAgent - Generic Refresher 7
    8 sleeping USAWTI\vicksvapor USA03 . msdb AWAITING COMMAND 0 3794348 05/12 09:41:35 SQLAgent - Alert Engine 8
    9 RUNNABLE sa USAWSTAGE . master SELECT INTO 0 9 05/12 09:41:26 MS SQL Query Analyzer 9
    10 sleeping sa USA03 . MAS_USA SELECT 3812 68 05/11 04:00:01 DTS Wizard 10
    11 sleeping sa USAWSTAGE . master AWAITING COMMAND 0 9920 05/12 09:41:11 MS SQLEM 11
    12 sleeping USAWTI\vicksvapor . . MAS_USA AWAITING COMMAND 0 0 05/12 09:41:28 12
    13 sleeping USAWTI\vicksvapor . . MAS_SYSTEM AWAITING COMMAND 0 34 05/12 09:41:29 13
    14 sleeping sa USAWDB1 . MAS_INBOUND AWAITING COMMAND 0 52 05/12 09:36:32 USAWDB1-USAW_B2C_INBOUND-USA03 14
    15 sleeping USAWTI\vicksvapor . . MAS_USA AWAITING COMMAND 0 0 05/12 09:33:50 15
    16 sleeping USAWTI\vicksvapor . . MAS_SYSTEM AWAITING COMMAND 0 243 05/12 09:41:28 16
    17 sleeping USAWTI\vicksvapor . . MAS_USA AWAITING COMMAND 0 138 05/12 09:34:41 17
    18 sleeping USAWTI\vicksvapor . . MAS_USA AWAITING COMMAND 0 0 05/12 09:34:46 18
    19 sleeping USAWTI\vicksvapor . . MAS_SYSTEM AWAITING COMMAND 0 44 05/12 09:34:58 19
    20 sleeping USAWTI\vicksvapor . 28 MAS_USA INSERT 0 33 05/12 09:34:59 20
    21 sleeping mas_reports CS-C6FA1 . MAS_USA AWAITING COMMAND 1610 0 05/12 09:40:01 Microsoft Office XP 21
    22 RUNNABLE USAWTI\vicksvapor USA03 . master EXECUTE 0 0 05/11 21:40:00 SQLAgent - TSQL JobStep (Job 0 22
    23 sleeping USAWTI\vicksvapor USA03 20 MAS_USA DBCC 0 82898 05/12 03:57:08 SQLDMO_1 23
    24 sleeping USAWTI\vicksvapor . . MAS_USA AWAITING COMMAND 0 0 05/12 09:41:28 24
    26 sleeping USAWTI\vicksvapor . . MAS_USA AWAITING COMMAND 0 0 05/12 08:30:32 26
    27 sleeping USAWTI\vicksvapor . . MAS_SYSTEM AWAITING COMMAND 0 443 05/12 09:40:47 27
    28 sleeping USAWTI\vicksvapor . 10 MAS_USA INSERT 0 217 05/12 09:40:47 28
    38 sleeping sa USATESTSVR . MAS_USA AWAITING COMMAND 10893 17616 05/07 07:49:24 MS SQL Query Analyzer 38
    44 sleeping sa USATESTSVR . MAS_USA AWAITING COMMAND 0 96 05/06 10:02:56 MS SQL Query Analyzer 44
    61 sleeping sa USATESTSVR . master AWAITING COMMAND 0 28 05/12 08:05:37 MS SQLEM 61
    70 sleeping sa USATESTSVR . MAS_USA AWAITING COMMAND 0 39 05/12 08:10:08 MS SQL Query Analyzer 70
    71 sleeping sa 8Y4WF11 . eBridge41_Test AWAITING COMMAND 0 917 05/12 00:25:10 eBridge 71
    88 sleeping sa USATESTSVR . MAS_USA AWAITING COMMAND 4686 1 05/07 08:36:11 MS SQL Query Analyzer 88
    90 sleeping sa 8Y4WF11 . eBridge41_Test AWAITING COMMAND 0 82 05/11 11:47:49 eBridge 90
    143 sleeping USAWTI\vicksvapor . . MAS_USA AWAITING COMMAND 0 0 05/12 08:29:50 143
    144 sleeping USAWTI\vicksvapor . . MAS_SYSTEM AWAITING COMMAND 0 286 05/12 09:03:06 144
    145 sleeping USAWTI\vicksvapor . . MAS_USA AWAITING COMMAND 0 144 05/12 09:01:30 145
    146 sleeping sa USATESTSVR . MAS_USA AWAITING COMMAND 46529 38799 05/10 11:02:09 MS SQL Query Analyzer 146
    159 sleeping sa 8Y4WF11 . eBridge41_Test AWAITING COMMAND 0 1 05/11 11:42:21 eBridge 159
    168 sleeping MAS_REPORTS CAFE2001 . MAS_USA AWAITING COMMAND 0 3090 05/11 16:05:48 usa1 168
    172 sleeping sa USATESTSVR . MAS_USA AWAITING COMMAND 0 7689 05/12 08:19:44 MS SQL Query Analyzer 172
    181 sleeping MAS_REPORTS CAFE2001 . MAS_USA AWAITING COMMAND 0 0 01/01 00:00:00 FSMEngine 181
    185 sleeping MAS_REPORTS CAFE2001 . MAS_INBOUND AWAITING COMMAND 0 908 05/11 16:05:50 FSMEngine 185
    214 sleeping sa . . eBridge41_Test AWAITING COMMAND 0 1 05/11 11:47:50 214

    (44 row(s) affected)

  7. #7
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    aadil ... you may not have any option other than to stop and restart SQL Server. Some processes do not check the semaphore that the kill command sets, or the DTS process running under spid 10 may be in a loop. (a vague memory of a msoft article I read a couple of years back).

    I do not see spid 10 in a rollback state from the sp_who2 output you provided, so be aware that when you restart SQL Server, the database that spid 10 was in may be in recovery for quite some time, depending on what was actually occuring.

  8. #8
    Join Date
    Oct 2003
    Posts
    706
    I'd say that you have a very expensive and long-running query, not a "hung thread." Exercising godly powers to terminate its life prematurely is almost certain to spell grief. This problem needs to be cured at its true source.

    Check out the query execution plan of that query. I bet it's horrible, executing thousands upon thousands of subqueries. The query could probably use INNER JOIN quite nicely.

    Furthermore, the UNION of such queries might produce "all the results you want in one neat step," but a series of queries run separately, appending records to a temporary table, might do as well or better. Plus... I betcha a GROUP BY query would do the job for you.
    Last edited by sundialsvcs; 05-12-04 at 23:37.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •