February 14, 2012 at 3:01 am
Can any one tell me Is there any way to find out whether a particular table is getting effected by any of the SQL job?
February 14, 2012 at 3:19 am
What do you mean with "effected"?
Modified?
Currently locked?
...?
-- Gianluca Sartori
February 14, 2012 at 3:28 am
Sorry for the Confusion
I am getting a time out issue when I am running my SSIS package through SQL Job. In the SSIS package I have a simple Select statement which inserts the data into destination table. But when I run the same select statement in SSMS, it is executing with in 2 sec. So I just want to know whether the same table is involved in one or more jobs running at the same time.
February 14, 2012 at 3:37 am
You could quey locks being held on the table in sys.dm_tran_locks.
I would not re-invent the wheel, however, I would rather pick a ready-made tool like Adam Machanic sp_WhoIsActive.
With that procedure, you can easily find what blocks the SSIS package (given it is a blocking issue).
-- Gianluca Sartori
February 14, 2012 at 4:57 am
I am new to SQL.Can you please elaborate the steps I need to do? Thanks in Advance.
February 14, 2012 at 5:08 am
1. Download sp_WhoIasActive
2. Run the script to create the stored procedure
3. Start the SSIS package via job
4. Open a new query editor window in SSMS and type:
EXEC sp_WhoIsActive @get_outer_command = 1, @find_block_leaders = 1
You should see a list of processes running. One of those processes is the SSIS package. You will identify it from the "program_name" column.
If this is a blocking issue, the row corresponding to the SSIS package should have a SPID number in the "blocking_session_id" column.
-- Gianluca Sartori
February 14, 2012 at 5:09 am
chandrika.r 91171 (2/14/2012)
Sorry for the ConfusionI am getting a time out issue when I am running my SSIS package through SQL Job. In the SSIS package I have a simple Select statement which inserts the data into destination table. But when I run the same select statement in SSMS, it is executing with in 2 sec. So I just want to know whether the same table is involved in one or more jobs running at the same time.
Also activate logging in your SSIS package, so that you might get a clue where it is hanging.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 14, 2012 at 5:18 am
Hi Keon,
I activated the logging in SSIS Package. I came to know where it is hanging. Its trying to execute a Select statement which is joined with Some other tables. But when I take out the query and executes in SSMS, it works fine. So what my assumption is may be when the execution process comes to that point in SSIS, some other transaction is running on the Same table which is making the select Query execute for a longer time resulting in the time out issue. So I want to know what is the job that got initiated at the same time which uses the table which is used in SSIS package.
Sorry if there are any confusions..Please reply
February 14, 2012 at 6:55 am
chandrika.r 91171 (2/14/2012) via Private Message
Thats fine but still did not get the solution Gainluca. lets say I have some 100 jobs scheduled at the same time. one table is used in the code by the 2 jobs out of 100. How to know the two different jobs which modifies the table. Please suggest.
You will find the job id in the "program name" connection property.
The Job name can be retrieved from msdb.dbo.sysjobs.
Hope this helps
Gianluca
-- Gianluca Sartori
February 14, 2012 at 7:35 am
Thank you. It worked.
February 14, 2012 at 8:38 am
You're welcome.
Glad I could help.
-- Gianluca Sartori
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply