Hec Ramsey
asked on
SQL 2008 R2 Report on historic DeadLocks ? And Deadlock Detect
One caveat -- I imagine this is a common question, I just haven't found anything yet.
I am a dev and frequently lack rights run Sql Profiler, Activity Monitor, can's add a traceflag etc due to rights. I have no 3rd party software.
Is there a log (other than THE log) of these events I can query for historic locks? I have been sniffing through system queries and views and although some look promising I haven't found anything useful.
I found this script that gets CURRENTlock info, put it on a loop to insert to MyLockTable every 1 second, this is clumsy and resource intensive, but it does give me all the info that I need. Is there anything better out there?
I am a dev and frequently lack rights run Sql Profiler, Activity Monitor, can's add a traceflag etc due to rights. I have no 3rd party software.
Is there a log (other than THE log) of these events I can query for historic locks? I have been sniffing through system queries and views and although some look promising I haven't found anything useful.
I found this script that gets CURRENTlock info, put it on a loop to insert to MyLockTable every 1 second, this is clumsy and resource intensive, but it does give me all the info that I need. Is there anything better out there?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
An administrator seriously needs to turn on trace flag 1222 (globally).
DBCC TRACEON ( 1222, -1 )
Even with extended events, I'd still want the trace, as that puts the info into the SQL error log where you can always scrape it out later (assuming you have authority to access the log I guess :-( ).
DBCC TRACEON ( 1222, -1 )
Even with extended events, I'd still want the trace, as that puts the info into the SQL error log where you can always scrape it out later (assuming you have authority to access the log I guess :-( ).
ASKER
I work in a huge company and have 0 influence over how dba do their thing and cannot run profiler.
Assuming all i can do is query data and run non ddl or config procs, is thete anyway to view deadlock info?
Assuming all i can do is query data and run non ddl or config procs, is thete anyway to view deadlock info?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER