December 13, 2004 at 12:16 pm
Hello,
2 questions:
1. How to monitor when the lock of a certain mode (exclusive for example) is set on the resource? The profiler does not give me a lock mode. The syslockinfo table does contain all information that I need but it is dynamic, the lock is released - the info is not there anymore. BOL says " SQL Server does not support user-defined triggers on system tables" and I am not in a mood for running a select...insert job every second to pump data into the user table.
2. Did someone had a case when Read-Only user created a deadlock in the database? Someone was using DTS Import/Export Wizard in SQL 2000 to get data out connecting as Read-Only user with the only db_datareader permission set for only one database. The deadlock situation started when another user was trying to run Update using one of the CRM applications.
I was able to catch the Current Activity Processes view and save it as a picture so I am able to make some assumptions, but I am not able to reproduce locking.
Thanks in advance
Yelena
Regards,Yelena Varsha
December 13, 2004 at 1:21 pm
Check this article. Has some good explaination.
December 13, 2004 at 1:26 pm
Is the Lock event in SQL Profiler not specific enough? Try adding the "Mode" data column.
December 13, 2004 at 2:10 pm
Oh, well. It is there. Mode, I mean. Why I did not see it? Is it the time to change the monitor or finally pay attention to what one is doing?
But, seriously, Greg, thanks. It does show what I need, I may even filter by the lock mode.
Sa24: the article is great, especially the compatibility table. I will now check the CRM app if it sets Exclusive, this is the only explanation how it could block read-only Shared. Unless DTS does something more then Shared.
Yelena
Regards,Yelena Varsha
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply