SQL AGENT JOB(SQL SERVER 2008 R2)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL AGENT JOB(SQL SERVER 2008 R2)

Author

Topic

kond.mohan
Posting Yak Master

213 Posts

Posted - 2012-08-14 : 08:26:47

Dear all,

we have created SSIS Packages to Fetch the data from Source(Oracle) to Destinaiton(Sql Server 2008 R2)Using that data We have Generated The Reports.Data loading Process is Executing Through SQL AGENT JOB by

step1.LOADING DATA AND CREATING INDEXES AND 50 REPORTS(ssis DATAFLOWTASKS(SOURCE AND DESTIONAIONs ONLY))

step2. 50 Repors(50 DATAFLOWTASKS --sources and Destionaitons only)
)

Step 1 is taking 4hrs everyday
Step 2 is taking 30 MIntues everyday

example step1 finished at 12:30 PM

step 2 is not immediately Starting. today step 2 started at 2:40 Min

Difference time between step1 to Step2 is Taking huge time .

Server Processing(sp_who2) are showing very less.at that time.

anybody knows the solution Explain me

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-08-14 : 08:28:57

look inside the tasks? Maybe there is something glaringly obvious in the sql code (WAITFOR?)

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

kond.mohan
Posting Yak Master

213 Posts

Posted - 2012-08-16 : 05:01:04

Dear Charlie,

i have Checked the SSIS(Data Flow) Tasks.Every Task is having Select Statements with (NOLOCK) Tables. i have not used this kind of (WAITFOR?) code anywhere in my JOB Steps.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-16 : 05:06:05

Log the times taken for each of the tasks.
You will probably find one or two that are taking the time and can deal with that.

If yoou are doing processing inside the package rather than a straight transfer then get rid of it. Do the the processing on Oracle preferably or sql server then yoou have visibility of what is happening.
Make sure you are transferring the minimum data - again do processing oin the remote server to reduce the transfer.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-08-16 : 06:07:09

and at the last stage -- inserting the data. If you are using an OLEDB connection, make sure you are using the advanced mode and have a batch size of more than 1.

That has bitten me hard in the past!

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources