Discussion in 'SQL Server 2005 General DBA Questions' started by kumaravel v, Sep 22, 2008.
capture VM performance counters and check for any memory errors in error log and windows logs
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...
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.
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.
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.
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.
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
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.
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.
Separate names with a comma.