How frequently is database growing?
We always keep our database data and log file to auto grow by some value. But are we sure that the current auto growth parameters is correct. Is the auto growth happening to frequently? Or we have allocated more than sufficient space in one go that database have not grown for a year.
Auto growing data files very frequently with small increments is bad for database as well as OS. When ever auto growth happens database takes a performance hit. At OS level, this new chunk of space is not always adjacent to the previous space allocated to databases. Thus causing disk fragmentation.
So, how to determine, how frequently our database auto growth is happening?
Make use of below script to check how frequently the data and log file is growing.
DECLARE @path NVARCHAR(1000)
SELECT @path = Substring(PATH, 1, Len(PATH) – Charindex(‘\’, Reverse(PATH))) + ‘\log.trc’
WHERE id = 1
— id =1 means the active trace file
te.name AS eventname,
FROM ::fn_trace_gettable(@path, 0)
INNER JOIN sys.trace_events te
ON eventclass = trace_event_id
INNER JOIN sys.trace_categories AS cat
ON te.category_id = cat.category_id
WHERE te.name IN( ‘Data File Auto Grow’, ‘Log File Auto Grow’ )
ORDER BY databasename,starttime DESC;
Output will be the database name, start time when auto growth of data or log file happened. Now we can check, if auto growth is happening too frequently, we can increase the growth size.