Killing Sleeping Processes

Is there a way to Kill all 'sleeping' processes at once ?
Thanks.Hi,
Do not take a risk in killing all the sleeping process . But the way is,
select 'kill '+convert(char,spid) +char(10)+'go' from master..sysprocess
where status like 'sleep%'
Execute the output of the above script in query analyzer. This will kill all
the sleeping process
Thanks
Hari
MCDBA
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:5e2a01c3e5b3$358bb1c0$a401280a@.phx.gbl...

quote:

---

> Is there a way to Kill all 'sleeping' processes at once ?
> Thanks.

---

|||Thanks.......

quote:

---

>--Original Message--
>Hi,
>Do not take a risk in killing all the sleeping process .

---

But the way is,

quote:

---

>select 'kill '+convert(char,spid) +char(10)+'go' from

---

master..sysprocess

quote:

---

>where status like 'sleep%'
>Execute the output of the above script in query analyzer.

---

This will kill all

quote:

---

>the sleeping process
>Thanks
>Hari
>MCDBA
>
>"Brian" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:5e2a01c3e5b3$358bb1c0$a401280a@.phx.gbl...
once ?[QUOTE]
>
>.
>

---

sql

Killing Sleeping Processes

Is there a way to Kill all 'sleeping' processes at once ?
Thanks.Hi,
Do not take a risk in killing all the sleeping process . But the way is,
select 'kill '+convert(char,spid) +char(10)+'go' from master..sysprocess
where status like 'sleep%'
Execute the output of the above script in query analyzer. This will kill all
the sleeping process
Thanks
Hari
MCDBA
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:5e2a01c3e5b3$358bb1c0$a401280a@.phx.gbl...
> Is there a way to Kill all 'sleeping' processes at once ?
> Thanks.|||Thanks.......
>--Original Message--
>Hi,
>Do not take a risk in killing all the sleeping process .
But the way is,
>select 'kill '+convert(char,spid) +char(10)+'go' from
master..sysprocess
>where status like 'sleep%'
>Execute the output of the above script in query analyzer.
This will kill all
>the sleeping process
>Thanks
>Hari
>MCDBA
>
>"Brian" <anonymous@.discussions.microsoft.com> wrote in
message
>news:5e2a01c3e5b3$358bb1c0$a401280a@.phx.gbl...
>> Is there a way to Kill all 'sleeping' processes at
once ?
>> Thanks.
>
>.
>

Killing all sleeping processes

Hi All,
When I run sp_who2, I see there are many sleeping processes. Instead of
killing one by one, I was wondering if there is any way to kill all
sleeping processes programmatically (MS SQL Server 2000).
Thanks a million in advance.
Best regards,
mamunHi,
It is possible to do. But Killing the sleeping user process may not be a
good idea. A user processes
may be running for 40 minutes and when you check that process may be
sleeping and you code will kill that
process.
I recommend you to, not to automate this process in production server.
Script:-
use master
go
declare @.x varchar(1000)
set @.x=''
select @.x = @.x + 'Kill' + convert(varchar(5), spid)
from master.dbo.sysprocesses where status='Sleeping'
exec (@.x)
Schedule the script thru SQL Agent jobs. THe above script can take care of 1
user kill . If you have mutiple user, you have to slightly modify the script
Thanks
Hari
SQL Server MVP
"microsoft.public.dotnet.languages.vb" <mamun_ah@.hotmail.com> wrote in
message news:1116964671.573914.189310@.g49g2000cwa.googlegroups.com...
> Hi All,
>
> When I run sp_who2, I see there are many sleeping processes. Instead of
> killing one by one, I was wondering if there is any way to kill all
> sleeping processes programmatically (MS SQL Server 2000).
> Thanks a million in advance.
> Best regards,
> mamun
>|||If the unneeded idle connections are created by applications, then it's an
issue for the developer to resolve by managing the connection pool. If the
connections are created by people logging into Query Analyzer or Enterprise
Manager, then it's an issue for the DBA to resolve by perhaps restricting
logins and permissions.
"microsoft.public.dotnet.languages.vb" <mamun_ah@.hotmail.com> wrote in
message news:1116964671.573914.189310@.g49g2000cwa.googlegroups.com...
> Hi All,
>
> When I run sp_who2, I see there are many sleeping processes. Instead of
> killing one by one, I was wondering if there is any way to kill all
> sleeping processes programmatically (MS SQL Server 2000).
> Thanks a million in advance.
> Best regards,
> mamun
>

killing a process with a variable

I wish to kill all the processes for a given database.
have written the script that gives me all the spids for the database,
however I get an error when trying to execute;
KILL @.spid;
(Incorrect syntax near @.spid)
where @.spid is declared as a smallint.
Can anybody help?
TIA
Hi
You can't pass a variable. You need to create dynamic sql to execute that:
DECLARE @.exstring VARCHAR(20)
SELECT @.exstring = 'KILL ' + @.spid
executesql @.exstring
Regards
Mike
"Dan" wrote:

> I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>
>
|||Hi Dan - I think this works.
DECLARE @.i INT
DECLARE @.strSQL NVARCHAR(255)
SET @.i = 73
SET @.strSQL = 'KILL ' + CAST(@.i AS CHAR (2))
--PRINT @.strSQL
EXEC sp_executesql @.strSQL
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:eg10JwhpEHA.4008@.TK2MSFTNGP14.phx.gbl...
> I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>
|||Hi,
If your Sql server version is 2000 then go for ALTER Database command rather
than KILL command.
ALTER Database <dbname> set single_user with rollback immediate
The above command will remove all the connected users to that database
immediately. After the activity u can change the db the multiuser.
ALTER Database <dbname> set multi_user
Thanks
Hari
MCDBA
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:eg10JwhpEHA.4008@.TK2MSFTNGP14.phx.gbl...
>I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>
|||Hi Dan,
I wrote the following script and tested it:
Use master
go
SET NOCOUNT ON
DECLARE @.strSQL varchar(255)
PRINT 'Killing Users'
PRINT '--'
CREATE table #tmpUsers(
spid int,
eid int,
status varchar(30),
loginname varchar(50),
hostname varchar(50),
blk int,
dbname varchar(50),
cmd varchar(30))
INSERT INTO #tmpUsers EXEC SP_WHO
DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = 'YOUR DATABASE NAME
HERE'
DECLARE @.spid varchar(10)
DECLARE @.dbname2 varchar(40)
OPEN LoginCursor
FETCH NEXT FROM LoginCursor INTO @.spid, @.dbname2
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT 'Killing ' + @.spid
SET @.strSQL = 'KILL ' + @.spid
EXEC (@.strSQL)
END
FETCH NEXT FROM LoginCursor INTO @.spid, @.dbname2
END
CLOSE LoginCursor
DEALLOCATE LoginCursor
DROP table #tmpUsers
PRINT 'Done'
go
Just replace 'YOUR DATABASE NAME HERE' with your database name.
Sasan
"Dan" wrote:

> I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>
>

killing a process with a variable

I wish to kill all the processes for a given database.
have written the script that gives me all the spids for the database,
however I get an error when trying to execute;
KILL @.spid;
(Incorrect syntax near @.spid)
where @.spid is declared as a smallint.
Can anybody help?
TIAHi
You can't pass a variable. You need to create dynamic sql to execute that:
DECLARE @.exstring VARCHAR(20)
SELECT @.exstring = 'KILL ' + @.spid
executesql @.exstring
Regards
Mike
"Dan" wrote:
> I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>
>|||Hi Dan - I think this works.
DECLARE @.i INT
DECLARE @.strSQL NVARCHAR(255)
SET @.i = 73
SET @.strSQL = 'KILL ' + CAST(@.i AS CHAR (2))
--PRINT @.strSQL
EXEC sp_executesql @.strSQL
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:eg10JwhpEHA.4008@.TK2MSFTNGP14.phx.gbl...
> I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>|||Hi,
If your Sql server version is 2000 then go for ALTER Database command rather
than KILL command.
ALTER Database <dbname> set single_user with rollback immediate
The above command will remove all the connected users to that database
immediately. After the activity u can change the db the multiuser.
ALTER Database <dbname> set multi_user
Thanks
Hari
MCDBA
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:eg10JwhpEHA.4008@.TK2MSFTNGP14.phx.gbl...
>I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>|||Hi Dan,
I wrote the following script and tested it:
--
Use master
go
SET NOCOUNT ON
DECLARE @.strSQL varchar(255)
PRINT 'Killing Users'
PRINT '--'
CREATE table #tmpUsers(
spid int,
eid int,
status varchar(30),
loginname varchar(50),
hostname varchar(50),
blk int,
dbname varchar(50),
cmd varchar(30))
INSERT INTO #tmpUsers EXEC SP_WHO
DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = 'YOUR DATABASE NAME
HERE'
DECLARE @.spid varchar(10)
DECLARE @.dbname2 varchar(40)
OPEN LoginCursor
FETCH NEXT FROM LoginCursor INTO @.spid, @.dbname2
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT 'Killing ' + @.spid
SET @.strSQL = 'KILL ' + @.spid
EXEC (@.strSQL)
END
FETCH NEXT FROM LoginCursor INTO @.spid, @.dbname2
END
CLOSE LoginCursor
DEALLOCATE LoginCursor
DROP table #tmpUsers
PRINT 'Done'
go
--
Just replace 'YOUR DATABASE NAME HERE' with your database name.
Sasan
"Dan" wrote:
> I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>
>

KILLED/ROLLBACK - LCK_M_SCH_M - Estimated rollback completion: 0%

Hi,
Two processes on my SQL 2005 server stay with status KILLED/ROLLBACK:
#######################################################
spid
--
72
95
kpid
--
512
4784
lastwaittype
--
LCK_M_SCH_M
LCK_M_SCH_M
waitresource
--
TAB: 2:1095871390:0
TAB: 2:1544549701:0
login_time
--
2008-02-01 16:24:49.603
2008-02-01 16:44:10.533
last_batch
--
2008-02-01 16:24:50.590
2008-02-01 16:44:10.797
open_tran
--
1
1
status
--
suspended
suspended
hostname
--
WWW1
WWW2
program_name
--
.Net SqlClient Data Provider
.Net SqlClient Data Provider
cmd
--
KILLED/ROLLBACK (before kill was EXECUTE)
KILLED/ROLLBACK (before kill was EXECUTE)
Kill:
SPID 72: transaction rollback in progress. Estimated rollback completion:
0%. Estimated time remaining: 0 seconds.
SPID 95: transaction rollback in progress. Estimated rollback completion:
0%. Estimated time remaining: 0 seconds.
kill 72 with statusonly and kill 95 with status only say the same
#######################################################
I read archive post on group and mainly people suggest restart sql service.
I have this on production environment, so it isn't so easy to do.
Do you know any better way I think restart should be the last one.
--
Regards,
anxcompanxcomp,
I agree that restarting the service should be the last resort.
Unfortunately, I do not know of another resort.
If the locks still being held do not block anyone, then you can wait to
schedule the restart, but if it is blocking users of the system then you
will need to schedule the restart soon. (Happened to me today on a
development server. Always frustrating, but very much so on a production
server.)
RLF
"anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
news:B6034F08-7EA9-47C5-A8F7-CD7B3BD38005@.microsoft.com...
> Hi,
> Two processes on my SQL 2005 server stay with status KILLED/ROLLBACK:
> #######################################################
> spid
> --
> 72
> 95
> kpid
> --
> 512
> 4784
> lastwaittype
> --
> LCK_M_SCH_M
> LCK_M_SCH_M
>
> waitresource
> --
> TAB: 2:1095871390:0
> TAB: 2:1544549701:0
> login_time
> --
> 2008-02-01 16:24:49.603
> 2008-02-01 16:44:10.533
> last_batch
> --
> 2008-02-01 16:24:50.590
> 2008-02-01 16:44:10.797
> open_tran
> --
> 1
> 1
> status
> --
> suspended
> suspended
> hostname
> --
> WWW1
> WWW2
> program_name
> --
> .Net SqlClient Data Provider
> .Net SqlClient Data Provider
> cmd
> --
> KILLED/ROLLBACK (before kill was EXECUTE)
> KILLED/ROLLBACK (before kill was EXECUTE)
> Kill:
> SPID 72: transaction rollback in progress. Estimated rollback completion:
> 0%. Estimated time remaining: 0 seconds.
> SPID 95: transaction rollback in progress. Estimated rollback completion:
> 0%. Estimated time remaining: 0 seconds.
> kill 72 with statusonly and kill 95 with status only say the same
> #######################################################
> I read archive post on group and mainly people suggest restart sql
> service.
> I have this on production environment, so it isn't so easy to do.
> Do you know any better way I think restart should be the last one.
> --
> Regards,
> anxcomp|||Russel
Even if you restart the service, SQL Server attempts to recover the
database and in that case it will take long time.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:Ou5DpNEaIHA.4140@.TK2MSFTNGP04.phx.gbl...
> anxcomp,
> I agree that restarting the service should be the last resort.
> Unfortunately, I do not know of another resort.
> If the locks still being held do not block anyone, then you can wait to
> schedule the restart, but if it is blocking users of the system then you
> will need to schedule the restart soon. (Happened to me today on a
> development server. Always frustrating, but very much so on a production
> server.)
> RLF
> "anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
> news:B6034F08-7EA9-47C5-A8F7-CD7B3BD38005@.microsoft.com...
>> Hi,
>> Two processes on my SQL 2005 server stay with status KILLED/ROLLBACK:
>> #######################################################
>> spid
>> --
>> 72
>> 95
>> kpid
>> --
>> 512
>> 4784
>> lastwaittype
>> --
>> LCK_M_SCH_M
>> LCK_M_SCH_M
>>
>> waitresource
>> --
>> TAB: 2:1095871390:0
>> TAB: 2:1544549701:0
>> login_time
>> --
>> 2008-02-01 16:24:49.603
>> 2008-02-01 16:44:10.533
>> last_batch
>> --
>> 2008-02-01 16:24:50.590
>> 2008-02-01 16:44:10.797
>> open_tran
>> --
>> 1
>> 1
>> status
>> --
>> suspended
>> suspended
>> hostname
>> --
>> WWW1
>> WWW2
>> program_name
>> --
>> .Net SqlClient Data Provider
>> .Net SqlClient Data Provider
>> cmd
>> --
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> Kill:
>> SPID 72: transaction rollback in progress. Estimated rollback completion:
>> 0%. Estimated time remaining: 0 seconds.
>> SPID 95: transaction rollback in progress. Estimated rollback completion:
>> 0%. Estimated time remaining: 0 seconds.
>> kill 72 with statusonly and kill 95 with status only say the same
>> #######################################################
>> I read archive post on group and mainly people suggest restart sql
>> service.
>> I have this on production environment, so it isn't so easy to do.
>> Do you know any better way I think restart should be the last one.
>> --
>> Regards,
>> anxcomp
>|||Uri,
I know that can happen, but in actuality I have never had it take more than
a couple of minutes to recover even for a transaction that had been running
a couple of hours. Perhaps this is because rollback in a database that is
not in use yet is a lot quicker than rolling back a busy database.
However, in the case where you cannot kill a process and it is holding
critical locks, is there another choice? If so, I would love to know how to
resolve the problem without restarting the server.
RLF
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e50Rz7JaIHA.4208@.TK2MSFTNGP04.phx.gbl...
> Russel
> Even if you restart the service, SQL Server attempts to recover the
> database and in that case it will take long time.
>
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:Ou5DpNEaIHA.4140@.TK2MSFTNGP04.phx.gbl...
>> anxcomp,
>> I agree that restarting the service should be the last resort.
>> Unfortunately, I do not know of another resort.
>> If the locks still being held do not block anyone, then you can wait to
>> schedule the restart, but if it is blocking users of the system then you
>> will need to schedule the restart soon. (Happened to me today on a
>> development server. Always frustrating, but very much so on a production
>> server.)
>> RLF
>> "anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
>> news:B6034F08-7EA9-47C5-A8F7-CD7B3BD38005@.microsoft.com...
>> Hi,
>> Two processes on my SQL 2005 server stay with status KILLED/ROLLBACK:
>> #######################################################
>> spid
>> --
>> 72
>> 95
>> kpid
>> --
>> 512
>> 4784
>> lastwaittype
>> --
>> LCK_M_SCH_M
>> LCK_M_SCH_M
>>
>> waitresource
>> --
>> TAB: 2:1095871390:0
>> TAB: 2:1544549701:0
>> login_time
>> --
>> 2008-02-01 16:24:49.603
>> 2008-02-01 16:44:10.533
>> last_batch
>> --
>> 2008-02-01 16:24:50.590
>> 2008-02-01 16:44:10.797
>> open_tran
>> --
>> 1
>> 1
>> status
>> --
>> suspended
>> suspended
>> hostname
>> --
>> WWW1
>> WWW2
>> program_name
>> --
>> .Net SqlClient Data Provider
>> .Net SqlClient Data Provider
>> cmd
>> --
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> Kill:
>> SPID 72: transaction rollback in progress. Estimated rollback
>> completion:
>> 0%. Estimated time remaining: 0 seconds.
>> SPID 95: transaction rollback in progress. Estimated rollback
>> completion:
>> 0%. Estimated time remaining: 0 seconds.
>> kill 72 with statusonly and kill 95 with status only say the same
>> #######################################################
>> I read archive post on group and mainly people suggest restart sql
>> service.
>> I have this on production environment, so it isn't so easy to do.
>> Do you know any better way I think restart should be the last one.
>> --
>> Regards,
>> anxcomp
>>
>|||Russel
Yes, I had an epxerience where one person killed the long running
transaction and later on did a restart m and finally database is gone due to
recovering process
You are right , there is only chance to restart the service, I just wanted
to make apoint if you kill a long running transaction (DBCC REINDEX...) be
careful to restart the service and I'm recommeding to wait till rollback
will be completed
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OHiXLWMaIHA.5976@.TK2MSFTNGP05.phx.gbl...
> Uri,
> I know that can happen, but in actuality I have never had it take more
> than a couple of minutes to recover even for a transaction that had been
> running a couple of hours. Perhaps this is because rollback in a database
> that is not in use yet is a lot quicker than rolling back a busy database.
> However, in the case where you cannot kill a process and it is holding
> critical locks, is there another choice? If so, I would love to know how
> to resolve the problem without restarting the server.
> RLF
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e50Rz7JaIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Russel
>> Even if you restart the service, SQL Server attempts to recover the
>> database and in that case it will take long time.
>>
>>
>>
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:Ou5DpNEaIHA.4140@.TK2MSFTNGP04.phx.gbl...
>> anxcomp,
>> I agree that restarting the service should be the last resort.
>> Unfortunately, I do not know of another resort.
>> If the locks still being held do not block anyone, then you can wait to
>> schedule the restart, but if it is blocking users of the system then you
>> will need to schedule the restart soon. (Happened to me today on a
>> development server. Always frustrating, but very much so on a
>> production server.)
>> RLF
>> "anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
>> news:B6034F08-7EA9-47C5-A8F7-CD7B3BD38005@.microsoft.com...
>> Hi,
>> Two processes on my SQL 2005 server stay with status KILLED/ROLLBACK:
>> #######################################################
>> spid
>> --
>> 72
>> 95
>> kpid
>> --
>> 512
>> 4784
>> lastwaittype
>> --
>> LCK_M_SCH_M
>> LCK_M_SCH_M
>>
>> waitresource
>> --
>> TAB: 2:1095871390:0
>> TAB: 2:1544549701:0
>> login_time
>> --
>> 2008-02-01 16:24:49.603
>> 2008-02-01 16:44:10.533
>> last_batch
>> --
>> 2008-02-01 16:24:50.590
>> 2008-02-01 16:44:10.797
>> open_tran
>> --
>> 1
>> 1
>> status
>> --
>> suspended
>> suspended
>> hostname
>> --
>> WWW1
>> WWW2
>> program_name
>> --
>> .Net SqlClient Data Provider
>> .Net SqlClient Data Provider
>> cmd
>> --
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> Kill:
>> SPID 72: transaction rollback in progress. Estimated rollback
>> completion:
>> 0%. Estimated time remaining: 0 seconds.
>> SPID 95: transaction rollback in progress. Estimated rollback
>> completion:
>> 0%. Estimated time remaining: 0 seconds.
>> kill 72 with statusonly and kill 95 with status only say the same
>> #######################################################
>> I read archive post on group and mainly people suggest restart sql
>> service.
>> I have this on production environment, so it isn't so easy to do.
>> Do you know any better way I think restart should be the last one.
>> --
>> Regards,
>> anxcomp
>>
>>
>|||Uri, Thanks for the additional note. - RLF
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%236ff8gMaIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Russel
> Yes, I had an epxerience where one person killed the long running
> transaction and later on did a restart m and finally database is gone due
> to recovering process
> You are right , there is only chance to restart the service, I just wanted
> to make apoint if you kill a long running transaction (DBCC REINDEX...)
> be careful to restart the service and I'm recommeding to wait till
> rollback will be completed
>
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:OHiXLWMaIHA.5976@.TK2MSFTNGP05.phx.gbl...
>> Uri,
>> I know that can happen, but in actuality I have never had it take more
>> than a couple of minutes to recover even for a transaction that had been
>> running a couple of hours. Perhaps this is because rollback in a database
>> that is not in use yet is a lot quicker than rolling back a busy
>> database.
>> However, in the case where you cannot kill a process and it is holding
>> critical locks, is there another choice? If so, I would love to know how
>> to resolve the problem without restarting the server.
>> RLF
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:e50Rz7JaIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Russel
>> Even if you restart the service, SQL Server attempts to recover the
>> database and in that case it will take long time.
>>
>>
>>
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:Ou5DpNEaIHA.4140@.TK2MSFTNGP04.phx.gbl...
>> anxcomp,
>> I agree that restarting the service should be the last resort.
>> Unfortunately, I do not know of another resort.
>> If the locks still being held do not block anyone, then you can wait to
>> schedule the restart, but if it is blocking users of the system then
>> you will need to schedule the restart soon. (Happened to me today on a
>> development server. Always frustrating, but very much so on a
>> production server.)
>> RLF
>> "anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
>> news:B6034F08-7EA9-47C5-A8F7-CD7B3BD38005@.microsoft.com...
>> Hi,
>> Two processes on my SQL 2005 server stay with status KILLED/ROLLBACK:
>> #######################################################
>> spid
>> --
>> 72
>> 95
>> kpid
>> --
>> 512
>> 4784
>> lastwaittype
>> --
>> LCK_M_SCH_M
>> LCK_M_SCH_M
>>
>> waitresource
>> --
>> TAB: 2:1095871390:0
>> TAB: 2:1544549701:0
>> login_time
>> --
>> 2008-02-01 16:24:49.603
>> 2008-02-01 16:44:10.533
>> last_batch
>> --
>> 2008-02-01 16:24:50.590
>> 2008-02-01 16:44:10.797
>> open_tran
>> --
>> 1
>> 1
>> status
>> --
>> suspended
>> suspended
>> hostname
>> --
>> WWW1
>> WWW2
>> program_name
>> --
>> .Net SqlClient Data Provider
>> .Net SqlClient Data Provider
>> cmd
>> --
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> Kill:
>> SPID 72: transaction rollback in progress. Estimated rollback
>> completion:
>> 0%. Estimated time remaining: 0 seconds.
>> SPID 95: transaction rollback in progress. Estimated rollback
>> completion:
>> 0%. Estimated time remaining: 0 seconds.
>> kill 72 with statusonly and kill 95 with status only say the same
>> #######################################################
>> I read archive post on group and mainly people suggest restart sql
>> service.
>> I have this on production environment, so it isn't so easy to do.
>> Do you know any better way I think restart should be the last one.
>> --
>> Regards,
>> anxcomp
>>
>>
>>
>|||Uri,
One additional comment: In the case described by anxcomp, I am unsure that
the rollback will ever complete. I kept one in that state open for a day
(and a long day it was, too) and it never rolled back and for the whole time
a
KILL spid WITH STATUSONLY:
reported the following:
Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
I have also seen a similar situation, but it reported:
Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.
FWIW,
RLF
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%236ff8gMaIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Russel
> Yes, I had an epxerience where one person killed the long running
> transaction and later on did a restart m and finally database is gone due
> to recovering process
> You are right , there is only chance to restart the service, I just wanted
> to make apoint if you kill a long running transaction (DBCC REINDEX...)
> be careful to restart the service and I'm recommeding to wait till
> rollback will be completed
>
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:OHiXLWMaIHA.5976@.TK2MSFTNGP05.phx.gbl...
>> Uri,
>> I know that can happen, but in actuality I have never had it take more
>> than a couple of minutes to recover even for a transaction that had been
>> running a couple of hours. Perhaps this is because rollback in a database
>> that is not in use yet is a lot quicker than rolling back a busy
>> database.
>> However, in the case where you cannot kill a process and it is holding
>> critical locks, is there another choice? If so, I would love to know how
>> to resolve the problem without restarting the server.
>> RLF
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:e50Rz7JaIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Russel
>> Even if you restart the service, SQL Server attempts to recover the
>> database and in that case it will take long time.
>>
>>
>>
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:Ou5DpNEaIHA.4140@.TK2MSFTNGP04.phx.gbl...
>> anxcomp,
>> I agree that restarting the service should be the last resort.
>> Unfortunately, I do not know of another resort.
>> If the locks still being held do not block anyone, then you can wait to
>> schedule the restart, but if it is blocking users of the system then
>> you will need to schedule the restart soon. (Happened to me today on a
>> development server. Always frustrating, but very much so on a
>> production server.)
>> RLF
>> "anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
>> news:B6034F08-7EA9-47C5-A8F7-CD7B3BD38005@.microsoft.com...
>> Hi,
>> Two processes on my SQL 2005 server stay with status KILLED/ROLLBACK:
>> #######################################################
>> spid
>> --
>> 72
>> 95
>> kpid
>> --
>> 512
>> 4784
>> lastwaittype
>> --
>> LCK_M_SCH_M
>> LCK_M_SCH_M
>>
>> waitresource
>> --
>> TAB: 2:1095871390:0
>> TAB: 2:1544549701:0
>> login_time
>> --
>> 2008-02-01 16:24:49.603
>> 2008-02-01 16:44:10.533
>> last_batch
>> --
>> 2008-02-01 16:24:50.590
>> 2008-02-01 16:44:10.797
>> open_tran
>> --
>> 1
>> 1
>> status
>> --
>> suspended
>> suspended
>> hostname
>> --
>> WWW1
>> WWW2
>> program_name
>> --
>> .Net SqlClient Data Provider
>> .Net SqlClient Data Provider
>> cmd
>> --
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> Kill:
>> SPID 72: transaction rollback in progress. Estimated rollback
>> completion:
>> 0%. Estimated time remaining: 0 seconds.
>> SPID 95: transaction rollback in progress. Estimated rollback
>> completion:
>> 0%. Estimated time remaining: 0 seconds.
>> kill 72 with statusonly and kill 95 with status only say the same
>> #######################################################
>> I read archive post on group and mainly people suggest restart sql
>> service.
>> I have this on production environment, so it isn't so easy to do.
>> Do you know any better way I think restart should be the last one.
>> --
>> Regards,
>> anxcomp
>>
>>
>>
>|||>I kept one in that state open for a day (and a long day it was, too) and it
>never rolled back and for the whole time
So what does it do? Is it just killed without rollback the process?
Fortunately or unfortunately :-) I did not happen to use KILL command in a
production/development that is on SQL Server 2005 , moreover I have never
used WITH STATUSONLY oprion
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23tdvtINaIHA.1168@.TK2MSFTNGP02.phx.gbl...
> Uri,
> One additional comment: In the case described by anxcomp, I am unsure that
> the rollback will ever complete. I kept one in that state open for a day
> (and a long day it was, too) and it never rolled back and for the whole
> time a
> KILL spid WITH STATUSONLY:
> reported the following:
> Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
> I have also seen a similar situation, but it reported:
> Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.
> FWIW,
> RLF
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%236ff8gMaIHA.5208@.TK2MSFTNGP04.phx.gbl...
>> Russel
>> Yes, I had an epxerience where one person killed the long running
>> transaction and later on did a restart m and finally database is gone due
>> to recovering process
>> You are right , there is only chance to restart the service, I just
>> wanted to make apoint if you kill a long running transaction (DBCC
>> REINDEX...) be careful to restart the service and I'm recommeding to wait
>> till rollback will be completed
>>
>>
>>
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:OHiXLWMaIHA.5976@.TK2MSFTNGP05.phx.gbl...
>> Uri,
>> I know that can happen, but in actuality I have never had it take more
>> than a couple of minutes to recover even for a transaction that had been
>> running a couple of hours. Perhaps this is because rollback in a
>> database that is not in use yet is a lot quicker than rolling back a
>> busy database.
>> However, in the case where you cannot kill a process and it is holding
>> critical locks, is there another choice? If so, I would love to know
>> how to resolve the problem without restarting the server.
>> RLF
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:e50Rz7JaIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Russel
>> Even if you restart the service, SQL Server attempts to recover the
>> database and in that case it will take long time.
>>
>>
>>
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:Ou5DpNEaIHA.4140@.TK2MSFTNGP04.phx.gbl...
>> anxcomp,
>> I agree that restarting the service should be the last resort.
>> Unfortunately, I do not know of another resort.
>> If the locks still being held do not block anyone, then you can wait
>> to schedule the restart, but if it is blocking users of the system
>> then you will need to schedule the restart soon. (Happened to me
>> today on a development server. Always frustrating, but very much so
>> on a production server.)
>> RLF
>> "anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
>> news:B6034F08-7EA9-47C5-A8F7-CD7B3BD38005@.microsoft.com...
>> Hi,
>> Two processes on my SQL 2005 server stay with status KILLED/ROLLBACK:
>> #######################################################
>> spid
>> --
>> 72
>> 95
>> kpid
>> --
>> 512
>> 4784
>> lastwaittype
>> --
>> LCK_M_SCH_M
>> LCK_M_SCH_M
>>
>> waitresource
>> --
>> TAB: 2:1095871390:0
>> TAB: 2:1544549701:0
>> login_time
>> --
>> 2008-02-01 16:24:49.603
>> 2008-02-01 16:44:10.533
>> last_batch
>> --
>> 2008-02-01 16:24:50.590
>> 2008-02-01 16:44:10.797
>> open_tran
>> --
>> 1
>> 1
>> status
>> --
>> suspended
>> suspended
>> hostname
>> --
>> WWW1
>> WWW2
>> program_name
>> --
>> .Net SqlClient Data Provider
>> .Net SqlClient Data Provider
>> cmd
>> --
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> Kill:
>> SPID 72: transaction rollback in progress. Estimated rollback
>> completion:
>> 0%. Estimated time remaining: 0 seconds.
>> SPID 95: transaction rollback in progress. Estimated rollback
>> completion:
>> 0%. Estimated time remaining: 0 seconds.
>> kill 72 with statusonly and kill 95 with status only say the same
>> #######################################################
>> I read archive post on group and mainly people suggest restart sql
>> service.
>> I have this on production environment, so it isn't so easy to do.
>> Do you know any better way I think restart should be the last one.
>> --
>> Regards,
>> anxcomp
>>
>>
>>
>>
>|||Uri,
> So what does it do? Is it just killed without rollback the process?
My window into what is really going on is only the tools that SQL Server
provides. Did it rollback at all? Maybe, but it claims it has not. Did it
really make any significant changes? No idea. Did it hold blocking locks?
Yes, preventing some application functions from working. Et cetera.
It _claims_ that it has entered the rollback state, but has rolled nothing
back. :-(
(I hope that anxcomp is finding this interesting.)
RLF|||I've tried switch database to simple or offline mode for a moment, but
without success.
#############################
alter database db1
set single_user
with
rollback immediate
alter database db1
set offline
with
rollback immediate
#############################
It only remains for me to restart :-(
Hope database restore will not take long time.
--
Regards,
anxcomp|||anxcomp,
When you say "database restore" I assume that you mean the automatic
"database recovery" that happens at startup. I reviewed the logs of the
server I had to do this on. (And, yes, I do hate being pushed into taking
that action.)
The SQL Server did some automatic DBCC CHECKDB and other recovery work. For
the big, active database at the root of the problem at recovery time it
said.
Recovery of database "BigDatabase" (111) is 0% complete (approximately
4826 seconds remain)
That would have been about 80 minutes, but it actually recovered in 29
seconds with 1 transaction rolling back.
For what that is worth,
RLF
"anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
news:4EDF93EA-C416-404A-8B20-0D8A982A9944@.microsoft.com...
> I've tried switch database to simple or offline mode for a moment, but
> without success.
> #############################
> alter database db1
> set single_user
> with
> rollback immediate
> alter database db1
> set offline
> with
> rollback immediate
> #############################
> It only remains for me to restart :-(
> Hope database restore will not take long time.
> --
> Regards,
> anxcomp|||Hi,
I've restarted today morning SQL Service. Fortunately SQL started without
any problem (about 30 seconds). ActiveMonitor doesn't show any problematic
processes :-)
--
Regards,
anxcomp

KILLED/ROLLBACK - LCK_M_SCH_M - Estimated rollback completion: 0%

Hi,
Two processes on my SQL 2005 server stay with status KILLED/ROLLBACK:
################################################## #####
spid
72
95
kpid
512
4784
lastwaittype
LCK_M_SCH_M
LCK_M_SCH_M
waitresource
TAB: 2:1095871390:0
TAB: 2:1544549701:0
login_time
2008-02-01 16:24:49.603
2008-02-01 16:44:10.533
last_batch
2008-02-01 16:24:50.590
2008-02-01 16:44:10.797
open_tran
1
1
status
suspended
suspended
hostname
WWW1
WWW2
program_name
..Net SqlClient Data Provider
..Net SqlClient Data Provider
cmd
KILLED/ROLLBACK (before kill was EXECUTE)
KILLED/ROLLBACK (before kill was EXECUTE)
Kill:
SPID 72: transaction rollback in progress. Estimated rollback completion:
0%. Estimated time remaining: 0 seconds.
SPID 95: transaction rollback in progress. Estimated rollback completion:
0%. Estimated time remaining: 0 seconds.
kill 72 with statusonly and kill 95 with status only say the same
################################################## #####
I read archive post on group and mainly people suggest restart sql service.
I have this on production environment, so it isn't so easy to do.
Do you know any better way I think restart should be the last one.
Regards,
anxcomp
anxcomp,
I agree that restarting the service should be the last resort.
Unfortunately, I do not know of another resort.
If the locks still being held do not block anyone, then you can wait to
schedule the restart, but if it is blocking users of the system then you
will need to schedule the restart soon. (Happened to me today on a
development server. Always frustrating, but very much so on a production
server.)
RLF
"anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
news:B6034F08-7EA9-47C5-A8F7-CD7B3BD38005@.microsoft.com...
> Hi,
> Two processes on my SQL 2005 server stay with status KILLED/ROLLBACK:
> ################################################## #####
> spid
> --
> 72
> 95
> kpid
> --
> 512
> 4784
> lastwaittype
> --
> LCK_M_SCH_M
> LCK_M_SCH_M
>
> waitresource
> --
> TAB: 2:1095871390:0
> TAB: 2:1544549701:0
> login_time
> --
> 2008-02-01 16:24:49.603
> 2008-02-01 16:44:10.533
> last_batch
> --
> 2008-02-01 16:24:50.590
> 2008-02-01 16:44:10.797
> open_tran
> --
> 1
> 1
> status
> --
> suspended
> suspended
> hostname
> --
> WWW1
> WWW2
> program_name
> --
> .Net SqlClient Data Provider
> .Net SqlClient Data Provider
> cmd
> --
> KILLED/ROLLBACK (before kill was EXECUTE)
> KILLED/ROLLBACK (before kill was EXECUTE)
> Kill:
> SPID 72: transaction rollback in progress. Estimated rollback completion:
> 0%. Estimated time remaining: 0 seconds.
> SPID 95: transaction rollback in progress. Estimated rollback completion:
> 0%. Estimated time remaining: 0 seconds.
> kill 72 with statusonly and kill 95 with status only say the same
> ################################################## #####
> I read archive post on group and mainly people suggest restart sql
> service.
> I have this on production environment, so it isn't so easy to do.
> Do you know any better way I think restart should be the last one.
> --
> Regards,
> anxcomp
|||Russel
Even if you restart the service, SQL Server attempts to recover the
database and in that case it will take long time.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:Ou5DpNEaIHA.4140@.TK2MSFTNGP04.phx.gbl...
> anxcomp,
> I agree that restarting the service should be the last resort.
> Unfortunately, I do not know of another resort.
> If the locks still being held do not block anyone, then you can wait to
> schedule the restart, but if it is blocking users of the system then you
> will need to schedule the restart soon. (Happened to me today on a
> development server. Always frustrating, but very much so on a production
> server.)
> RLF
> "anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
> news:B6034F08-7EA9-47C5-A8F7-CD7B3BD38005@.microsoft.com...
>
|||Uri,
I know that can happen, but in actuality I have never had it take more than
a couple of minutes to recover even for a transaction that had been running
a couple of hours. Perhaps this is because rollback in a database that is
not in use yet is a lot quicker than rolling back a busy database.
However, in the case where you cannot kill a process and it is holding
critical locks, is there another choice? If so, I would love to know how to
resolve the problem without restarting the server.
RLF
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e50Rz7JaIHA.4208@.TK2MSFTNGP04.phx.gbl...
> Russel
> Even if you restart the service, SQL Server attempts to recover the
> database and in that case it will take long time.
>
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:Ou5DpNEaIHA.4140@.TK2MSFTNGP04.phx.gbl...
>
|||Russel
Yes, I had an epxerience where one person killed the long running
transaction and later on did a restart m and finally database is gone due to
recovering process
You are right , there is only chance to restart the service, I just wanted
to make apoint if you kill a long running transaction (DBCC REINDEX...) be
careful to restart the service and I'm recommeding to wait till rollback
will be completed
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OHiXLWMaIHA.5976@.TK2MSFTNGP05.phx.gbl...
> Uri,
> I know that can happen, but in actuality I have never had it take more
> than a couple of minutes to recover even for a transaction that had been
> running a couple of hours. Perhaps this is because rollback in a database
> that is not in use yet is a lot quicker than rolling back a busy database.
> However, in the case where you cannot kill a process and it is holding
> critical locks, is there another choice? If so, I would love to know how
> to resolve the problem without restarting the server.
> RLF
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e50Rz7JaIHA.4208@.TK2MSFTNGP04.phx.gbl...
>
|||Uri, Thanks for the additional note. - RLF
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%236ff8gMaIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Russel
> Yes, I had an epxerience where one person killed the long running
> transaction and later on did a restart m and finally database is gone due
> to recovering process
> You are right , there is only chance to restart the service, I just wanted
> to make apoint if you kill a long running transaction (DBCC REINDEX...)
> be careful to restart the service and I'm recommeding to wait till
> rollback will be completed
>
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:OHiXLWMaIHA.5976@.TK2MSFTNGP05.phx.gbl...
>
|||Uri,
One additional comment: In the case described by anxcomp, I am unsure that
the rollback will ever complete. I kept one in that state open for a day
(and a long day it was, too) and it never rolled back and for the whole time
a
KILL spid WITH STATUSONLY:
reported the following:
Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
I have also seen a similar situation, but it reported:
Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.
FWIW,
RLF
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%236ff8gMaIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Russel
> Yes, I had an epxerience where one person killed the long running
> transaction and later on did a restart m and finally database is gone due
> to recovering process
> You are right , there is only chance to restart the service, I just wanted
> to make apoint if you kill a long running transaction (DBCC REINDEX...)
> be careful to restart the service and I'm recommeding to wait till
> rollback will be completed
>
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:OHiXLWMaIHA.5976@.TK2MSFTNGP05.phx.gbl...
>
|||>I kept one in that state open for a day (and a long day it was, too) and it
>never rolled back and for the whole time
So what does it do? Is it just killed without rollback the process?
Fortunately or unfortunately :-) I did not happen to use KILL command in a
production/development that is on SQL Server 2005 , moreover I have never
used WITH STATUSONLY oprion
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23tdvtINaIHA.1168@.TK2MSFTNGP02.phx.gbl...
> Uri,
> One additional comment: In the case described by anxcomp, I am unsure that
> the rollback will ever complete. I kept one in that state open for a day
> (and a long day it was, too) and it never rolled back and for the whole
> time a
> KILL spid WITH STATUSONLY:
> reported the following:
> Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
> I have also seen a similar situation, but it reported:
> Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.
> FWIW,
> RLF
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%236ff8gMaIHA.5208@.TK2MSFTNGP04.phx.gbl...
>
|||Uri,

> So what does it do? Is it just killed without rollback the process?
My window into what is really going on is only the tools that SQL Server
provides. Did it rollback at all? Maybe, but it claims it has not. Did it
really make any significant changes? No idea. Did it hold blocking locks?
Yes, preventing some application functions from working. Et cetera.
It _claims_ that it has entered the rollback state, but has rolled nothing
back. :-(
(I hope that anxcomp is finding this interesting.)
RLF
|||I've tried switch database to simple or offline mode for a moment, but
without success.
#############################
alter database db1
set single_user
with
rollback immediate
alter database db1
set offline
with
rollback immediate
#############################
It only remains for me to restart :-(
Hope database restore will not take long time.
Regards,
anxcomp
sql

Kill Processes

Can you kill processes in SQL Server 2K without stopping SQL Server and restarting it? I am using sp_who2 to get a list of active users and I see some accounts that are logged off but still showing up and I am trying to find a way to Kill these accounts and the processes they are doing.you can use
1) KILL 53(spid)
Or
Enterprise Manager-->Management-->current Activity-->Process Info
you can pick specific spid and kill it from here..
SS|||What do you mean logged off?

Sleeping?

How many connections are we talking about?

Do you see the Login?

Can you NET SEND them a message?

Better off to leave them alone...It takes resources to reestablish them...

Anyway you'll love this...

It's KILL <spid>|||Do I just do this in the Query Analyer?

kill logged in processes to a database

How do kill all the processes in a database? Here is my situation; I copy a
database called “Active” into a database called “Training” once a we
ek. If
there are still students logged into the Training database the job fails and
I have to go and kill all the processes related to the Training databases so
that I can restart the job that copies that Active database to the training
database. Is there a t-sql script that I can use to kill all (1 – 500) the
users logged into Training database as a first step in a job before the copy
process?
Thanks
JosephSur you can do that by looping through the process and kill them by a cursor
basis, but the best thing would be to use ALTER DATABASE statement to set
the database to a "restricted" mode where you can do your maintainance. More
information in BOL under ALTER DATABASE e.g.
ALTER DATABASE <Nameofthedb> SET
SINGLE_USER with rollback immediate
Setting back after the maintainance:
ALTER DATABASE <Nameofthedb> SET
READWRITE with rollback immediate
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Joseph" <Joseph@.discussions.microsoft.com> schrieb im Newsbeitrag
news:C44DC59D-FD66-4491-9620-84DB1A59CC0E@.microsoft.com...
> How do kill all the processes in a database? Here is my situation; I copy
> a
> database called "Active" into a database called "Training" once a w.
> If
> there are still students logged into the Training database the job fails
> and
> I have to go and kill all the processes related to the Training databases
> so
> that I can restart the job that copies that Active database to the
> training
> database. Is there a t-sql script that I can use to kill all (1 - 500) the
> users logged into Training database as a first step in a job before the
> copy
> process?
> Thanks
> Joseph
>sql

kill idle process automatically

Dear all,
is there a way of killing those processes automatically by the server
where the processes have been idle for long time (where the last SQL batch
was executed >45 mins ago)?
Thanx in advanced.
Lee
Learn
I afraid there isn't, but using sp_who2 system stored procedure you will be
able to see all activities on the server as well as status of the
processes.
"Learn Yee" <learnyee@.freightmark.com.my> wrote in message
news:OdnYO08ZEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Dear all,
> is there a way of killing those processes automatically by the server
> where the processes have been idle for long time (where the last SQL batch
> was executed >45 mins ago)?
> Thanx in advanced.
> Lee
>
|||Hi,
Killing the sleeping user process may not be a good idea. A user processes
may be running for 40 minutes
and when you check that process may be sleeping and you code will kill that
process.
I recommend you to, not to automate this process in production server.
How to do:-
To do this you can Query the MASTER..SYSPROCESSES table for Login_time ,
Status,cpu columns
compare the values with your threshold (say 45 minutes) and then use the
command KILL <spid> to kill the process.
Thanks
Hari
MCDBA
"Learn Yee" <learnyee@.freightmark.com.my> wrote in message
news:OdnYO08ZEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Dear all,
> is there a way of killing those processes automatically by the server
> where the processes have been idle for long time (where the last SQL batch
> was executed >45 mins ago)?
> Thanx in advanced.
> Lee
>
|||Learn Yee,
Why do you want to do that? Could you describe your problem in further
detail? I would not recommend this.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Learn Yee wrote:
> Dear all,
> is there a way of killing those processes automatically by the server
> where the processes have been idle for long time (where the last SQL batch
> was executed >45 mins ago)?
> Thanx in advanced.
> Lee
>

kill idle process automatically

Dear all,
is there a way of killing those processes automatically by the server
where the processes have been idle for long time (where the last SQL batch
was executed >45 mins ago)?
Thanx in advanced.
LeeLearn
I afraid there isn't, but using sp_who2 system stored procedure you will be
able to see all activities on the server as well as status of the
processes.
"Learn Yee" <learnyee@.freightmark.com.my> wrote in message
news:OdnYO08ZEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Dear all,
> is there a way of killing those processes automatically by the server
> where the processes have been idle for long time (where the last SQL batch
> was executed >45 mins ago)?
> Thanx in advanced.
> Lee
>|||Hi,
Killing the sleeping user process may not be a good idea. A user processes
may be running for 40 minutes
and when you check that process may be sleeping and you code will kill that
process.
I recommend you to, not to automate this process in production server.
How to do:-
To do this you can Query the MASTER..SYSPROCESSES table for Login_time ,
Status,cpu columns
compare the values with your threshold (say 45 minutes) and then use the
command KILL <spid> to kill the process.
Thanks
Hari
MCDBA
"Learn Yee" <learnyee@.freightmark.com.my> wrote in message
news:OdnYO08ZEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Dear all,
> is there a way of killing those processes automatically by the server
> where the processes have been idle for long time (where the last SQL batch
> was executed >45 mins ago)?
> Thanx in advanced.
> Lee
>|||Learn Yee,
Why do you want to do that? Could you describe your problem in further
detail? I would not recommend this.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Learn Yee wrote:
> Dear all,
> is there a way of killing those processes automatically by the server
> where the processes have been idle for long time (where the last SQL batch
> was executed >45 mins ago)?
> Thanx in advanced.
> Lee
>

kill idle process automatically

Dear all,
is there a way of killing those processes automatically by the server
where the processes have been idle for long time (where the last SQL batch
was executed >45 mins ago)?
Thanx in advanced.
LeeLearn
I afraid there isn't, but using sp_who2 system stored procedure you will be
able to see all activities on the server as well as status of the
processes.
"Learn Yee" <learnyee@.freightmark.com.my> wrote in message
news:OdnYO08ZEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Dear all,
> is there a way of killing those processes automatically by the server
> where the processes have been idle for long time (where the last SQL batch
> was executed >45 mins ago)?
> Thanx in advanced.
> Lee
>|||Hi,
Killing the sleeping user process may not be a good idea. A user processes
may be running for 40 minutes
and when you check that process may be sleeping and you code will kill that
process.
I recommend you to, not to automate this process in production server.
How to do:-
To do this you can Query the MASTER..SYSPROCESSES table for Login_time ,
Status,cpu columns
compare the values with your threshold (say 45 minutes) and then use the
command KILL <spid> to kill the process.
Thanks
Hari
MCDBA
"Learn Yee" <learnyee@.freightmark.com.my> wrote in message
news:OdnYO08ZEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Dear all,
> is there a way of killing those processes automatically by the server
> where the processes have been idle for long time (where the last SQL batch
> was executed >45 mins ago)?
> Thanx in advanced.
> Lee
>|||Learn Yee,
Why do you want to do that? Could you describe your problem in further
detail? I would not recommend this.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Learn Yee wrote:
> Dear all,
> is there a way of killing those processes automatically by the server
> where the processes have been idle for long time (where the last SQL batch
> was executed >45 mins ago)?
> Thanx in advanced.
> Lee
>sql

kill all processes

Hello
I am looking for a command that would kill all sessions (active or not) on a
database or server.
Is there such command?
thanks for your help
WilfridIt is easy at the database level. Use ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE. At
the server level, you could loop sysprocesses and for each connection, execute the KILL command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wilfrid" <grille11@.yahoo.com> wrote in message news:442d4094$0$479$626a54ce@.news.free.fr...
> Hello
> I am looking for a command that would kill all sessions (active or not) on a database or server.
> Is there such command?
> thanks for your help
> Wilfrid
>

kill all processes

Hello
I am looking for a command that would kill all sessions (active or not) on a
database or server.
Is there such command?
thanks for your help
Wilfrid
It is easy at the database level. Use ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE. At
the server level, you could loop sysprocesses and for each connection, execute the KILL command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wilfrid" <grille11@.yahoo.com> wrote in message news:442d4094$0$479$626a54ce@.news.free.fr...
> Hello
> I am looking for a command that would kill all sessions (active or not) on a database or server.
> Is there such command?
> thanks for your help
> Wilfrid
>
sql

kill all processes

Hello
I am looking for a command that would kill all sessions (active or not) on a
database or server.
Is there such command?
thanks for your help
WilfridIt is easy at the database level. Use ALTER DATABASE ... SET SINGLE_USER WIT
H ROLLBACK IMMEDIATE. At
the server level, you could loop sysprocesses and for each connection, execu
te the KILL command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wilfrid" <grille11@.yahoo.com> wrote in message news:442d4094$0$479$626a54ce@.news.free.fr...

> Hello
> I am looking for a command that would kill all sessions (active or not) on
a database or server.
> Is there such command?
> thanks for your help
> Wilfrid
>

Kill a SQL process

Hi, I hope someone can help.
We are using SQL MSDE on a SBS2003 server for monitoring purposes. There is
a small problem with one of the SQL processes leaking memory. I need to be
able search for and kill a connection from the Firewall service to the maste
r
table.
I am doing this as a scheduled job within SQL Enterprise Manager. I can
successfully track down the SPID of the connection that I want to kill. I
just don't know how to go about killing it.
Here is the code I already have;
USE master;
SELECT spid
FROM sysprocesses
WHERE memusage>40000
AND dbid=1
Could someone tell me please how to kill this connection now that i have the
SPID?
My thanks in advance.
Chris.run a cursor on the select that u have written.
get the result into a variable say @.spid
and just use the command
KILL @.spid|||give the command
kill 123
where 123 is the spid|||sorry about that outburst :)
try this
USE master;
declare @.rowcount int
,@.spid int
while (@.rowcount >0)
begin
SELECT top 1 @.spid = spid
FROM sysprocesses
WHERE
memusage>40000 AND
dbid=1
set @.rowcount = @.@.rowcount
exec('kill ' + @.spid)
end
let me know if it helps|||Thanks, i appreciate your quick reply
Please excuse my ignorance but I do not know how to do this. I have never
used SQL before so I am not familiar with commands and syntax.
How do I get the result into the variable?
Do I have to declare the variable first, if so how?
What do you mean, run a cursor?
"Arumugam" wrote:

> run a cursor on the select that u have written.
> get the result into a variable say @.spid
> and just use the command
> KILL @.spid
>|||declare @.spid int,
@.qry varchar(20)
declare c1 cursor for
SELECT spid
FROM sysprocesses
WHERE memusage>40000
AND dbid=1
open c1
fetch next from c1 into @.spid
while @.@.fetch_status = 0
begin
select @.qry = 'KILL ' + cast (@.spid as varchar(10))
exec(@.qry)
fetch next from c1 into @.spid
end
close c1
deallocate c1|||Thanks for the code. Don't worry about the 'outburst' it wasn't as harsh as
I
was expecting. I tried it but it doesn't kill the process.
I'm nearly at the point of understanding the code below, I just don't get
why the @.rowcount is in there. What would it's initial value be? and where
does that come from? Is this the reason it doesn't work?
Sorry if I sound daft. I'm not. Just really new to SQL.
"Omnibuzz" wrote:

> sorry about that outburst :)
> try this
> USE master;
> declare @.rowcount int
> ,@.spid int
> while (@.rowcount >0)
> begin
> SELECT top 1 @.spid = spid
> FROM sysprocesses
> WHERE
> memusage>40000 AND
> dbid=1
> set @.rowcount = @.@.rowcount
> exec('kill ' + @.spid)
> end
> let me know if it helps|||It should read @.@.rowcount. It's a system global variable containing the
number of rows affected by the last DML statement.
ML
http://milambda.blogspot.com/|||Which instance of @.rowcount should be @.@.rowcount?|||Thanks for the help you three.
I got it working now. No need for the WHILE statement in there. Unless I've
missunderstood?
Cheers,
Chris.
"Chris ONeill" wrote:
> Thanks for the code. Don't worry about the 'outburst' it wasn't as harsh a
s I
> was expecting. I tried it but it doesn't kill the process.
> I'm nearly at the point of understanding the code below, I just don't get
> why the @.rowcount is in there. What would it's initial value be? and where
> does that come from? Is this the reason it doesn't work?
> Sorry if I sound daft. I'm not. Just really new to SQL.
> "Omnibuzz" wrote:
>