Default Trace Not Working
-
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 -
did you check the errorlog files to determine who disabled / stopped the default trace ?
Johan
Learn to play, play to learn !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 but most of the time this is me
-
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
- keanyukas - Friday, April 6, 2018 8:10 AM
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
-
did that this is what i get
- keanyukas - Friday, April 6, 2018 11:22 AMdid 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
-
positive because it works on every other server. also every other server just has one entry when you select from sys.traces
- keanyukas - Friday, April 6, 2018 11:48 AM
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
-
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 -
Check the permissions on the folder as well as the file itself - it would be being accessed by the SQL Server service account.
Sue
-
full blown permissions on the directory
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply