Sometimes, when we try to view database properties using SQL server management studio GUI to add/delete any data/log file and to view or set the auto growth settings, we get the below error even though the owner of the database is already set.
The below script will provide you the all the database related information, including free space in the files along with the auto growth settings, so it will help us to add any data or log file in case of any emergency situation where the drives are running short of free disk space. The auto growth settings can also be retrieved using this script so that we can restrict/increase the file growth as per our convenience.
SELECT
[Logical File NAME] = left(s.NAME,45), [FILE PATH] = left(s.FILENAME,100), [FILE_SIZE_MB] =convert(decimal(12,2),round(s.size/128.000,2)),
[SPACE_USED_MB] =convert(decimal(12,2),round(fileproperty(s.name,’SpaceUsed’)/128.000,2)),
[FREE_SPACE_MB] =convert(decimal(12,2),round((s.size-fileproperty(s.name,’SpaceUsed’))/128.000,2)),
Autogrowth = ‘Autogrowth: ‘
+
CASE
WHEN (s.status & 0x100000 = 0 AND CEILING((s.growth * 8192.0) / (1024.0 * 1024.0)) = 0.00) OR s.growth = 0 THEN ‘None’
WHEN s.status & 0x100000 = 0 THEN ‘By ‘ + CONVERT(VARCHAR,CEILING((s.growth * 8192.0) / (1024.0 * 1024.0))) + ‘ MB’
ELSE ‘By ‘ + CONVERT(VARCHAR,s.growth) + ‘ percent’
END
+
CASE
WHEN (s.status & 0x100000 = 0 AND CEILING((s.growth * 8192.0) / (1024.0 * 1024.0)) = 0.00) OR s.growth = 0 THEN ”
WHEN CAST([maxsize] * 8.0 / 1024 AS DEC(20,2)) <= 0.00 THEN ‘, unrestricted growth’
ELSE ‘, restricted growth to ‘ + CAST(CAST([maxsize] * 8.0 / 1024 AS DEC(20)) AS VARCHAR) + ‘ MB’
END
FROM dbo.sysfiles s
By: Madhu Mohan
No Comments