Default Trace Not Working

  • keanyukas

    SSC Eights!

    Points: 914

    Someone disabled and removed the default trace. I added it back and it seems to be running however when i run a query to get the autogrowth of my databases i get the following error
    Msg 567, Level 16, State 5, Line 23
    File '' either does not exist or is not a recognizable trace file. Or there was an error opening the file.
    Tried a number of times to disable and enable it but still not working
    I can see trace file on server but it is not growing and there is plenty of room

  • Johan Bijnens

    SSC Guru

    Points: 134253

    did you check the errorlog files to determine who disabled / stopped the default trace ?

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • keanyukas

    SSC Eights!

    Points: 914

    I did not my main concern is to get it going again however when i put it back and do a select * from sys.traces i get two entries one with id of 1 and one with id of two the path for 1 is null and path for 2 is populated however when i try to query for growth it tells me file does not exist

  • Sue_H

    SSC Guru

    Points: 90000

    keanyukas - Friday, April 6, 2018 8:10 AM

    I did not my main concern is to get it going again however when i put it back and do a select * from sys.traces i get two entries one with id of 1 and one with id of two the path for 1 is null and path for 2 is populated however when i try to query for growth it tells me file does not exist

    Try using sp_configure to disable and then reenable the default trace -
    EXEC sp_configure 'default trace enabled', 0;
    GO
    RECONFIGURE WITH OVERRIDE;
    GO
    EXEC sp_configure 'default trace enabled', 1;
    GO
    RECONFIGURE WITH OVERRIDE;

    Did you check the directory yourself to see if that trace file exists? And is there enough space on that drive for the trace?
    Have you checked the SQL Server error log?

    Sue

  • keanyukas

    SSC Eights!

    Points: 914

    did that this is what i get

  • Sue_H

    SSC Guru

    Points: 90000

    keanyukas - Friday, April 6, 2018 11:22 AM

    did that this is what i get

    Okay...so that shows the default trace is running. And someone else is running profiler (rowset trace with null file path). Nothing unusual.
    Are you sure it's not your query that is incorrect as the trace is running.

    Sue

  • keanyukas

    SSC Eights!

    Points: 914

    positive because it works on every other server. also every other server just has one entry when you select from sys.traces

  • Sue_H

    SSC Guru

    Points: 90000

    keanyukas - Friday, April 6, 2018 11:48 AM

    positive because it works on every other server. also every other server just has one entry when you select from sys.traces

    The two entries I already explained - someone is running profiler.  It means nothing other than on the other servers, no one was running profiler.

    Sue

  • keanyukas

    SSC Eights!

    Points: 914

    i get that but the query does not find the trace file and does every where else even if i change the id to 2 still will not work
    DECLARE @filename NVARCHAR(1000);
    DECLARE @bc INT;
    DECLARE @ec INT;
    DECLARE @bfn VARCHAR(1000);
    DECLARE @efn VARCHAR(10);
     
    -- Get the name of the current default trace
    SELECT @filename = CAST(value AS NVARCHAR(1000))
    FROM ::fn_trace_getinfo(DEFAULT)
    WHERE traceid = 1 AND property = 2;
     
    -- rip apart file name into pieces
    SET @filename = REVERSE(@filename);
    SET @bc = CHARINDEX('.',@filename);
    SET @ec = CHARINDEX('_',@filename)+1;
    SET @efn = REVERSE(SUBSTRING(@filename,1,@bc));
    SET @bfn = REVERSE(SUBSTRING(@filename,@ec,LEN(@filename)));
     
    -- set filename without rollover number
    SET @filename = @bfn + @efn
     
    -- process all trace files
    SELECT
      ftg.StartTime
    ,te.name AS EventName
    ,DB_NAME(ftg.databaseid) AS DatabaseName 
    ,ftg.Filename
    ,(ftg.IntegerData*8)/1024.0 AS GrowthMB
    ,(ftg.duration/1000)AS DurMS
    FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg
    INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id 
    WHERE (ftg.EventClass = 92  -- Date File Auto-grow
        OR ftg.EventClass = 93) -- Log File Auto-grow
    ORDER BY ftg.StartTime

  • Sue_H

    SSC Guru

    Points: 90000

    Check the permissions on the folder as well as the file itself - it would be being accessed by the SQL Server service account.

    Sue

  • keanyukas

    SSC Eights!

    Points: 914

    full blown permissions on the directory

Viewing 11 posts - 1 through 11 (of 11 total)

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