Processes can not be terminated on Sybase ASE

sybasesybase-ase-15.7

In 2 days, 1494 processes can not be terminated. This transactions, select data from IQ from ASE using by services. IQ was stopped yesterday and 1494 processes was occured. I killed kill spid but it was not terminated. what should i do to close, kill or stop these processes? I increase a parameter sp_configure 'max cis remote connections',1800 because service can not retrieve data from IQ to ASE… What should i do? Thanks

CpuTime     SPID     DBName     column4     HostName               BlockingSPID     SecondsWaiting     WaitTime     MemUsageKB     SQLText                                                                                                                                                                                                                                             RowsAffected     StartTime               
 ----------  -------  ---------  ----------  ---------------------  ---------------  -----------------  -----------  -------------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ---------------  ----------------------- 
 356         3247     tempdb     my2u      weblogic2.localdomain  (null)           91339              91519500     54             DYNAMIC_SQL dyn145: create proc dyn145 as select x, y from mytable  where ID = 55728914                                                                                                                            0                2018-09-24 09:53:10.843 
 56          3819     tempdb     my2u      weblogic2.localdomain  (null)           91491              91671600     48             DYNAMIC_SQL dyn138: create proc dyn138 as select x, y from mytable  where ID = 4572954                                                                                                                             0                2018-09-24 09:50:39.043 
 55          4124     tempdb     my2u      weblogic1.localdomain  (null)           90587              90767900     68             DYNAMIC_SQL dyn156:                                                                                                                                                                                                                                 0                2018-09-24 10:05:42.746 
 55          4169     tempdb     my2u      weblogic2.localdomain  (null)           91570              91750000     70             DYNAMIC_SQL dyn246:                                                                                                                                                                                                                                 0                2018-09-24 09:49:20.646 
 54          4681     tempdb     my2u      weblogic3.localdomain  (null)           90077              90257500     68             DYNAMIC_SQL dyn156:                                                                                                                                                                                                                                 0                2018-09-24 10:14:13.146 
 54          3621     tempdb     my2u      weblogic2.localdomain  (null)           92437              92617400     70             DYNAMIC_SQL dyn162:                                                                                                                                                                                                                                 0                2018-09-24 09:34:53.243 
 54          5083     tempdb     my2u      weblogic3.localdomain  (null)           89846              90026700     82             DYNAMIC_SQL dyn656:                                                                                                                                                                                                                                 0                2018-09-24 10:18:03.946 
 53          3158     tempdb     my2u      weblogic2.localdomain  (null)           91798              91978800     48             DYNAMIC_SQL dyn138: create proc dyn138 as select x, y from mytable  where ID = 63213   
...


sp_who  3247
fid spid    status  loginame    origname    hostname    blk_spid    dbname  tempdbname  cmd block_xloid
  0 3247    terminating my2u    my2u    weblogic2.localdomain          0    MYDB    tempdb  SELECT            0

I realized that my tran logs increasing from 1 Mb to 500 Mb… syslogshold result:

dbid     reserved     spid     page      xactid        masterxactid     starttime                name                                                                 xloid    
 -------  -----------  -------  --------  ------------  ---------------  -----------------------  -------------------------------------------------------------------  -------- 
 7        0            0        1129      000000000000  000000000000     2013-03-24 17:36:13.36   $replication_truncation_point                                        0        
 9        0            3995     41965187  02805683000b  000000000000     2018-09-24 10:24:13.546  $chained_transaction                                                 7990     
 9        0            0        41965187  000000000000  000000000000     2018-09-25 12:47:15.303  $replication_truncation_point                                        0  

select object_name (id),* from master..syslocks where spid = 3995
GO
id  dbid    page    type    spid    class   fid context row loid    partitionid nodeid
259567215   9   0   4   3995    Non Cursor Lock                 0   0   0   7990    0   (null)
546266866   9   0   4   3995    Non Cursor Lock                 0   0   0   7990    0   (null)
451567899   9   0   4   3995    Non Cursor Lock                 0   0   0   7990    0   (null)
579568355   9   0   4   3995    Non Cursor Lock                 0   0   0   7990    0   (null)
1676634085  9   0   4   3995    Non Cursor Lock                 0   0   0   7990    0   (null)

Best Answer

You can check the status of a kill command with statusonly.

For example:

kill 3247 with statusonly

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36272.1600/doc/html/san1393051027962.html

You can also check if any of those spids have open transactions on the syslogshold table:

select * from master..syslogshold

Related Solutions

Sybase ASE 12 – Common Questions and Solutions

Sybase ASE will not allow you to directly delete rows from multiple tables using a wild card, but it's pretty simple to create a script to find the table names, and loop through them.

You can find the tables names by querying sysobjects within the database.

To find all the tables with the prefix Table_ you would do:

select name from myDatabase..sysobjects
where type = "U"
and name like "Table\_%" escape "\"

In the above query "U" is the flag for User defined tables. Also because _ is a single character wild card in T-SQL, we have to escape it to find the literal _. T-SQL allows almost any character to act as the escape, but it must be defined after the like clause. Finally % is the multi-character wild card, the T-SQL equivalent to * in *nix and DOS.

It would also be possible to use the above query to build a sql script. We start by telling the server not to print out the number of rows affected by the query using set nocount. This allows us to create a script that will run error-free, without needing additional editing.

set nocount on    \\supressess rows affected printout
select "delete from " +name+ " where type = 1" + char(10) + "go"
from myDatabase..sysobjects
where type = "U"
and name like "Table\_%" escape "\"
go

If you execute this from isql and using the -b flag to supress headers, and -o flag to redirect output to a file, it will build a script that can then be executed directly.

isql -Uusername -Ppassword -Sservername -b -omyScriptToDeleteData.sql -iSQLToFindTables.sql

Need script for new login creation in sybase ase

There are many, many ways you could handle adding a list of logins/users to Sybase ASE, all of which include a mix of (Unix/Linux) shell scripting and T-SQL coding.

One simple example:

set passs=`cat /cis1/prod/dba/input/. ----`

cat addthese.txt | while read newlogin
do
isql -Usa -SPDSQTS1 <<EO_SQL > /tmp/error.out 2>&1
$passs
use master
go
if suser_id("${newlogin}") is NULL
    exec sp_addlogin ${newlogin},Sybase123
go
----------------------
-- repeat following block for each db of interest (eg, qts_db, engcomdb)
use <db>
go
if user_id("${newlogin}") is NULL
    exec sp_adduser ${newlogin}, ${newlogin}, eng
go
---------------------
-- rest of your coding
go
EO_SQL

... check for errors in /tmp/error.out ...

done

You could place all of the above into a shell script, make sure addthese.txt is correct/up-to-date, and then run the shell script.

Variations on the above could include:

putting the T-SQL code in a stored proc and then calling the stored proc for each new login
placing the above T-SQL code in a template.sql file with NEWLOGIN used as place holder, a shell script would then run sed 's/NEWLOGIN/${newlogin}/g' template.sql > runme.sql followed by isql -i runme.sql > error.out 2>&1

There are many variations based on which shell you're using and your coding style/preferences.

Best Answer

Related Solutions

Sybase ASE 12 – Common Questions and Solutions

Need script for new login creation in sybase ase

Related Question