Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Who has accessed my 2005 server? Expand / Collapse
Author
Message
Posted Saturday, September 20, 2008 4:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:54 AM
Points: 10,340, Visits: 13,338
Comments posted to this topic are about the item Who has accessed my 2005 server?



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #572976
Posted Wednesday, November 5, 2008 6:08 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 5, 2012 3:55 PM
Points: 442, Visits: 185
Really useful script that shows how to do use the trace. Thanks!

Post #597262
Posted Monday, November 10, 2008 10:02 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 12:05 PM
Points: 892, Visits: 1,559
This probably sounds like a no-brainer but I have a question about the code. I understand what it does but what I didn't know about is the trace itself. Does SQL Server by default always run a trace on every connection and that's what this code is returning the details on? IO thought traces were restricted to Profiler. If the answer is yes then is this default trace similar to a trace in Profiler?

Thanks


Kindest Regards,

Just say No to Facebook!
Post #599990
Posted Monday, November 10, 2008 10:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:54 AM
Points: 10,340, Visits: 13,338
SQL Server 2005 has a basic trace that is created when you install SQL Server and starts on start-up. You could remove this trace if you wanted to, but since you didn't know about it, it obviously isn't negatively affecting performance. This is also what many of the Management reports get their data from.

Profiler is actually just a GUI interface on top of server-side tracing. You could check out the Profiler video's on www.jumpstarttv.com where I explain how to use Profiler to generate a script for a server-side trace.

You can also lookup server-side trace in BOL.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #600009
Posted Monday, November 10, 2008 10:44 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 12:05 PM
Points: 892, Visits: 1,559
Jack -

Thanks for the inf. So the video you are referencing, it can show one how to basically create your own custom server side trace to do what the default one in 2005 does but do it capturing what you;ve specified in your custom trace? I imagine based on your descrpition that the video will show you how to take a trace you've defined (like a termplate trace you've created) and apply that as the server side trace.

Thanks


Kindest Regards,

Just say No to Facebook!
Post #600039
Posted Monday, November 10, 2008 11:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:54 AM
Points: 10,340, Visits: 13,338
Yes, within reason. It does not demonstrate how to start the trace when SQL Server starts.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #600052
Posted Monday, February 9, 2009 4:42 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 23, 2010 7:48 PM
Points: 38, Visits: 183
The code is nice, but it actually shows only the records from the last trace file (SQL Srv 2005 Ent , SP2 and SP3).

select * from sys.traces
- returns the last trace file, e.g
'E:\MSSQL\SystemData\MSSQL.1\MSSQL\LOG\log_734.trc'

But ::fn_trace_gettable() - requires the first file name from the sequence.

For example, if there are 5 files stored, then it is necessary to pass
('E:\MSSQL\SystemData\MSSQL.1\MSSQL\LOG\log_730.trc', 5)

Otherwise, you will see info only from the last file.

I also tried going back by passing the last file name and negative number of files(-3), but it did not work as desired - only the last log info was returned.
Post #653321
Posted Monday, February 9, 2009 5:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 23, 2014 12:47 PM
Points: 3, Visits: 120
when I run the script( as posted), error is returned:
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near 'T'.

this: select * from sys.traces
returns: C:\Program Files\Microsoft SQL Server\MSSQL\log\log_77.trc

I am a novice at Traces and profiler.
what might be my error?
Post #653324
Posted Monday, February 9, 2009 7:18 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:54 AM
Points: 10,340, Visits: 13,338
Vitali Lisau (2/9/2009)
The code is nice, but it actually shows only the records from the last trace file (SQL Srv 2005 Ent , SP2 and SP3).

select * from sys.traces
- returns the last trace file, e.g
'E:\MSSQL\SystemData\MSSQL.1\MSSQL\LOG\log_734.trc'

But ::fn_trace_gettable() - requires the first file name from the sequence.

For example, if there are 5 files stored, then it is necessary to pass
('E:\MSSQL\SystemData\MSSQL.1\MSSQL\LOG\log_730.trc', 5)

Otherwise, you will see info only from the last file.

I also tried going back by passing the last file name and negative number of files(-3), but it did not work as desired - only the last log info was returned.


You are correct that is a nice catch. This actual happens because Path actually includes the file number instead of the default name. I will correct the code to reflect this.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #653367
Posted Monday, February 9, 2009 7:40 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:54 AM
Points: 10,340, Visits: 13,338
I modified the code in the script to fix this oversight. Steve just needs to release the updated code. Here it is here as well:

SELECT 
I.NTUserName,
I.loginname,
I.SessionLoginName,
I.databasename,
Min(I.StartTime) as first_used,
Max(I.StartTime) as last_used,
S.principal_id,
S.sid,
S.type_desc,
S.name
FROM
sys.traces T CROSS Apply
::fn_trace_gettable(CASE
WHEN CHARINDEX( '_',T.[path]) <> 0 THEN
SUBSTRING(T.PATH, 1, CHARINDEX( '_',T.[path])-1) + '.trc'
ELSE T.[path]
End, T.max_files) I LEFT JOIN
sys.server_principals S ON
CONVERT(VARBINARY(MAX), I.loginsid) = S.sid
WHERE
T.id = 1 And
I.LoginSid is not null
Group By
I.NTUserName,
I.loginname,
I.SessionLoginName,
I.databasename,
S.principal_id,
S.sid,
S.type_desc,
S.name

I overlooked the fact that the Path stored in sys.traces includes the numeric extension to the file name. The CASE strips the numeric portion of the file name.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #653373
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse