|
Home > Archive > MS SQL Server > March 2006 > to monitor user logins/logouts
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
to monitor user logins/logouts
|
|
| maxzsim via SQLMonster.com 2006-02-28, 8:23 pm |
| Hi All ,
This link is from a previous posting
http://vyaskn.tripod.com/ server_si..._sql_server.htm and i followed
the instructions .
I am looking for logins & logouts and i have successfully created the trace
based on the scripts. however, the trc file is always 0 kb despite me logging
in & out several times
where could i have gone wrong in order to get the file populated with data ?
appreciate ur advise
tks & rdgs
--
Message posted via http://www.webservertalk.com
| |
|
|
| maxzsim via SQLMonster.com 2006-02-28, 8:24 pm |
| Hi,
yes you are right i need to stop & clear the trace before the trace file get
populated.
However from the trace file i could only see 2 columns namely "Event Class"
& "Server Name"
i ran the following scripts
====================
====================
=
DECLARE @TraceID int
EXEC CreateTrace
'C:\My SQL Traces\LongRunningPr
ocs',
@OutputTraceID = @TraceID OUT
EXEC AddEvent -- 14 login , 15 logout , 3 - databaseid & 6 - NTUserName
@TraceID,
'14,15',
'3, 6'
EXEC StartTrace @TraceID
GO
====================
====================
==
could you advise where i might have gone wrong ?
tks & rdgs
Tibor Karaszi wrote:[color=darkred
]
>The trace file grow in steps as long as the trace is running. If you stop and then close the trace,
>you should see the size of the file increase.
>
>[quoted text clipped - 11 lines]
--
Message posted via http://www.webservertalk.com
| |
| maxzsim via SQLMonster.com 2006-02-28, 8:24 pm |
| Hi ,
Is there any way to write all the events straight to a table instead of a
trace file and then save it as a trace table ?
tks & rdgs
Tibor Karaszi wrote:[color=darkred
]
>The trace file grow in steps as long as the trace is running. If you stop and then close the trace,
>you should see the size of the file increase.
>
>[quoted text clipped - 11 lines]
--
Message posted via http://www.webservertalk.com
| |
| maxzsim via SQLMonster.com 2006-03-05, 8:23 pm |
| Hi ,
I have found out where i have gone wrong , i shld be putting the actual
eventname & column name instead of their id
tks & rdgs
maxzsim wrote:[color=darkred
]
>Hi,
>
> yes you are right i need to stop & clear the trace before the trace file get
>populated.
>
> However from the trace file i could only see 2 columns namely "Event Class"
>& "Server Name"
> i ran the following scripts
> ====================
====================
=
> DECLARE @TraceID int
>
>EXEC CreateTrace
> 'C:\My SQL Traces\LongRunningPr
ocs',
> @OutputTraceID = @TraceID OUT
>
>EXEC AddEvent -- 14 login , 15 logout , 3 - databaseid & 6 - NTUserName
> @TraceID,
> '14,15',
> '3, 6'
>
>
>EXEC StartTrace @TraceID
>GO
>
> ====================
====================
==
>
>could you advise where i might have gone wrong ?
>
>tks & rdgs
>[quoted text clipped - 4 lines]
--
Message posted via http://www.webservertalk.com
| |
| maxzsim via SQLMonster.com 2006-03-05, 8:23 pm |
| Hi ,
furthermore , is there any other method to find out who has logged in/out ?
how abt in SQL Server 2005 , is there any tbl that captures the login/logout
?
appreciate ur advise
tks & rdgs
maxzsim wrote:[color=darkred
]
>Hi ,
>
> Is there any way to write all the events straight to a table instead of a
>trace file and then save it as a trace table ?
>
>tks & rdgs
>[quoted text clipped - 4 lines]
--
Message posted via http://www.webservertalk.com
| |
|
|
| Tibor Karaszi 2006-03-05, 8:23 pm |
| > furthermore , is there any other method to find out who has logged in/out ?
Sure, just make certain you trace the necessary events and columns. In fact, the default Profiler
trace already have these.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/
"maxzsim via webservertalk.com" <u14644@uwe> wrote in message news:5c960c599511d@u
we...
> Hi ,
>
> furthermore , is there any other method to find out who has logged in/out ?
>
> how abt in SQL Server 2005 , is there any tbl that captures the login/logout
> ?
>
>
> appreciate ur advise
>
> tks & rdgs
>
> maxzsim wrote:
>
> --
> Message posted via http://www.webservertalk.com
| |
| ivanpe@online.microsoft.com 2006-03-05, 8:23 pm |
| >> Is there any way to write all the events straight to a table instead of a
SQL Server Profiler allows you to do this, however this extra functionality is not "natively" supported by the SQL Server engine. It is built in the Profiler tool by leveraging rowset (in-mem) trace. Profiler reads the stream of events from SQL Server and
inserts them in a table. This is not very efficient and it is not recommended on production systems.
To create such trace, start Profiler and select File -> New Trace. After the connection to SQL Server is established, select the events you need and make sure you check the "Save to table" checkbox. The you will be asked to select database and table name.
The recommended approach is to create a server side file trace. At any time (you don't need to stop the trace on SQL Server 2005) you can load the events into a table or run queries against the file by leveraging fn_trace_gettable:
-- to query
select * from fn_trace_gettable ('file_name', default)
-- to insert in a table
select * into #foo from fn_trace_gettable ('file_name', default)
Thanks,
-Ivan
-----Original Message-----
From: Tibor Karaszi
Posted At: Wednesday, March 01, 2006 10:10 AM
Posted To: microsoft.public.sqlserver.server
Conversation: to monitor user logins/logouts
Subject: Re: to monitor user logins/logouts
[color=darkred]
> furthermore , is there any other method to find out who has logged in/out ?
Sure, just make certain you trace the necessary events and columns. In fact, the default Profiler trace already have these.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/
"maxzsim via webservertalk.com" <u14644@uwe> wrote in message news:5c960c599511d@u
we...
> Hi ,
>
> furthermore , is there any other method to find out who has logged in/out ?
>
> how abt in SQL Server 2005 , is there any tbl that captures the login/logout
> ?
>
>
> appreciate ur advise
>
> tks & rdgs
>
> maxzsim wrote:
>
> --
> Message posted via http://www.webservertalk.com
| |
| maxzsim via SQLMonster.com 2006-03-05, 8:24 pm |
| tks Ivan ..
ivanpe@online.microsoft.com wrote:
>
>SQL Server Profiler allows you to do this, however this extra functionality is not "natively" supported by the SQL Server engine. It is built in the Profiler tool by leveraging rowset (in-mem) trace. Profiler reads the stream of events from SQL Server an
d inserts them in a table. This is not very efficient and it is not recommended on production systems.
>
>To create such trace, start Profiler and select File -> New Trace. After the connection to SQL Server is established, select the events you need and make sure you check the "Save to table" checkbox. The you will be asked to select database and table name
|
|
|
|
|