asked on

SQL Server - reporting on locked tables

We have an Enterprise Content Mgmt app connected to SQL 2008 R2. Lately we've encountered locked tables. Is it possible to run historical reports on previous locks, or can this only be obtained at the time the table is locked?

I'm aware of sp_who2 but as far as I know that's only helpful at the time of the lock. We're trying to gather information on performance issues from last week.

Thanks in advance.

chaau

You can use the xp_readerrorlog. I recommend you use a temp table:

CREATE TABLE #Errors 
  ( 
     Logdate VARCHAR(23),ProcessInfo VARCHAR(12),MsgText VARCHAR(3000) 
  ) 

INSERT #Errors 
EXEC xp_readerrorlog 
  0 

INSERT #Errors 
EXEC xp_readerrorlog 
  1 

INSERT #Errors 
EXEC xp_readerrorlog 
  2 

CREATE INDEX idx_temp1 
  ON #Errors(Logdate /* , MsgText */) 

select * FROM #Errors WHERE CAST(Logdate as DATETIME) >= GETDATE() - 7

Open in new window

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vitor Montalvão

Is it possible to run historical reports on previous locks, or can this only be obtained at the time the table is locked?

It's only possible when it happens unless you have some kind of monitoring tool active (SCOM for example) or a profiler running constantly on the instance.
Do you know when the locks usually happens? If so you can set a trace to run during that period to try to capture the locks.