A colleague was trying to restore our production database’s to an internal server for this week and noted that the process failed due to a lack of storage. In particular, one database seemed to have grown significantly since the last time we had done this (bi-weekly process). A quick look at the default trace, in particular, the Data File Auto Grow event would reveal all…or would it…?
I use this code to look for auto file growth events;
SELECT TE.name AS [EventName], T.DatabaseName, t.DatabaseID, t.NTDomainName, t.ApplicationName, t.LoginName, t.SPID, t.Duration, t.StartTime, t.EndTime, DATEDIFF(SECOND, StartTime, EndTime) AS Seconds FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), (SELECT TOP 1 f.[value] FROM sys.fn_trace_getinfo(NULL) f WHERE f.property = 2)), DEFAULT) T JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id WHERE te.name = 'Data File Auto Grow' ORDER BY t.StartTime;
This has a few amendments from the original code by Fedor Georgiev. The original can be found here.
Unfortunately, this normally reliable code did not show me any events, rather, it threw an exception:
Msg 19050, Level 16, State 1, Line 3 Trace file name '' is invalid. A quick check to see whether the default trace was actually available;
SELECT * FROM sys.fn_trace_getinfo(null);
You could also run;
sp_configure 'default trace enabled';
In my case, the default trace was enabled, but nothing was returned from sys.fn_trace_getinfo and a quick check of the .trc files on the server showed they stopped being written to about 2 weeks ago.
The solution was pretty simple to get the trace running again:
sp_configure 'default trace enabled', 0 GO RECONFIGURE GO sp_configure 'default trace enabled', 1 GO RECONFIGURE GO
This creates a new trace and will not link to existing .trc files, although, you could take a copy of these before you start and look at them with profiler if you want to check the files over. Given that our data was over 2 weeks old, there wasn’t any value in this for us.
On further analysis, the root cause of this issue was space on the drive. Looking at the error log, it clearly says the trace was stopped due to insufficient space which links to an issue we had a little while back (resolved very quickly, I might add!!).