How to find latest executed query in sql server 2000

Question

How to find latest executed query in sql server 2000

Rohan -708182

Old Hand

Points: 312
More actions
June 19, 2008 at 3:08 am

#188028

hi ...
How to find latest executed query in sql server 2000
regards ..

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 399364 More actions · Answer 1

First, I'd try posting in the SQL Server 2000 forums, not the 2005 forum.

Second, there isn't an immediate method to do this. There are some options. If you're running a trace, you can see which procedure was executed when. If you turn on the "black box" which is just a trace for the last 30 minutes worth of commands, you can see it there too. If you get a log explorer tool, you can look at the log for a given database. None of these methods are going to be up to the millisecond, the most recently executed procedure.

I'd have to hit the 2000 books online, but you might be able query the procedure cache (I know exactly how in 2005, but it's different). Try looking that up.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

Grant Fritchey (6/19/2008)
I'd have to hit the 2000 books online, but you might be able query the procedure cache (I know exactly how in 2005, but it's different). Try looking that up.

Select * from syscacheobjects

There's not much in there, and there's no time information to say when the query was last run. If you need this info accuratly, you'll have to set up some form of trace.

Grant, was there a "black box" trace on SQL 2000? Where?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 399364 More actions · Answer 3

I can't find the documentation. Here's a support article, but it's not what I meant:

http://support.microsoft.com/kb/281671

And Kalen

http://www.sqlmag.com/Article/ArticleID/16078/sql_server_16078.html

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

Ah. A form of serverside trace.

I thought for a moment that I'd missed a feature like 2005's default trace. :hehe:

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 399364 More actions · Answer 5

No, it's not the same at all. 2005 really is better.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Rohan -708182 Old Hand Points: 312 More actions · Answer 6

Actually i want log the query executed in stored procedure ..

any way of doing it in sql server 2000..

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 7

Profiler or a server side trace.

If you want to see the queries within the procs, you want the sp:StmtCompleted event.

Be aware that if your server is busy the trace file will get large quite fast.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass