SQL Trace for past queries

Question

Is it possible to use SQL 2005 Server profiler to run a trace for queries that happened for example 6 hours ago? Or is trace only used for real time? I'm trying to find a way to view old TSQL queries. Is this possible with SQL 2005?

Answer 1

Simple question, simple answer: No. This is realtime.

Answer 2

Trace is real time only, although if you want to do some auditing on your database in the future, you might want to set up triggers. http://msdn.microsoft.com/en-us/library/ms189799.aspx

Answer 3

Profiler captures realtime events, so past events are gone.

You can have some past informations looking into the procedure cache, but this isn't the complete log of queries:

SELECT [cp].[refcounts] 
, [cp].[usecounts] 
, [cp].[objtype] 
, [st].[dbid] 
, [st].[objectid] 
, [st].[text] 
, [qp].[query_plan] 
FROM sys.dm_exec_cached_plans cp 
CROSS APPLY sys.dm_exec_sql_text ( cp.plan_handle ) st 
CROSS APPLY sys.dm_exec_query_plan ( cp.plan_handle ) qp ;