March 2, 2010 at 2:08 am
hello all!
I was wondering if it is possible to show all the clients who connected to a certain database between specific dates. Do i need to use SQL profiler? or is this logged by default in a system table or something.
thanx!
March 2, 2010 at 5:34 am
There is a default trace which you can look at. There is more information here, here and here.
For example, the following will give you the name of the latest default trace file:
SELECT * FROM sys.fn_trace_getinfo(0);
as will this:
SELECT * FROM ::fn_trace_getinfo(DEFAULT);
DEFAULT is not used to specifically refer to the default trace in this instance - if you have more than one trace running, it will return information for all traces running in the SQL Server instance.
You can also do this:
SELECT * FROM sys.traces;
Once you have the details of the filename you want to look at, you can select the data you want.
For example, the following code will give you all the data in all available rollover trace files:
SELECT * FROM sys.fn_trace_gettable('<pathname>\log_<number>.trc', default);
or
SELECT * FROM ::fn_trace_gettable('<pathname>\log_<number>.trc', default);
From there, it's a simple matter to zero in on the data you want. The default trace logs certain events; you may wish to create your own trace to monitor other events. To discover which events your default trace is logging, you can run the following:
SELECT t.EventID,
t.ColumnID,
e.name as Event_Description,
c.name as Column_Description
FROM ::fn_trace_geteventinfo(1) t
INNER JOIN sys.trace_events e
ON t.eventID = e.trace_event_id
INNER JOIN sys.trace_columns c
ON t.columnid = c.trace_column_id
March 2, 2010 at 5:39 am
thank you for your're reply! i will take a look at it!
March 4, 2010 at 10:31 pm
thank u for suggestion will do experiment on it
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply