Post reply

SSIS Job not able to trace in sp_who

  • April 15, 2014 at 12:31 pm

    #290256

    Hi All,

    I am running an SQL agent Job which intern run the SSIS package which imports data to one of the database,but when i run the sp_who2 i am unable to view that process SPID.

    How do we trace this.

  • April 18, 2014 at 6:06 am

    #1707029

    Are you running the job and package on the same server as the database? That's the only reason why I can think you might not see the SPID. You may be looking on the wrong server.

    Otherwise, you may be looking for the wrong thing. What exactly are you looking for when you search for the SPID? It might behoove you to use a filtered sp_who2 rather than run the whole thing and look through the randomly sorted lines.

    DECLARE @Table TABLE(

    SPID INT,

    Status VARCHAR(MAX),

    LOGIN VARCHAR(MAX),

    HostName VARCHAR(MAX),

    BlkBy VARCHAR(MAX),

    DBName VARCHAR(MAX),

    Command VARCHAR(MAX),

    CPUTime INT,

    DiskIO INT,

    LastBatch VARCHAR(MAX),

    ProgramName VARCHAR(MAX),

    SPID_1 INT,

    REQUESTID INT

    )

    INSERT INTO @Table EXEC sp_who2

    SELECT * FROM @Table

    --WHERE --ProgramName LIKE 'Microsoft%' AND

    --LOGIN NOT LIKE '%\%'

    ORDER BY LOGIN DESC, HostName;

    I use various WHERE conditions to check for ProgramName or Logins (the '%\%' bit excludes Windows logins that have the domain names with them). Anyway, using these conditions makes it so much easier for me to find something specific.

    Also, your load may be happening so fast that you can't catch it with sp_who2 simply because the process is done by the time you get your data back.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 2 posts - 1 through 1 (of 1 total)

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