May 9, 2016 at 7:10 pm
I am trying to return several columns from the sys.database_files view and want to calculate the growth amount of the file. If there is a view or something that has this value please let me know. If there is no view I need to calculate based on that values in the sys.database_files view.
I am trying to return the amount that a database file will autogrow in a database. If it is percent I need to return the percentage value. If its absolute I want to return the amount in MB.
So the is_percent_growth has two values 1 = percent and 0 = absolute value
The growth field will give you the percent amount or it will give you the absolute value (which I can divide by 128) and get the MB
The logic would be
If is_percent_growth = 1 then I want to return the value in growth
If is_percent_growth = 0 then I need to return the value of growth divided by 128
I initially thought I could use case, but I read that it will not do any calculations
I am thinking to use something like this, but its not working
Select
Name
, physical_name
,(IF is_percent_growth = 0 then
select growth/128 from sys.database_files where is_percent_growth = 0
else
select growth from sys.database_files where is_percent_growth = 1
end as [amount growth]
,getdate()
From sys.database_files
I cannot get it to work.
Any help is appreciated.
Jeff
May 9, 2016 at 7:58 pm
select
...
,growth/CASE is_percent_growth WHEN 0 then 128 ELSE 1 END as [amount growth]
...
FROM ...
_____________
Code for TallyGenerator
May 11, 2016 at 6:13 pm
Thank you very much It was perfect.
Jeff
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply