Every command blocked
-
Hi
We have an issue that happens on the odd occasion and I am having difficulty trying to find out what is going on.
SQL Server 2008 R2 SP3
Virtual
2 node cluster
Running on Windows Server 2008 R2The problem that is happening is that the application will run something and I suspect that this process is blocking absolutely everything including any query at all that I attempt to run including sp_who2 or any of the dmvs. I am able to log on to the sql server ok and connect to the sql instance ok. But I open a new query and run sp_who2 and the query just continues to execute until the application process completes (can be > 20 minutes) or the application process is killed off. SQL Monitoring of the sql server gets blocked during this time and even our msdb jobs that run sp_whoisactive every minute get blocked. Even a query to check for blocking is blocked so its difficult to investigate. Once the application process is killed, we can get back in but cannot determine what sql was running. The developers are unable to give me much investigating on the application side. The application runs multiple processes for certain tasks. last time the issue happened, we killed off one particular application process and that resolved it but the developers are unable to tell me what that process was doing.
Is there anywhere I can look in to sql server after the incident has occurred to try to narrow it down? This issue happens only on the odd occasion, could be a month between occurrences.
Cheers
-
Further to this, around the time we get blocked, there is a command for alter server audit and it had a wait type of (30651ms)PREEMPTIVE_XE_CALLBACKEXECUTE. This was collected right before we were blocked out from running anything. I assume that is what caused us to get blocked out but I think the Alter Server Audit command was blocked or waiting by an application process as when we killed the application process, everything was the freed up.
The application runs on a separate server. Nothing besides SQL runs on the SQL Server
-
Burfos,
SQL Server 2008 R2 SP3
Virtual
2 node cluster
Running on Windows Server 2008 R2What vm are you using ?, if you are using vmware, there are some recommendations that you have to take into consideration when running SQL Server.
I remember some, like energy plan, drivers for network adapter and configuration , adjustments in your SQL Server memory, sql server owner policies applied.
best regards,
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply