January 13, 2010 at 7:27 am
Can any body tell me please
How do you display the last executed batch or command in the SQL Server 2000 and 2005 database?
Thanks
Irfan
January 13, 2010 at 7:33 am
Run a server-side trace, and query that.
Look up sp_trace_create in Books Online for details on how to do that.
Here's a sample:
declare @ID int, @FS bigint, @FilePath nvarchar(245), @1 bit
select @FS = 100,
@filepath = 'F:\Trace\TSQLTrace',
@1 = 1
exec sp_trace_create
@traceid = @id output, -- Trace ID (output)
@options = 2, -- File rollover option
@tracefile = @filepath, -- Must be a local path on the server
@maxfilesize = @FS, -- Trace File Size (in Meg)
@stoptime = null, -- No predetermined stop time
@filecount = 100 -- Max number of trace files (rolls over)
-- 10 = RPC Complete
-- Sets columns for trace data
exec sp_trace_setevent @id, 10, 1, @1
exec sp_trace_setevent @id, 10, 3, @1
exec sp_trace_setevent @id, 10, 13, @1
exec sp_trace_setevent @id, 10, 15, @1
exec sp_trace_setevent @id, 10, 16, @1
exec sp_trace_setevent @id, 10, 17, @1
exec sp_trace_setevent @id, 10, 18, @1
-- 12 = SQL Batch Complete
-- Sets columns for trace data
exec sp_trace_setevent @id, 12, 1, @1
exec sp_trace_setevent @id, 12, 3, @1
exec sp_trace_setevent @id, 12, 13, @1
exec sp_trace_setevent @id, 12, 15, @1
exec sp_trace_setevent @id, 12, 16, @1
exec sp_trace_setevent @id, 12, 17, @1
exec sp_trace_setevent @id, 12, 18, @1
-- Which database to trace (if not set, will trace all)
exec sp_trace_setfilter @id, 35, 0, 0, N'MyDB'
-- Turns on the trace
exec sp_trace_setstatus @id, 1
-- Trace summary data (numeric codes, not particularly human-readable)
select *
from fn_trace_getinfo(@id)
--Trace detail data
select *
from sys.traces
where id = @id
--exec sp_trace_setstatus 2, 0 -- Stop the trace
--exec sp_trace_setstatus 2, 2 -- Delete the trace
--exec sp_trace_setstatus 2, 1 -- Restart the trace
--select * from fn_trace_getinfo(0) -- Select data on all active traces
--select * from fn_trace_gettable ( filename , number_files )
-- To select data from the trace while running.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 13, 2010 at 7:59 am
Thank you sir but i am not good in T-SQL/declaring variables.I would like to have some thing short like one SP which i can run and get required information or if there is any trace i can create in Profiler.
January 13, 2010 at 1:21 pm
The above code from Gsquared creates a Profiler Trace at the location specified. Also, you do not have to pass any values to the variables declared.
Just change the name of the database to the one on which you want to create a trace.
Also, you can convert the above sql to a Stored Procedure.
HTH...
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
January 13, 2010 at 2:39 pm
Ok,The above code will give me the last executed batch/command not the current running right?
and how to stop this trace because in profiler i know if you create any i run any trace i have to stop it or mentioned the time when it have to stop.
January 13, 2010 at 2:44 pm
If you look at the bottom of the script I posted, there are commands commented out that will stop the trace, query the trace, get rid of the trace, and other actions. All you have to do is uncomment them and add in the trace ID that you want to stop.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 13, 2010 at 2:57 pm
I did,t pay attention in the commented areas in the bottom but before reading your reply i read and came to know how to stop=0/delete=2/restart=1 the race.
Thank you very much Sir for your quick response and great help.
Irfan
January 14, 2010 at 6:52 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 14, 2010 at 7:33 am
Thank you 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply