• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 11477
  • Last Modified:

script to kill user connections in the db

Hi ,

I have to do a Unix replicatio for a Sybase db ,before doing that I need to kil all the other users connections ,can you help me with the script.

This is what I have so far:

CREATE proc rp_kill_db_processes
(@dbname varchar(20))
as

Declare @dbid int,
@spid int,
@str nvarchar(128)
select @dbid = dbid from master..sysdatabases
where name = @dbname
declare spidcurs cursor for
select spid from master..sysprocesses where dbid = @dbid
open spidcurs
fetch next from spidcurs into @spid
While @@fetch_status = 0
Begin
Select @str = 'Kill '+convert(nvarchar(30),@spid)
exec(@str)
--print @str
fetch next from spidcurs into @spid
End
Deallocate spidcurs
GO

Please let me know asap.

thanks
gyans
0
gyans
Asked:
gyans
  • 9
  • 8
1 Solution
 
bretCommented:
Could you explain more about what you are actually trying to do?
Why do you feel you have to kill the user connections?

If you really need to do that, it would probably be much easier
to just shutdown ASE and restart it using -m (single user) and/or
a different port number than usual.

However, it sounds to me like you should just use the QUIESCE DATABASE
command to temporarily halt activity on the database while you
copy the underlying devices.  When the database copy is brought
only, any transactions that were in progress at the time of the copy
will be rolled back automatically.

-bret


0
 
gyansAuthor Commented:
We are using 4.9.2 version of Sybase, initally  when we did dump and load it took hours to complete.
Then we started sql backtrack, it would still take 14  to 30 hours to complete.
So now we shutdown the databases ,and then copy it over the DR server.
and backup the DR server to tape.

How would I use the commands that you gave.

To use our type of replciation we are getting into all kinds of errors during shutdown ,so we want to kill all the user connections except the master and issue a normal shutdown.

I would really apprecaite your ahelp,
thanks
0
 
bretCommented:
Well, the QUIESCE command was added well after 4.9.2.  I recommend that
you clearly state your version up front in future questions.

For that matter, the EXEC command used for dynamic sql (i.e. executing
a string of commands rather than an existing stored procedure) was also not
in 4.9.2

What I remember doing for 4.9.x was creating a UNIX shell script
something like this, i.e. use SQL to create a SQL batch file that will
kill all the existing processes in the server.  
The column name is set to "--" to make it appear to be a comment.

isql -Usa -P -o killemall.sql << EOF
set nocount on
select "kill " + convert(char(5), spid) + char(10) + "go" as "--" from master..sysprocesses
go
EOF
isql -Usa -P -i killemall.sql

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
gyansAuthor Commented:
This kills all the processes , I do not want the sybase processes in master db to be killed ,it should go down with a normal shutdown.

I only need to kill all the user processes in all the other databases except master.
We need to do a graceful shutdown ,currently we do a 'shutdown with nowait' and that has caused corruption in our database twice and since we lose  time and data we are resorting to this processes.
thanks
0
 
bretCommented:
Ok - just add whatever additional WHERE clauses you want to the 'select "kill"...' statement,
such as "where dbid != 1"
0
 
gyansAuthor Commented:
The sql will look something like this then:

isql -Usa -P -o killemall.sql << EOF
set nocount on
select "kill " + convert(char(5), spid) + char(10) + "go" as "--" from master..sysprocesses where dbid ! = 1
go


what is killemall.sql
, thanks.
I have allocated 500 points only so that entire process is clear and correct.

thanks for helping,
0
 
bretCommented:
"killemall.sql" is a sql script file that is created by the first isql session and then executed by the
second isql session.  You can change the name to any legal file name, I just like the phrase "Kill 'em all" .  It's contents will depend on the contents of sysprocesses when the file is run, but should look something like this:


--
kill 13
go
kill 15
go
kill 16
go
0
 
gyansAuthor Commented:
So it should be lie this:
The script is as following:
isql -Usa -P -o killemall.sql << EOF
set nocount on
select "kill " + convert(char(5), spid) + char(10) + "go" as "--" from master..sysprocesses where dbid ! = 1
go
EOF
isql -Usa -P -i killemall.sql

Call it killsql.sh and put this in cron,it should work then ,hope I am placing it correct.
thanks

0
 
bretCommented:
Yes.  You probably want to add a "shutdown" command to the cron job as well, so

isql -Usa -P -o killemall.sql << EOF
set nocount on
select "kill " + convert(char(5), spid) + char(10) + "go" as "--" from master..sysprocesses where dbid ! = 1
print "shutdown"
print "go"
go
EOF
isql -Usa -P -i killemall.sql
0
 
gyansAuthor Commented:
isql -Usa -Ssybcps4 -Pxxxx -o killemall.sql << EOF
set nocount on
select "kill " + convert(char(5), spid) + char(10) + "go"   from master..sysproc
esses where dbid ! = 1
go
EOF
isql -Usa -Pxxxxx -Ssybcps4 -i killemall.sql

*****************************************

I ran the above script and got the following error:


Msg 102, Level 15, State 1:
Line 2:
Incorrect syntax near '-'.
thanks for helping
0
 
bretCommented:
Does that error come from the first or the second invocation of ISQL?

I suspect the second, in which case what does killemall.sql contain
(particularly line 2)?

0
 
gyansAuthor Commented:
yes this is what killemall.sql contains:

 -------------
 kill 1
go


thanks for helping
0
 
bretCommented:
Hm.  Must be something about the old version - apparently
it doesn't accept "--" as a beginning comment marker?  It has
been about 8 years since I worked much on 4.9.x

Anyway, it should work to just get rid of the "-------"
line.  So try rewriting the script as...


isql -Usa -Ssybcps4 -Pxxxx -o killemall.sql << EOF
set nocount on
select "kill " + convert(char(5), spid) + char(10) + "go"   from master..sysproc
esses where dbid ! = 1
go
EOF
tail +2 killemall.sql | isql -Usa -Pxxxxx -Ssybcps4
0
 
gyansAuthor Commented:
Output of the current sql is as following:

 -------------
 kill 1
go


We get the same error:

Msg 102, Level 15, State 1:
Line 1:
Incorrect syntax near '-'.

thanks
0
 
bretCommented:
what do you get for

tail +2 killemall.sql

?
0
 
gyansAuthor Commented:
-------------
 kill 1
go
0
 
bretCommented:
Try adjusting the "+2" value until the line of hyphens is removed.

Or there may be some syntax differences between the "tail" command
on your system and on mine - check the man page for tail for the correct
syntax to remove the first couple of lines and leave the rest.

-bret
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now