Over the last week I’ve been finding an unusual number of log file growths. I did some investigation and found a good script to help me track what was the current status for file growths. It is as follows:
SELECT
SD.database_id,
SD.name,
SF.name as FileName,
recovery_model_desc,
CASE SF.status & 0×100000
WHEN 1048576 THEN ‘Percentage’
WHEN 0 THEN ‘MB’ END AS ‘GROWTH Option’,
size * 8/1000 as DBsizeMb ,
CASE SF.status & 0×100000
when 1048576 then sf.growth
else sf.growth * 8/1000 end as growth
FROM SYS.SYSALTFILES SF
inner JOIN SYS.DATABASES SD ON SD.database_id = SF.dbid
where sd.database_id > 5
order by sd.name ,size desc