Deadlocks on msdb

Question

Deadlocks on msdb

Emil B

SSCertifiable

Points: 5568
More actions
September 30, 2015 at 2:31 am

#321628

Hi, past 2 day's im getting deadlock alerts from sql monitor
HighDeadlock | 2162718
Raised on:.\(local)
Time raised:30 Sep 2015 1:18 AM (UTC+01)
Details
SPID:80
Victim:Victim process
Lock details:object: msdb.dbo.dbm_monitor_data, lock mode: Intent exclusive (IX), file id: 1, page no. 19688
Fragment:use msdb
exec sys.sp_dbmmonitorresults @database_name=N'Live', @mode = 0, @update_table = 1
SPID:81
Victim:
Lock details:object: msdb.dbo.dbm_monitor_data, lock mode: Intent exclusive (IX), file id: 1, page no. 19688
Fragment:use msdb
exec sys.sp_dbmmonitorresults @database_name=N'Live', @mode = 0, @update_table = 1
SPID:87
Victim:
Lock details:object: msdb.dbo.dbm_monitor_data, lock mode: Shared (S), file id: 1, page no. 19689
Fragment:use msdb
exec sys.sp_dbmmonitorresults @database_name=N'Live', @mode = 0, @update_table = 1
I assume that's something to do with mirroring but what is causing it?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Brandie Tarvin SSC Guru Points: 172995 More actions · Answer 1

Deadlocks are generally caused by two different items hitting the same table / extents / pages at the same time and one of them holding an exclusive lock.

Are you using the monitoring tables for anything? Do you have jobs set up that reference them?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Emil B SSCertifiable Points: 5568 More actions · Answer 2

Brandie Tarvin (9/30/2015)
...Are you using the monitoring tables for anything? Do you have jobs set up that reference them?

After I read this statement i realized that another DBA decided to set up a monitoring job around 2 days ago to check the state of mirroring. Would that nail it?

Emil B SSCertifiable Points: 5568 More actions · Answer 3

There is a job that executes sys.sp_dbmmonitorupdate every minute.

Brandie Tarvin SSC Guru Points: 172995 More actions · Answer 4

Emil B (9/30/2015)
Brandie Tarvin (9/30/2015)
...Are you using the monitoring tables for anything? Do you have jobs set up that reference them?
After I read this statement i realized that another DBA decided to set up a monitoring job around 2 days ago to check the state of mirroring. Would that nail it?

Probably.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.