0

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)

bumped to the homepage by Community 2 hours ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

0

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
  • Command was executed successfully Warnings: ---> W (1): Status report cannot be obtained. ROLLBACK for spid:3247 is not in progress. <--- – AhmetMelihBaşbuğ Sep 25 '18 at 14:08
  • Please also check the syslogshold table. In addition check if there is any errors or stack traces on the server errorlog. – socaire Sep 25 '18 at 14:31
  • @socaire Kindly note that kill spid with statusonly is available from SP135 or later, doesn't work in initial service packs(I was working in SP110 and wasn't there). Moreover there is issue with this command as it works only on those SPID where rollback is already started and not on active transactions as mentioned here --> launchpad.support.sap.com/#/notes/0002541466 – Learning_DBAdmin Mar 19 at 9:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.