Red Gate forums :: View topic - Deadlocks not being captured
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Monitor 2
SQL Monitor 2 forum

Deadlocks not being captured

Search in SQL Monitor 2 forum
Post new topic   Reply to topic
Jump to:  
Author Message
ScottHerbert



Joined: 07 Oct 2011
Posts: 13
Location: Canberra

PostPosted: Fri Mar 23, 2012 3:27 am    Post subject: Deadlocks not being captured Reply with quote

I'm running SQL Monitor against several environments.

In our test/dev envs, I'm getting alerts when deadlocks occur.

However in prod, I can see deadlocks in the SQL Logs, but no alert in SQL Mon.
Back to top
View user's profile Send private message
Chris Spencer



Joined: 29 Aug 2006
Posts: 301
Location: Red Gate - Cambridge

PostPosted: Fri Mar 23, 2012 1:46 pm    Post subject: Reply with quote

Hi

Are there any warnings/errors in the 'Show Log' page linked from the Monitored Servers page for each server? As SQL Monitor gets its deadlock information from the SQL error logs, and deadlocks are definitely appearing there, it suggests that the software might be having problems accessing the logs. Maybe due to a permission issue as we use remote file access to read the SQL Logs. If errors do exist we have a troubleshooting page that includes details on testing remote file access:
http://www.red-gate.com/supportcenter/Content?p=SQL%20Monitor&c=SQL_Monitor/help/3.0/SM_Testing_data_collection.htm&toc=SQL_Monitor/help/3.0/toc1469857.htm

Another possibility - is the 1204 trace flag enabled for the servers in question? This is a necessity for deadlock alerting and SQL Monitor will attempt to enable it automatically and send an alert if it is unable to do so. Currently we only use trace flag 1204 but it might be trace flag 1222 that is creating the SQL Log entries on your servers? The DBCC TRACESTATUS command can be used to find the status of any trace flag
http://msdn.microsoft.com/en-us/library/ms187809.aspx

Regards
Chris
_________________
Chris Spencer
Test Engineer
Red Gate
Back to top
View user's profile Send private message
ScottHerbert



Joined: 07 Oct 2011
Posts: 13
Location: Canberra

PostPosted: Sun Mar 25, 2012 11:09 pm    Post subject: Reply with quote

Quote:
Are there any warnings/errors in the 'Show Log' page linked from the Monitored Servers page for each server?


I forced a deadlock, and there are no errors shown in the Show log page at all for that server. However I do note in the SQL Log itself that amongst the many lines of info for the deadlock that occurred, there's an entry that says "Log viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null Values.. Content:"

Quote:
As SQL Monitor gets its deadlock information from the SQL error logs, and deadlocks are definitely appearing there, it suggests that the software might be having problems accessing the logs. Maybe due to a permission issue as we use remote file access to read the SQL Logs. If errors do exist we have a troubleshooting page that includes details on testing remote file access:


I think a permissions issue is unlikely as the the SQL Monitor service account is a member of the Administrators group on the monitored server. Also, I've not noticed any other alerts not appearing; for example high processor usage alerts are popping up as expected.

Quote:
Another possibility - is the 1204 trace flag enabled for the servers in question?


Yes, it's enabled.
Back to top
View user's profile Send private message
Chris Spencer



Joined: 29 Aug 2006
Posts: 301
Location: Red Gate - Cambridge

PostPosted: Tue Mar 27, 2012 10:55 am    Post subject: Reply with quote

Quote:
"Log viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null Values.. Content:"
I can't find any good information about this error online but I'd be surprised if it was related to SQL Monitor not creating deadlock alerts.

Are deadlock alerts are definitely enabled for this specific server in the Alert Settings page?

Also could we have a few details of the server in question so that I can run some tests? For example, sql server version, clustered or standalone etc.

Regards
Chris
_________________
Chris Spencer
Test Engineer
Red Gate
Back to top
View user's profile Send private message
ScottHerbert



Joined: 07 Oct 2011
Posts: 13
Location: Canberra

PostPosted: Thu Mar 29, 2012 12:37 am    Post subject: Reply with quote

Quote:
I can't find any good information about this error online but I'd be surprised if it was related to SQL Monitor not creating deadlock alerts.


Unfortunately I think it's barking up the wrong tree, as I just forced a deadlock on my LAB box, got the same weird message in the LAB SQL Log, but happily rec'd an alert in SQL Monitor.

Quote:
Are deadlock alerts are definitely enabled for this specific server in the Alert Settings page?


Deadlock alerts are definitely enabled: we haven't really played at all with alert levels or en/disabling.

Quote:
Also could we have a few details of the server in question so that I can run some tests? For example, sql server version, clustered or standalone etc.


SQL Server 2008 R2, Clustered active/active with 3 instances.
The SQL Monitor service account is admin on the machine and has sysadmin in SQL.

I've also noticed the lack of all other alerts for one of the instances on the cluster.
Back to top
View user's profile Send private message
ScottHerbert



Joined: 07 Oct 2011
Posts: 13
Location: Canberra

PostPosted: Thu Mar 29, 2012 12:55 am    Post subject: Reply with quote

Interestingly, when i removed and re-added the server in SQL Monitor, then forced a deadlock, the alert started appearing.

So it looks to me to be a bug with SQL monitor.
Back to top
View user's profile Send private message
Chris Spencer



Joined: 29 Aug 2006
Posts: 301
Location: Red Gate - Cambridge

PostPosted: Thu Mar 29, 2012 10:49 am    Post subject: Reply with quote

I'm going to do some cluster-specific testing and will let you know if I find anything.

Would it be possible for you to email the SQL Monitor log files to chris.spencer@red-gate.com? - specifically the files that start with "Base Deployment".

Please see the following article on where to find your log files:
http://www.red-gate.com/supportcenter/content/SQL_Monitor/knowledgebase/SM_LogFilesKB

Regards
Chris
_________________
Chris Spencer
Test Engineer
Red Gate
Back to top
View user's profile Send private message
ScottHerbert



Joined: 07 Oct 2011
Posts: 13
Location: Canberra

PostPosted: Fri Mar 30, 2012 12:13 am    Post subject: Reply with quote

Hi Chris,

I've emailed you the logs.

Thanks,
Scott
Back to top
View user's profile Send private message
Chris Spencer



Joined: 29 Aug 2006
Posts: 301
Location: Red Gate - Cambridge

PostPosted: Tue Apr 03, 2012 11:19 am    Post subject: Re: Reply with quote

ScottHerbert wrote:
Hi Chris,

I've emailed you the logs.

Thanks,
Scott


Hi Scott

Unfortunately I don't seemed to have received these log files. Is it possible to try sending them again?

To chris.spencer@red-gate.com

Thanks
Chris
_________________
Chris Spencer
Test Engineer
Red Gate
Back to top
View user's profile Send private message
ScottHerbert



Joined: 07 Oct 2011
Posts: 13
Location: Canberra

PostPosted: Tue Apr 03, 2012 11:35 pm    Post subject: Reply with quote

I've re-emailed, from Scott dot Herbert at arc dot gov dot au
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group