• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 11108
  • 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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