Post reply

SQL 2005 job

  • July 17, 2008 at 12:16 pm

    #122416

    Hi

    I have very basic and general question.

    SQL 2005 agent job is scheduled to run automatically and it uses lot of SPs under certain account

    There should be only one SPID for that job right?

    Or that job can issue multiple SPIds??

    Thanks

  • July 17, 2008 at 12:37 pm

    #844597

    If you have multiple steps in a job, each step will establish a new connection. They may or may not be issued the same spid.

  • July 17, 2008 at 12:42 pm

    #844600

    because when my job(with multiple steps ) runs and at same time so many end users are there and they are creating blocking with that job.

    i want to know my job blocks end users or end users blocks the job?

    if each step has different spid then how can i monitor or compare?any ideads?

    thanks

  • July 17, 2008 at 12:45 pm

    #844602

    Assuming your job agent is connecting with a different username, it should be fairly easy to see if that user is blocking others just by looking at the activity monitor.

  • July 17, 2008 at 12:53 pm

    #844610

    right now i can see around 50 different spids for same login during whole period of job.

    and i can see some other reportng users with different logins and they are not blocking

    and results of performance counters says its blocking. is there any way i can see blocked and bloked by spids so i can check exactly what is blocking?

    Thanks

  • July 17, 2008 at 12:58 pm

    #844619

    Assuming the steps are all Execute SQL steps in your job, each step will get 1 spid. Since the steps have to be sequential, each connection will close before the next one opens. So, if you are seeing more then one connection open from your job at the same time you have either missed something in your description of the job, or the connections are coming from somewhere else.

  • July 17, 2008 at 1:01 pm

    #844621

    Try this Query..

    select hostname,loginame,DB_NAME(dbid),* from sys.sysprocesses

    where blocked<>0

    and spid >51

    order by last_batch desc

    Maninder
    www.dbanation.com

  • July 17, 2008 at 1:05 pm

    #844625

    ya i m looking at snapshot captured at every 2 minutes. we have some kind of template that generates this snapshot.

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

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