Error when you try to launch database properties in SSMS

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

Leave a Comment

Your email address will not be published.