January 20, 2016 at 4:54 pm
Hello
Tried with diff ways but unable to remove the zeros after the decimal point in the column : max_size
Please suggest
select DB_NAME(mf.database_id) database_name
, mf.name logical_name
, CONVERT (DECIMAL (20,2) , (CONVERT(DECIMAL, size)/128)) [file_size_MB]
, CASE mf.is_percent_growth
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [is_percent_growth]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB'
END AS [growth_in_increment_of]
, CASE mf.is_percent_growth
WHEN 1 THEN
CONVERT(DECIMAL(20,2), (((CONVERT(DECIMAL, size)*growth)/100)*8)/1024)
WHEN 0 THEN
CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL, growth)/128))
END AS [next_auto_growth_size_MB]
, CASE mf.max_size
WHEN 0 THEN 'No growth is allowed'
WHEN -1 THEN 'File will grow until the disk is full'
--ELSE CONVERT(VARCHAR, mf.max_size)
ELSE CONVERT(NVARCHAR(20), round(CONVERT(numeric(12,2), max_size) * 8/1024.0,2)) + N' MB'
END AS [max_size]
, physical_name
from sys.master_files mf
Thanks.
January 20, 2016 at 5:24 pm
That's because ROUND doesn't actually remove digits. It will just round the value to that place, leaving the current scale in place (i.e., ROUND(1.12345,2) is 1.12000, not 1.12).
Because of the rules shown here https://technet.microsoft.com/en-us/library/ms190476(v=sql.110).aspx, 8/1024.0 will give a result with 6 digits of scale, and the resulting multiplication with the numeric(12,2) gives you a number with 8 digits of scale. ROUNDing doesn't remove those extra digits; it just rounds as shown above.
Multiplying by 8 and dividing by 1024 is the same as dividing by 128, so I'd simplify that bit accordingly. Then, I'd skip the ROUNDing and just CONVERT the result of max_size/128.0.
That would leave you with this for that line:
ELSE CONVERT(NVARCHAR(20), CONVERT(numeric(12,2), max_size/128.0)) + N' MB'
Cheers!
January 21, 2016 at 1:58 pm
Many thanks Jacob for the explanation
Thanks.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply