Insert Exec running forever

Question

Post reply

Insert Exec running forever

Kameron Hott-353319

SSC Rookie

Points: 31
More actions
July 27, 2006 at 5:14 pm

#153112

Hello all,
Ok, I am having an issue where I am using Insert Exec and it runs on and on and never ends but if I run the Exec part by itself it runs in less than a second. Example below:
Create Table #tmpReport2(
             JobID              varchar(200),
             JobServer          varchar(200),
             JobName            varchar(200),
             JobEnabled         int,
             JobDescription     varchar(200),
             JobStartStep       int,
             JobCategory        varchar(200),
             JobOwner           varchar(200),
             JobNotifyEventlog int,
             JobNotifyEmail     int,
             JobNotifyNetsend   int,
             JobNotifyPage      int,
             JobEmailOperator   varchar(200),
             JobNetsendOperator varchar(200),
             JobPageOperator    varchar(200),
             JobDeleteLevel     int,
             JobCreateDate      varchar(30),
             JobModifyDate      varchar(30),
             JobVersion         int,
             JobLastRunDate     varchar(10),
             JobLastRunTime     varchar(10),
             JobLastOutcome     int,
             JobNextRunDate     varchar(10),
             JobNextRunTime     varchar(10),
             JobNextRunID       int,
             JobCurrentStatus   int,
             JobCurrentStep     varchar(20),
             JobCurrentRetry    int,
             JobHasStep         int,
             JobHasSchedule     int,
             JobHasTarget       int,
             JobType            int)
Insert #tmpreport2
       exec OTGETL02.msdb..sp_help_job @job_id = '{EFA62BDF-16DD-4E5C-B4C5-B7953151A349}',
                                     @job_aspect = N'job'

If you run:
exec OTGETL02.msdb..sp_help_job @job_id = '{EFA62BDF-16DD-4E5C-B4C5-B7953151A349}',
                                     @job_aspect = N'job'
It runs quickly. I am very puzzled.
Thank you

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

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

Sergiy SSC Guru Points: 110209 More actions · Answer 1

Distributed transactions.

_____________
Code for TallyGenerator

Kameron Hott-353319 SSC Rookie Points: 31 More actions · Answer 2

Thank Sergiy for the reply. Yes, it is a distributed transaction, but does it have to take forever to run? I am trying to gather the status of all jobs from every server we have here for a report I have to produce each day. Instead of going through each server and manually checking the status of the jobs I was hoping to automate this process. We have about 40 SQL server boxes.

Kameron Hott-353319 SSC Rookie Points: 31 More actions · Answer 3

Anyone else have any ideas that may help me on this? It has to be possible, I would think....

Thanks

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 4

A little off-the-wall but I've run into similar problems with other things... the way I got around it was to run this type of thing on each server with an output to a text file in a common area. Then, I'd do a bulk insert for the files in the common area... nasty fast. A little extra work but well worth it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kameron Hott-353319 SSC Rookie Points: 31 More actions · Answer 5

Thanks Jeff. I guess I am going to have to go that route too. It is not like this on all servers, but just a couple of them and they are also linked. So who knows. Thanks again...

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 6

If it's not slow on all the servers, then you may be having some router or hub problems. For example, if the port and card the server is on are setup to auto-negotiate the speed, it will always choose the worst case. If the duplex is incorrectly set (I forget if it should be full or half), big problems with speed.

Make a list of the "good" and "bad" servers and have your network guys take a peak...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kameron Hott-353319 SSC Rookie Points: 31 More actions · Answer 7

Thanks again. I did go and make a DTS package on each server to put the results in to a single access database for now. And that works rather quickly. But I think I will go ahead and see if the network team can look into this.

Kam