Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Job not able to trace in sp_who Expand / Collapse
Author
Message
Posted Tuesday, April 15, 2014 12:31 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:38 PM
Points: 132, Visits: 816
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.
Post #1562013
Posted Friday, April 18, 2014 6:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 10:45 AM
Points: 5,572, Visits: 6,356
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1562984
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse