asked on 6/30/2015

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.

Microsoft SQL Server Microsoft SQL Server 2008 Databases

Last Comment

Vitor Montalvão

7/1/2015

chaau

7/1/2015

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

7/1/2015

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Vitor Montalvão

7/1/2015

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.