September 20, 2008 at 4:26 am
Comments posted to this topic are about the item Who has accessed my 2005 server?
Jack Corbett
Consultant - Straight Path Solutions
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
November 5, 2008 at 6:08 am
Really useful script that shows how to do use the trace. Thanks!
November 10, 2008 at 10:02 am
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!November 10, 2008 at 10:16 am
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 http://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
Consultant - Straight Path Solutions
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
November 10, 2008 at 10:44 am
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!November 10, 2008 at 11:02 am
Yes, within reason. It does not demonstrate how to start the trace when SQL Server starts.
Jack Corbett
Consultant - Straight Path Solutions
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
February 9, 2009 at 4:42 pm
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.
February 9, 2009 at 5:00 pm
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?
February 9, 2009 at 7:18 pm
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
Consultant - Straight Path Solutions
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
February 9, 2009 at 7:40 pm
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
Consultant - Straight Path Solutions
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
April 9, 2009 at 7:02 am
Great script Jack! Thanks for this and I know its been out for a while already but I have been real busy. This will help me a lot. I will definitely implement it. I would also like to get a list of login failures so I can follow it up because someone one day told me that if you have a lot of login failures it may be someone trying to hack the database. Be glad if you could help me!
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
April 9, 2009 at 10:10 am
Thanks Manie.
You can use the default trace to check for login failures, here's an idea on how:
SELECT
I.*
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 JOIN
sys.trace_events AS TE ON
I.EventClass = TE.trace_event_id
WHERE
TE.[name] = 'Audit Login Failed'
At R. Barry Young's suggestion I am working on a presentation about mining the default trace that will likely turn into at least one article here at SSC within a year or so. Your question helps with ideas. It takes me awhile to write and I have a series on Profiler that I need to finish first.
Jack Corbett
Consultant - Straight Path Solutions
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
April 10, 2009 at 6:31 am
Thanks Jack, this is real nice and I have already added it to my collection of scripts. I look forward to that articles about the Profiler!
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
April 13, 2009 at 3:53 pm
Jack Corbett (2/9/2009)
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,
.....
{Removed for brevity}
... 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.
Apolagies if this sounds dumb because I may just be missing something but the way I read your reply with this new code was that it would load all of the traace files and not just the last one else why strip out the file number portion of the trace file name? When I run this exact code as is I only get data from the most recent trace file.
Whats weird about this is I swear there was a forum posting discussing this same thing with the fn_Trace_Gettable() function/procedure and I thought someone did list a way to get the thing to load content from all trc files in that same directory.
Am I going crazy or is that familiar to anyone else, that there is a way to load all the trc file?
Thanks Jack!
Kindest Regards,
Just say No to Facebook!April 14, 2009 at 2:28 am
The "new" code with the file number removed should load all the files for the specified trace. On my laptop, which I reboot somewhat regularly, and does not have a really active SQL Server, the code without removing the file number only queries the "active" file while the new code returns data from all the files.
Jack Corbett
Consultant - Straight Path Solutions
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
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply