For some time now I've been using
fn_trace_gettable to automatically collect and report certain events on the server.
Function is being called with following parameters:
Which should return results from all 5 default trace files. And it used to work just fine, but recently I discovered that the script started to return empty results even though I knew for sure that those events took place.
When inspecting the cause, I discovered contents of log dir looks like that:
WriteTime Length Name --------- ------ ---- 2018-10-01 1 048 576 log_242.trc 2018-10-02 1 048 576 log_243.trc 2018-10-03 2 097 152 log_244.trc 2018-10-26 0 log_247.trc 2018-10-27 0 log_248.trc 2018-10-28 0 log_249.trc 2018-10-29 1 048 576 log_250.trc 2018-10-30 9 024 000 log_251.trc
A few things are wrong here:
- More than standard 5 rotating files
- Some files are empty
- Inconsistent rollover numbering
I tested the function (0 is the same as
default and should return results from selected file and all files up to the newest active):
::fn_trace_gettable('C:\MSSQL\Log\log.trc',0) ::fn_trace_gettable('C:\MSSQL\Log\log_242.trc',0) ::fn_trace_gettable('C:\MSSQL\Log\log_247.trc',0)
First two calls return results only from first 3 files. Seems like this is because there are no 245 file. Third call throws an error because the file is blank.
I solved this by deleting all trace files except most recent 250 and 251 and
::fn_trace_gettable('C:\MSSQL\Log\log.trc',0) now works as expected, returning results from both files. New files should be created in the future with correct rollover numbers and old files should be deleted. But this situation may happen all over again.
- Why those empty files were created in the first place? No server restarts around dates when trace files were created.
- Why there are no 245 and 246 files? I suspect that they were deleted as there are already 5 newer files.
- Why the server lost track of old 242,243,244 files and didn't delete them upon creating new ones?
- How to prevent this behaviour in the future?