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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You can use the xp_readerrorlog. I recommend you use a temp table:
     Logdate VARCHAR(23),ProcessInfo VARCHAR(12),MsgText VARCHAR(3000) 

INSERT #Errors 
EXEC xp_readerrorlog 

INSERT #Errors 
EXEC xp_readerrorlog 

INSERT #Errors 
EXEC xp_readerrorlog 

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

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

Open in new window

Scott PletcherSenior DBACommented:
SQL Server wouldn't keep a history of table locks, thus, no, you wouldn't be able to report on previous locking, as you suspected.

You can set trace flags to help you capture deadlock info, but that's a specific situation.

You could run something periodically to capture data from sys.dm_tran_locks, but even that would be only whatever locks happened to be active at the time.

If you know in advance of when a situation might come up again, you could start Profiler in advance and allow it to run during that time.  You could then analyze its output.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.