SQL 2005 Default Trace

Question

SQL 2005 Default Trace

planetmatt

SSCrazy

Points: 2201
More actions
October 6, 2008 at 10:03 am

#125050

I have a job which monitors the default trace file and alerts me to database creations/deletions/restores.
This works fine on most of our servers but on one it doesn't.
select * from sys.configurations where configuration_id = 1568
shows that the default trace is running but
select * from ::fn_trace_getinfo(0)
returns 0 records and there is no sign of a log.trc anywhere on the file system.
Does anyone know why the default trace would not be running or how to get it working?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 1

Have you checked the default log file location where these files get created? Also, run select * from sys.traces and see under the path dispalyed in output of above query(path column).

MJ

Jack Corbett SSC Guru Points: 184393 More actions · Answer 2

The default trace has a trace id of 1 not 0. That is why you get no data from fn_trace_getinfo.

Manu is right check the path to the trace files. If the SQL Server was not installed using defaults or is a named instance it may be in a different location. How are you querying the trace files?

This is how I query trace files:

sys.traces T CROSS Apply

::fn_trace_gettable(T.path, T.max_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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

If you've installed sP2 and have the reports, the "Schema Changes History" is a visual version of that function as well.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

planetmatt SSCrazy Points: 2201 More actions · Answer 4

MANU (10/6/2008)
Have you checked the default log file location where these files get created? Also, run select * from sys.traces and see under the path dispalyed in output of above query(path column).
MJ

Yes, checked the default log locations, no sign of log.trc.

SELECT * FROM SYS.TRACES returns 0 records.

I also tried the schema changes default report but this returns this error:

Unable to retrieve data for this section of the report. Following error occurred. Msg 567, Level 16, State 5 File '' either does not exist or is not a recognizable trace file. Or there was an error opening the file.

Perry Whittle SSC Guru Points: 233902 More actions · Answer 5

how are you creating\starting the trace?

is it a server side trace?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jack Corbett SSC Guru Points: 184393 More actions · Answer 6

planetmatt (10/7/2008)
MANU (10/6/2008)
Have you checked the default log file location where these files get created? Also, run select * from sys.traces and see under the path dispalyed in output of above query(path column).
MJ
Yes, checked the default log locations, no sign of log.trc.
SELECT * FROM SYS.TRACES returns 0 records.
I also tried the schema changes default report but this returns this error:
Unable to retrieve data for this section of the report. Following error occurred. Msg 567, Level 16, State 5 File '' either does not exist or is not a recognizable trace file. Or there was an error opening the file.

For some reason the default trace was removed from this server. You would need to re-create the trace. I would guess that there is a script available that will re-create this trace for you.

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

Dream Weaver Ten Centuries Points: 1104 More actions · Answer 7

This thread help me out today since I had the same problem as Matt.

If it's any help to someone else, restarting the SQL Server instance was enough to get the default trace running again.

The server had recently run out of disk space (the DBAs don't monitor dev servers as closely as prod). We're speculating that the trace shut off then and didn't start again until the instance was started.