What was happening at time x in my db?

All,

I am a former Oracle developer who is now needing to develop and do
some admin type things in sql server 2005. I am trying to track down
the cause of database timeouts that happen sporadically in a .Net app
due to timeouts occuring with database queries (sp calls and
inserts). I have a simple insert query that occasionally times out.
The query should be very fast (subsecond when run in SSMS usually) My
assumption is that the query is being blocked by something else and
that's why it times out.

My question(finally) is a very general one. How can I best
troubleshoot locks/waits/long running sql that happened sometime in
the past if I was not tracing at the time it happened? The areas I am
currently reading are: Performance views (like
sys.dm_os_waiting_tasks , dm_tran_locks ) but isn't it the case that
the data disappears from these after a certain point, like when query
plans go away?

So, I'm just looking for the preferred method to find out what was
happening on my database at a given past point in time around a
certain event.

Any links or where to start would be great,

thanks in advance

"seanD" wrote in message

> All,
>
> I am a former Oracle developer who is now needing to develop and do
> some admin type things in sql server 2005. I am trying to track down
> the cause of database timeouts that happen sporadically in a .Net app
> due to timeouts occuring with database queries (sp calls and
> inserts). I have a simple insert query that occasionally times out.
> The query should be very fast (subsecond when run in SSMS usually) My
> assumption is that the query is being blocked by something else and
> that's why it times out.
>
> My question(finally) is a very general one. How can I best
> troubleshoot locks/waits/long running sql that happened sometime in
> the past if I was not tracing at the time it happened? The areas I am
> currently reading are: Performance views (like
> sys.dm_os_waiting_tasks , dm_tran_locks ) but isn't it the case that
> the data disappears from these after a certain point, like when query
> plans go away?
>
> So, I'm just looking for the preferred method to find out what was
> happening on my database at a given past point in time around a
> certain event.
>
> Any links or where to start would be great,
>
> thanks in advance

Hi

You may want to look at running a trace or using SQL Profiler, alternatively
look at http://support.microsoft.com/kb/271509

John