Post reply

how to know which spid corresponding to dts job fetches records from other server

  • November 6, 2008 at 1:03 am

    #75356

    hi all

    i am facing a problem i.e i am using one dts package which fetches records from another server ,i want to know which spid corresponding to this dts job in second server. i can trace it through current activity , but i need a query to know this information. any body can help..

    Thanks in advance

    siva

  • November 7, 2008 at 3:54 pm

    #895862

    Executing sp_who2 in Query Analyzer will give you the most of the info in Current Activity

  • December 2, 2009 at 3:14 am

    #1086335

    I'm having a very similar problem... & need to know the SPID of a DTS so that I can kill it...

    been looking at data in: sp_who2 & sysprocesses, anyhow here's the issue... 🙂

    Need to find SPID for a DTS Package to kill it please if exceeds certain time (eg: 4hrs or more - as assume hung)?

    So I can kill it using something like this:

    KILL 54;

    KILL 54 WITH STATUSONLY;

    GO

    Could changing these 2 values in 'sp_configure' help?

    remote login timeout (s) and/or remote query timeout (s)

    currently set to 2,147,483,647secs = 596,523hrs = 24,855days = 68yrs

    Also, how can I match up sysdtspackagelog with sysprocesses for example?

    SELECT [name], starttime, endtime, datediff(ss, starttime, getdate() )/3600.0 as DTS_RUNHRS

    FROM sysdtspackagelog

    where endtime is NULL

    --and datediff(ss, starttime, getdate() )/3600.0 >= 4

    ORDER BY starttime DESC

    name Refresh All Cubes

    starttime 2009-12-02 13:35:32.000

    endtimeNULL

    DTS_RUNHRS0.061111

    select spid, kpid, dbid, status, cpu, cpu/3600.0 as RUNHRS, program_name, login_time, cmd, sql_handle

    from master.dbo.sysprocesses

    where spid>=50 and dbid>0

    --and cpu >= (3600)

    and [program_name] = 'Microsoft SQL Server Analysis Services'

    order by spid

    spid59

    kpid2736

    dbid5

    statusrunnable

    cpu469062

    RUNHRS130.295-- this cant be right???

    program_nameMicrosoft SQL Server Analysis Services

    login_time2009-12-02 13:29:24.703

    cmdSELECT

    sql_handle0x01000500FB024F33C020E8640000000000000000

    Reason behind this:

    The DTS is run via an SQL Server Agent Job which uses Transact-SQL & calls the dts using xp_cmdshell & DTSRUN. However I'm finding that intermittently the job step will hang for hours & cancelling the job manually wont stop the job, have to kill SQL Server instead. From what I've been reading... if the process the xp_cmdshell ran is killed then it will exit & hence my need to find the DTS's SPID so I can kill it. 🙂

  • December 5, 2009 at 7:15 pm

    #1088225

    BUMP... anyone know please?

  • December 12, 2009 at 3:37 am

    #1091504

    BUMP... anyone know please?

  • December 15, 2009 at 5:43 am

    #1092451

    BUMP.... as no replies yet...

    Anyone at all know how to find the SPID for a DTS?

    Any feedback on how I can do it in another way to avoid xp_cmdshell?

    Trying to run commands in a job to run exe's & DTS's BUT need to check value in a table before running them ('scheduler' table) and after commands finished, update the values in the same table (eg: error returned & turn off flag if succeeded). This is where the need for xp_cmdshell came into it, but can't just cancel a job if it's executing xp_cmdshell as it continues to run until xp_cmdshell has complete (which it never does if the DTS is hanging).

  • December 17, 2009 at 3:10 pm

    #1093786

    Log the @@SPID during your package execution

  • December 17, 2009 at 4:09 pm

    #1093809

    Ok taa, but how please?

    * In the TSQL code (xp_cmdshell('...dtsrun...')

    but it never gets past the xp_cmdshell in the code as that's what hangs.

    * in the DTS itself somehow?

    * in a separate script to see what the currently running process is?

    but there'd be more than one running:

    - the sql server agent job (executing the xp_cmdshell)

    - the dts package (called from the xp_cmdshell)

  • December 18, 2009 at 8:41 am

    #1094084

    You can use @@SPID with in the DTS and then log it into you log table with the process name.

    However each connection to SQL Server will result in a differ SPID so as long as all the process used the same connection this could work. Or You can log the SPID before every action with in the DTS

    If you're calling the Dts via xp_cmdshell within a Store Procedure then before you do the call you can log the SPID.

    INSERT INTO "YOUR LOG"

    SELECT @@SPID, "PROCESS NAME", GETDATE()

    Another way is to get the Windows' PID so you can Kill it from Task Manager.

    In the VB script

    Imports System

    Imports System.Diagnostics

    Imports System.ComponentModel

    Dim vCurrentProcess As Process = Process.GetCurrentProcess()

    Dim vPID As In32 = vCurrentProcess.Id

    I am not sure how to return value from a Script to a DTS variable since I've only worked on SSIS, so I don't have that in the above script.

    Good Luck

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply