|
Our target environment is MSSQL2008. We are facing some db locking issue in production. The problem is that the system stored procs like sp_who, sp_who2, sp_lock has to be run during the lock occurance to show you the information. Our team for don’t have direct access to production db, and have to go through DBA team to obtain the stored proc results. Is there any way we can retrieve similar locking information to above after the lock has occurred? E.g. if locking occurs 2pm today, and the DBA only available to help query the database at 3pm, is there any where he can still source the information that happened during 2pm lock? |
|
Sort of. You could, for instance, keep a job running which dumps results of those SPs into a table and then have that table made available to you for analysis. I think the simpler options are to either coordinate with the production DBAs so that you can trouble shoot the problem as it's happening or reproduce the contention in a different environment for which you have access. I was afraid you'd say that TG, I was still hoping if there were any other way to tackle this. Thanks for your input anyway. Anyone else have other ideas?
(Feb 23 at 07:08)
Jack Goh
|
|
Found that the following gives good detailed information into the SQL ERRORLOG files. DBCC TRACEON (1222, -1) DBCC TRACEON (1204, -1) At least with this on, you don't have to be "there when it happens". You can get the log and investigate later. |
