Kill session

I've made a kill to a session that is stucked in SQL Server 2000 Enterprise +
SP3 with the status KILLED/ROLLBACK.
How can i force the kill ?
SQL> kill 567
SPID 567: transaction rollback in progress. Estimated rollback completion:
100%. Estimated time remaining: 0 seconds.
Thanks in advance
CC
CC
Take your coffee , it takes time
"CC" <CC@.discussions.microsoft.com> wrote in message
news:CB29FBB8-0772-4315-9DAB-3D898D39C354@.microsoft.com...
> I've made a kill to a session that is stucked in SQL Server 2000
> Enterprise +
> SP3 with the status KILLED/ROLLBACK.
> How can i force the kill ?
> SQL> kill 567
> SPID 567: transaction rollback in progress. Estimated rollback completion:
> 100%. Estimated time remaining: 0 seconds.
> Thanks in advance
> CC
|||Thanks Uri but:
-Estimated time remaining: 0 seconds
-Estimated rollback completion:100%
-Spid last_batch in sysprocesses 2005-09-12 00:52:15.380 (7 Days)
This session is from a backup:
sp_lock:
5671400DB[BULK-OP-LOG] XGRANT
5671400DB[BULK-OP-DB] XGRANT
5671400DB SGRANT
My full database backup don't start because of this session and i do not
want to restart SQL Server service on a production database because of a
zombie spid.
I think i will need more than a coffe : ) Can you help ?
The spid was killed but does not release locks held.
CC
"Uri Dimant" wrote:

> CC
> Take your coffee , it takes time
>
> "CC" <CC@.discussions.microsoft.com> wrote in message
> news:CB29FBB8-0772-4315-9DAB-3D898D39C354@.microsoft.com...
>
>
|||CC
Well, one option is to restart MS Service , another is if the first one does
not help ,in Task Manager to kill the process.
"CC" <CC@.discussions.microsoft.com> wrote in message
news:131B1221-349A-4260-93F9-2FF6BAF63BBD@.microsoft.com...[vbcol=seagreen]
> Thanks Uri but:
> -Estimated time remaining: 0 seconds
> -Estimated rollback completion:100%
> -Spid last_batch in sysprocesses 2005-09-12 00:52:15.380 (7 Days)
> This session is from a backup:
> sp_lock:
> 567 14 0 0 DB [BULK-OP-LOG] X GRANT
> 567 14 0 0 DB [BULK-OP-DB] X GRANT
> 567 14 0 0 DB S GRANT
> My full database backup don't start because of this session and i do not
> want to restart SQL Server service on a production database because of a
> zombie spid.
> I think i will need more than a coffe : ) Can you help ?
> The spid was killed but does not release locks held.
> CC
> "Uri Dimant" wrote:
|||So is BULK-OP-DB and BULK-OP-LOG used for any file growth operations? I see
it holding locks on backups?
"Uri Dimant" wrote:

> CC
> Well, one option is to restart MS Service , another is if the first one does
> not help ,in Task Manager to kill the process.
>
>
> "CC" <CC@.discussions.microsoft.com> wrote in message
> news:131B1221-349A-4260-93F9-2FF6BAF63BBD@.microsoft.com...
>
>

Kill Process

Hello again,
I have one process that appear runing (OMNIBACK), i've yet
kill the process (The command(s) completed
successfully)... but the process still running.
Can i do a SUPERKILL ;o)
i dont know what to do.
Best Regards
> I have one process that appear runing (OMNIBACK), i've yet
> kill the process (The command(s) completed
> successfully)... but the process still running.
> Can i do a SUPERKILL ;o)
They are likely in the middle of a rollback. If you say KILL <spid> again
you should be able to see what percentage of rollback has completed.
To be safe, I would just let the process finish what it's doing to clean
itself up. Many people are tempted to just power down the box or force the
SQL Server service to restart, but I promise you this isn't the way to go...
can lead to suspect database, or a database that has to start the same
recovery/rollback process all over again before you will be able to use it.
While you're waiting, I would find out what they did, why you had to kill
the user, and take steps to prevent this from happening again.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
|||KILL is limited in its power.
The closest thing to SUPERKILL is the hefty: ALTER DATABASE mydb SET
SINGLE_USER. Then you can make it multi-user again. This will chase
_everybody_ (not one user) out of a database.
Russell Fields
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:ec0601c43cfd$7e94f420$a301280a@.phx.gbl...
> Hello again,
> I have one process that appear runing (OMNIBACK), i've yet
> kill the process (The command(s) completed
> successfully)... but the process still running.
> Can i do a SUPERKILL ;o)
> i dont know what to do.
> Best Regards
>
|||you can add the status_only clause to Kill.
One question - if it's rolled back 100% but still not ending, is it running something outside of SQL? I.e. something like sp_OaCreate to run a COM component or xp_cmdshell or xp_sendmail?
If that's the case then SQL can lose sight of whether the process is active or not - normally because the dll (or whatever) hasn't reported status back.
Alicia
http://www.sqlporn.co.uk
|||Maybe it's executing a WSH script, and the server is waiting for someone to
walk by and click "OK" on a MsgBox. :-)
"Alicia" <anonymous@.discussions.microsoft.com> wrote in message
news:ABA285E4-A89A-45BE-BBB0-B2A6DCCEC86F@.microsoft.com...
> you can add the status_only clause to Kill.
> One question - if it's rolled back 100% but still not ending, is it
running something outside of SQL? I.e. something like sp_OaCreate to run a
COM component or xp_cmdshell or xp_sendmail?
> If that's the case then SQL can lose sight of whether the process is
active or not - normally because the dll (or whatever) hasn't reported
status back.
> Alicia
> http://www.sqlporn.co.uk
|||Yeah - I've seen that in a job running DTS ActiveX script with debug code in
it...
xp_cmdshell 'notepad' will do it as well :-)
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:OS8lU6QPEHA.2636@.TK2MSFTNGP10.phx.gbl...
> Maybe it's executing a WSH script, and the server is waiting for someone
to
> walk by and click "OK" on a MsgBox. :-)
>
>
> "Alicia" <anonymous@.discussions.microsoft.com> wrote in message
> news:ABA285E4-A89A-45BE-BBB0-B2A6DCCEC86F@.microsoft.com...
> running something outside of SQL? I.e. something like sp_OaCreate to run a
> COM component or xp_cmdshell or xp_sendmail?
> active or not - normally because the dll (or whatever) hasn't reported
> status back.
>

Kill Old Sessions

I've got a few databases which users access using Terminal Server. I
noticed today I had several (20+) sessions which had a Last Batch date
which were days even weeks old.
I want to kill these old sessions if the Last Batch date is greater
than 5 hours.
I also noticed there are several background sessions being run by the
sa account on master db and they are several days old. I don't believe
I should kill these sessions.
Does anyone have a script they currently use to manage these old
sessions?
Izzy
I forgot to list, I'm using SQL Server 2000.
Thanks,
Izzy wrote:
> I've got a few databases which users access using Terminal Server. I
> noticed today I had several (20+) sessions which had a Last Batch date
> which were days even weeks old.
> I want to kill these old sessions if the Last Batch date is greater
> than 5 hours.
> I also noticed there are several background sessions being run by the
> sa account on master db and they are several days old. I don't believe
> I should kill these sessions.
> Does anyone have a script they currently use to manage these old
> sessions?
> Izzy
|||It is just a matter of writing a cursor on the sysprocesses table. You can use
http://www.dbmaint.com/download/util...kill_users.sql as a starter for your script.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Izzy" <israel.richner@.gmail.com> wrote in message
news:1160062917.135174.41640@.h48g2000cwc.googlegro ups.com...
>I forgot to list, I'm using SQL Server 2000.
> Thanks,
>
> Izzy wrote:
>
|||Execellent!
Thanks a bunch.
Izzy
Tibor Karaszi wrote:[vbcol=seagreen]
> It is just a matter of writing a cursor on the sysprocesses table. You can use
> http://www.dbmaint.com/download/util...kill_users.sql as a starter for your script.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Izzy" <israel.richner@.gmail.com> wrote in message
> news:1160062917.135174.41640@.h48g2000cwc.googlegro ups.com...
|||It seems to me that you are treating the symptop, not the problem.
The symptom is the old sessions.
The problem is that people do not exit Terminal Server correctly. Can you
encourage users to log out of the Terminal Server correctly? Can you
remotely log the users out (and end their database connection in the
process)?
Keith Kratochvil
"Izzy" <israel.richner@.gmail.com> wrote in message
news:1160061806.706762.53740@.m7g2000cwm.googlegrou ps.com...
> I've got a few databases which users access using Terminal Server. I
> noticed today I had several (20+) sessions which had a Last Batch date
> which were days even weeks old.
> I want to kill these old sessions if the Last Batch date is greater
> than 5 hours.
> I also noticed there are several background sessions being run by the
> sa account on master db and they are several days old. I don't believe
> I should kill these sessions.
> Does anyone have a script they currently use to manage these old
> sessions?
> Izzy
>
|||That is exactly the problem, most users are set up to be logged out of
terminal server at midnight if they are not already logged out.
BUT, I have users who work in our shop on 3rd shift who use the same
account as users on first shift.
I've explained too them they need to log out correctly, but of course
users do whatever they want anyway, and just give you lip service while
your in front of them.
Question:
In the example you sent, your query does not eliminate some sessions
from being killed. For instance, I have 4 which have this listed in the
"cmd" line:
LAZY WRITER
LOG WRITER
LOCK MONITOR
CHECKPOINT SLEEP
Is there going to be any negative or unexpected behavior if these get
killed?
Is there something I should query on to eliminate system processes?
Izzy
Keith Kratochvil wrote:[vbcol=seagreen]
> It seems to me that you are treating the symptop, not the problem.
> The symptom is the old sessions.
> The problem is that people do not exit Terminal Server correctly. Can you
> encourage users to log out of the Terminal Server correctly? Can you
> remotely log the users out (and end their database connection in the
> process)?
> --
> Keith Kratochvil
>
> "Izzy" <israel.richner@.gmail.com> wrote in message
> news:1160061806.706762.53740@.m7g2000cwm.googlegrou ps.com...
|||> In the example you sent, your query does not eliminate some sessions
> from being killed. For instance, I have 4 which have this listed in the
> "cmd" line:
> LAZY WRITER
> LOG WRITER
> LOCK MONITOR
> CHECKPOINT SLEEP
> Is there going to be any negative or unexpected behavior if these get
> killed?
These are system connections, and I'm pretty certain they can't be killed even if you try to (else
MS wouldn't done a good job protecting the system processes). You should add a filter to the SELECT
statement, like spid > 50.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Izzy" <israel.richner@.gmail.com> wrote in message
news:1160066738.477635.116660@.h48g2000cwc.googlegr oups.com...
> That is exactly the problem, most users are set up to be logged out of
> terminal server at midnight if they are not already logged out.
> BUT, I have users who work in our shop on 3rd shift who use the same
> account as users on first shift.
> I've explained too them they need to log out correctly, but of course
> users do whatever they want anyway, and just give you lip service while
> your in front of them.
> Question:
> In the example you sent, your query does not eliminate some sessions
> from being killed. For instance, I have 4 which have this listed in the
> "cmd" line:
> LAZY WRITER
> LOG WRITER
> LOCK MONITOR
> CHECKPOINT SLEEP
> Is there going to be any negative or unexpected behavior if these get
> killed?
> Is there something I should query on to eliminate system processes?
> Izzy
>
> Keith Kratochvil wrote:
>
|||You've been very helpful Tibor, many thanks!
Izzy
Tibor Karaszi wrote:[vbcol=seagreen]
> These are system connections, and I'm pretty certain they can't be killed even if you try to (else
> MS wouldn't done a good job protecting the system processes). You should add a filter to the SELECT
> statement, like spid > 50.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Izzy" <israel.richner@.gmail.com> wrote in message
> news:1160066738.477635.116660@.h48g2000cwc.googlegr oups.com...

Kill command don't work

I've this problem:
A process (SPID 62) with some locks on some tables. The application which
had generated the query are not running, is closed! In SQL server I can see
the SPI 62 that are blocking other users.
I try to kill the process (KILL 62) but nothing! The SPID 62 not die!
I need to restare sql server to solve my problem.
The query that SPID62 are running is a normal INSERT INTO without any
problems or other...
How may I do to obtain more information around the fact that the SQL KILL
command not work?
I've already tried with KILL 62 WITH: Kill 62 with status only and I obtain
: "Extimated... rollbak 0%,... 0 minutes"
But the process rest in rollback and not stop.
and why SQL server if the client go off not stop this process itself?
thanks in advance.
Teo
I has this case at a customer site just yesterday. It turned out that the table they inserted into
had a trigger which executed an extended stored procedure. If any external (to SQL Server) code
becomes hung, you cannot kill that SPID.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Teo(I)" <TeoI@.discussions.microsoft.com> wrote in message
news:73DE3473-DFAC-4C77-A645-057871F3DDEC@.microsoft.com...
> I've this problem:
> A process (SPID 62) with some locks on some tables. The application which
> had generated the query are not running, is closed! In SQL server I can see
> the SPI 62 that are blocking other users.
> I try to kill the process (KILL 62) but nothing! The SPID 62 not die!
> I need to restare sql server to solve my problem.
> The query that SPID62 are running is a normal INSERT INTO without any
> problems or other...
> How may I do to obtain more information around the fact that the SQL KILL
> command not work?
> I've already tried with KILL 62 WITH: Kill 62 with status only and I obtain
> : "Extimated... rollbak 0%,... 0 minutes"
> But the process rest in rollback and not stop.
> and why SQL server if the client go off not stop this process itself?
> thanks in advance.
> Teo
|||DBCC traceon 3604 on SPID 62:
Process id 62 killed by hostname SERVER01, host process ID 6816...
but this isn't true! the spid is alive..!!!
If I retry i obtain:
Process id 62 killed by hostname SERVER01, host process ID 6816...
I don't understand!
|||well... I've a trigger too... But my trigger do another insert in a second
table (backup table) on a different db in the same istance. In this case a
rollback or kill must work.