September 22, 2008 at 6:04 am
a power failure caused me to loose a query I was building for a full day, I ran it just before the power went off at my machine the server was still on is there a way to extract a Select query that ran in the last 24 hrs?
September 22, 2008 at 6:10 am
It might (if you ran it) be in the plan cache. If not, and there wasn't a trace running, then no.
select * from syscacheobjects
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2008 at 6:16 am
ok where would I find syscacheobjects, does not appear to exist??? sql 2000
September 22, 2008 at 6:34 am
Should work. It definitely exists on SQL 2000. Maybe try prefixing with the master DB, though that shouldn't be necessary on SQL 2000, only on 2005 and higher.
select * from master..syscacheobjects
What error are you getting?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2008 at 7:00 am
thank you very much for your help unfortunately the query does not appear in the table I am using a like statement to find the query using one of the field names I gave it, Like '%_hrs%' the strange thing is it found the query I used before I started writing it this morning am I missing something, and how long does the query stay in this table? Thanks again for you help
September 22, 2008 at 7:13 am
Depends how often it's used, how expensive it was to compile, how much memory pressure the server's under and a whole bunch of other stuff. The table's not a list of everything run, it's a view into SQL's cached plan store.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply