Sql Server locked tabled

I am thinking this is impossible but I wanted to make sure.

Is there a way for me to know when a table was locked a开发者_运维百科nd maybe for how long? I know that I can see whether a table is currently locked, but I would like to have a "history" of locks.

A "free" alternative to the RedGate tool mentioned in other response, is the MS-SQL Server Profiler (see in Tools menu from SQL Server Management Studio). This tool lets you define "traces" by specifying the type of event you wish to monitor and/or record. There's even [in SQL2008, maybe also in older versions] a default template for locking issues: TSQL_Locks.

Beware that analyzing the profiler's logs may require a bit of work/figuring out. It is possible to filter events based on a particular set of criteria (as well as filtering these at the source, i.e. excluding these from the log in the first place), but third party products such as RedGate's are likely to offer more ease-of-use, better aggregation features etc.

Edit:
(following Metju's remark) The solution suggested above, implies that one would start recording lock-related events in the profiler tool ahead of time. There is nothing, at least nothing publicly documented, in MS-SQL 2005 which would provide access to a complete historical info about the locks, "post facto". However, depending on one's needs, enough insight may sometimes be gathered from the SQL Activity Monitor (from 'Management' in the databases etc. tree on the left, in Management Studio), in particular the "Locks by object" view. This information, which is implicitly available (no need for any setup), can often be sufficient to identify the origins of dead-locks and similar issues.

Check this tool out from Red-Gate. I use it an awful lot for exactly this kind of thing. Plus it lets me check out long running queries and a host of other useful information. There are filters for Last hour, last day, forever etc...

RedGate SQL Response

Without running a monitoring tool (a roll your own, third party, or processing the output of SQL profiler), no there isn't.