Episode 218: File Growths

Episode 218: File Growths

Episode 218: File Growths 560 420 Carlos L Chacon

In this episode of the SQL Data Partners Podcast, Eugene and I go back to our DBA beginnings and talk about database file growth, how to manage files and how to tell when a file has grown.

DECLARE @current_tracefilename VARCHAR(500);
DECLARE @0_tracefilename VARCHAR(500);
DECLARE @indx INT;
SELECT @current_tracefilename = path
FROM sys.traces
WHERE is_default = 1;
SET @current_tracefilename = REVERSE(@current_tracefilename);
SELECT @indx = PATINDEX(‘%\%’, @current_tracefilename);
SET @current_tracefilename = REVERSE(@current_tracefilename);
SET @0_tracefilename = LEFT(@current_tracefilename, LEN(@current_tracefilename) – @indx) + ‘\log.trc’;
SELECT DatabaseName,
te.name,
Filename,
CONVERT(DECIMAL(10, 3), Duration / 1000000e0) AS TimeTakenSeconds,
StartTime,
EndTime,
(IntegerData * 8.0 / 1024) AS ‘ChangeInSize MB’,
ApplicationName,
HostName,
LoginName
FROM ::fn_trace_gettable(@0_tracefilename, DEFAULT) t
INNER JOIN sys.trace_events AS te ON t.EventClass = te.trace_event_id
WHERE(trace_event_id >= 92
AND trace_event_id <= 95)
ORDER BY t.StartTime;

Never Miss An Episode

Subscribe to get podcast notifications by email.

“We need to ensure that both our data files and our log files are right-sized.”

Carlos L Chacon
Episode 218: File Growths

3 Takeaways

  1. As administrators, we should keep ahead of database growth events.
  2. Your data rate of change will determine how often this affects a system.
  3. If there was slowness and now all is well, you might check growth history for clues.

Imagine what’s possible with a dedicated SQL specialist on your team.

Leave a Reply

Back to top