CA Client Automation

Expand all | Collapse all

DTSTOS constantly writing to SQL database

  • 1.  DTSTOS constantly writing to SQL database

    Posted Oct 17, 2018 06:21 AM
      |   view attached

    Hi I am hoping someone can help with fixing an issue I believe is with the SQL database for CA

    we are currently running CA ITCM 12.8.1 and over the last few days all our software delivery jobs have failed either with timeout for activation has been reached or with DTS Error: dts error

     

    upon investigating I found that in the logs for DTSTOS is it constantly writing to the SQL database the same information just in new rows,

     

    171018-08:29:25.7776488L|016060|000011e4|dtstos    |CDb             |CDb.cpp             |000520|DETAIL | Execute SQL Script command pointer
    171018-08:29:25.7776699L|016060|000011e4|dtstos    |DbImpl_Ado      |DbImpl_Ado.cpp      |000610|DETAIL | Execute command pointer
    171018-08:29:25.7777447L|016060|000011e4|dtstos    |CmdPtreImpl_Ado |CmdPtreImpl_Ado.cpp |001113|DETAIL | ExecuteCommandPointer sql: INSERT INTO dts_torproperties (object_id, row_num, object_data) VALUES(132641,23712779,?)
    171018-08:29:25.7777875L|016060|000011e4|dtstos    |CmdPtreImpl_Ado |CmdPtreImpl_Ado.cpp |001127|DETAIL | ExecuteCommandPointer sqlwparam: INSERT INTO dts_torproperties (object_id, row_num, object_data) VALUES(132641,23712779,'')
    171018-08:29:25.7786717L|016060|000011e4|dtstos    |CDb             |CDb.cpp             |001120|DETAIL | Destroy a Command pointer
    171018-08:29:25.7786979L|016060|000011e4|dtstos    |DbImpl_Ado      |DbImpl_Ado.cpp      |002838|DETAIL | Destroy Command Pointer

     

    this appears to cause all software delivery that requires DTS to stall.

    I have killed the DTSTOS process and removed all software delivery jobs and restarted the service which has stopped it writing to the database but has caused the database to rollback that transaction which appear it will take hours.

     

    This dts_torproperties table contains about 23 millions rows where as a couple of months ago only contained 200

    the only thing that has really changed is we added 600 scalability servers due to the numerous locations of the agents and the speed of the networks.

     

    what i'm asking is if someone can point me in a direction to resolving this issue I am having with DTS, maybe if I can find out what object_id 132641 is.

     

    any help would be much appreciated.

    Attachment(s)

    zip
    TRC_DTSTOS_4.log.zip   1.46 MB 1 version


  • 2.  Re: DTSTOS constantly writing to SQL database

    Broadcom Employee
    Posted Oct 17, 2018 08:11 AM

    Are you using dts to the end points for software delivery.  You should not be.

     

    Richard Lechner

    Principal Engineering Services Architect

     

    CA Technologies

    Mobile: +1 703 655 7161 | Richard.Lechner@ca.com

     

    <mailto:Richard.Lechner@ca.com>[CA]<http://www.ca.com/us/default.aspx>[Twitter]<http://twitter.com/CAInc>[Slideshare]<http://www.slideshare.net/cainc>[Facebook]<https://www.facebook.com/CATechnologies>[YouTube]<http://www.youtube.com/user/catechnologies>[LinkedIn]<http://www.linkedin.com/company/1372?goback=.cps_1244823420724_1>[Google]<https://plus.google.com/CATechnologies>[Google+]<http://www.ca.com/us/rss.aspx?intcmp=footernav>



  • 3.  Re: DTSTOS constantly writing to SQL database

    Posted Oct 17, 2018 08:22 AM

    No we are only using DTS for the transfer from Domain manager to scalability server and use Internal - NOS to the agents



  • 4.  Re: DTSTOS constantly writing to SQL database

    Broadcom Employee
    Posted Oct 17, 2018 09:45 AM

    Best open a support issue.

     

    Richard Lechner

    Principal Engineering Services Architect

     

    CA Technologies

    Mobile: +1 703 655 7161 | Richard.Lechner@ca.com

     

    <mailto:Richard.Lechner@ca.com>[CA]<http://www.ca.com/us/default.aspx>[Twitter]<http://twitter.com/CAInc>[Slideshare]<http://www.slideshare.net/cainc>[Facebook]<https://www.facebook.com/CATechnologies>[YouTube]<http://www.youtube.com/user/catechnologies>[LinkedIn]<http://www.linkedin.com/company/1372?goback=.cps_1244823420724_1>[Google]<https://plus.google.com/CATechnologies>[Google+]<http://www.ca.com/us/rss.aspx?intcmp=footernav>



  • 5.  Re: DTSTOS constantly writing to SQL database

    Broadcom Employee
    Posted Oct 17, 2018 10:17 AM

    This is known issue and open support.case and request for fix.

     

    Thanks and regards

    Ramesh



  • 6.  Re: DTSTOS constantly writing to SQL database

    Broadcom Employee
    Posted Oct 17, 2018 12:50 PM

    PRODUCT: Desktop and Server Management                   RELEASE: 12.8

     

    PROB #:  3136      FN: T437191    FT: D5J        DATE:    07 Mar 2014

    STARTRAK PRODUCT NAME: DTSVMG (Star Problem Product)

     

    PROBLEM SYMPTOM: WIN-STAGING JOB FAILS



  • 7.  Re: DTSTOS constantly writing to SQL database

    Posted Oct 17, 2018 01:04 PM

    Ahhh... silly 12.8 

     

    Time to upgrade!  End of this month, ITCM 12.9 goes EOS.  Time to make plans to get to r14 SP2!



  • 8.  Re: DTSTOS constantly writing to SQL database

    Posted Oct 18, 2018 04:17 AM

    we are looking at upgrading in the next month or so to R14 SP2, in the meantime I will request that fix via a support case.

     

    thank you everyone



  • 9.  Re: DTSTOS constantly writing to SQL database
    Best Answer

    Posted Oct 17, 2018 10:31 AM

    Hi Mark,

     

    We need to perform a cleanup of DTS and reinit the DTS tables in the database...

     

    Download the latest WinOffline from here:

    Latest WinOffline -- 2018.10.04 

     

    On the command line, run:

    WinOffline.exe -cleanlogs -cleanserver -skipcafstartup

     

    WinOffline will leave CAF stopped, and then run this command to clean the DTS database tables:

    dtdbinst -clean

     

    It's possible with an excess of 23 million rows, this may require manual intervention.  We shall see.  Check the table you mention after the cleanup completes, and see if the row count has been significantly reduced.

     

    Once the cleanup is completed, start CAF.

     

    -Brian



  • 10.  Re: DTSTOS constantly writing to SQL database

    Posted Oct 18, 2018 04:16 AM

    Hi Brian,

     

    This seems to have fixed it, DTS transfers are running and the rows are back down to 206.

    going to keep an eye on it for the rest of the day and hope it remains fixed.



  • 11.  Re: DTSTOS constantly writing to SQL database

    Posted Oct 18, 2018 10:44 AM

    Awesome, thanks for confirming. I had my doubts about the dtdbinst being able to cleanup that many rows.  Sounds like you just need fix T437191 to prevent it from happening again.  And hopefully an upgrade, in the near term, as you mention!