SQL Server Performance

I am getting more 'Waiting for a Worker thread' in JOBS

Discussion in 'SQL Server 2005 General DBA Questions' started by kumaravel v, Sep 22, 2008.

  1. Saurabh Srivastava New Member

    capture VM performance counters and check for any memory errors in error log and windows logs
  2. MohammedU New Member

    The virtual memory (MemToLeave) I was talking is not a page file it is the the memory reservered by the SQL server for different processes.
    This issue comes up only on 32 bit and you are running 64 bit so just ignore...
  3. Saurabh Srivastava New Member

    YES, somehow I overlooked that you are using 64-bit. No need to capture VM counters its ok. Although, if you see any warnings or errors related to max worker threads post it.
  4. kumaravel v New Member

    sure saurabh.
    I will let you know if i get worker threads. As for now, it is going smooth and not found any worker threads.
    Thanks for your assits.
    --kmr
  5. Saurabh Srivastava New Member

    You're always welcome. I'm glad to hear that its woking now. I'm assuming after changing TSQL max worker threads in syssubsystem tables it starts working. If you applied some other solution let us know, please. That would help others if they come across same issue.
  6. kumaravel v New Member

    Hi
    sorry for not sending you reply immediately. After i changed the max worker thereads in syssubsystem table i dont get any status message like waiting for worker thread. The other major changes i made in my job schedule. i,e before completed one job, the same job which activated by other job. Because of this i was getting waiting for worker thread. Now i have solved this issue by using your tips and removed the schedule from jobs.
    I have already ran the alter database isolation command in my sqlserver 2005. Very rarely i am getting Deadlock issue in the job steps. But still i am getting the deadlock issue.
    thanks.
  7. Saurabh Srivastava New Member

    Its good to hear that problem is resolved. I didn't get this "I have already ran the alter database isolation command in my sqlserver 2005" Please give me some more details to help you with deadlock
  8. kumaravel v New Member

    I ran the alter database command, i,e -- To Avoid Deadlock
    ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON
    After run this, i am getting very less deadlocks in my jobs. The below one which i am getting error msg in my jobs while processing. :-
    Executed as user: NT AUTHORITYSYSTEM. Transaction (Process ID 141) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205). The step failed.
    I am not sure how to solve this...
    thanks for your support saurabh.
    -----
  9. Saurabh Srivastava New Member

    Yes, Mohammed brought up a good point. You can also look at VM and mem-to-leave pressure if you are running 32-bit system.

Share This Page