March 9, 2009 at 12:01 pm
Anyone know if this is possible? Over the weekend, something or someone dropped a few linked servers in the production environment. I've check sql error log and event viewer.
thanks.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
March 9, 2009 at 12:03 pm
SQL 2005 keeps a default trace running, unless you've turned it off. That should have the data in that you're looking for.
- 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
March 9, 2009 at 12:06 pm
o? where is this default trace file stored? I don't remember turning anything off during setup.
thx
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
March 9, 2009 at 12:12 pm
Run:select * from fn_trace_getinfo(0)
That'll tell you whether the trace is running or not (row 5 = 1 means it's running, 0 means it's not), and the file location.
Run:select * from fn_trace_gettable ( filename , number_files )
Plug in the correct filename and either "default" or a number (greater than 1), and that will query the trace data for you. Start with something like selecting the top 10 from it, so you can get a feel for what the resultset will look like, then start playing around with the Where clause to find what you want.
It only keeps a certain amount of data, so it's possible what you need has "rolled out the bottom". If so, then it's pretty much gone. On most servers I've worked with, it's had at least a few days' data in it.
- 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
March 9, 2009 at 12:38 pm
hrmm ... don't know if that function and default trace captures anything about linked server. I just added a linked server and then queried that all the trace files and it didn't show up.
is there a way to edit the default trace to capture more specific data?
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
March 9, 2009 at 12:57 pm
The default mostly captures DDL commands. I thought it would have linked servers in that, but I guess not.
Instead of trying to modify the default trace, you'd probably be better off creating a trace that captures exactly what you want. Look up sp_trace_create and sp_trace_setevent and sp_trace_setstatus in Books Online for details on what you can trace and all the options on it.
- 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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply