June 7, 2014 at 3:18 pm
How can I Convert sys.master_files size column to GB?
Select db_name(mf.database_id) As DatabaseName
, convert(int
, Case When convert(bigint , sum(mf.size)) >= 268435456 Then Null
Else sum(mf.size) * 8 / 1000 -- Convert from 8192 byte pages to Kb
--ELSE CONVERT(DECIMAL(10,2),(SUM(size * 8.00) / 1024.00 / 1024.00))
End) As DatabaseSize_KB
, cast(getdate() As date) As SizeDate
From sys.master_files mf
Where mf.state = 0
Group By
mf.database_id;
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 7, 2014 at 3:54 pm
Hi
Try this
SELECT
DB.name,
SUM(CASE WHEN type = 0 THEN MF.size * 8 / 1024.0 /1024.0 ELSE 0 END) AS DataFileSizeGB,
SUM(CASE WHEN type = 1 THEN MF.size * 8 / 1024.0 /1024.0 ELSE 0 END) AS LogFileSizeGB
FROM
sys.master_files MF
JOIN sys.databases DB ON DB.database_id = MF.database_id
WHERE DB.source_database_id is null -- exclude snapshots
GROUP BY DB.name
ORDER BY DataFileSizeGB DESC
Igor Micev,My blog: www.igormicev.com
June 7, 2014 at 4:42 pm
That is sweet. I have the sizes stored in KB's. What statement could I used to convert from KB to GB?
Thanks a bunch!
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 7, 2014 at 5:58 pm
Welsh Corgi (6/7/2014)
That is sweet. I have the sizes stored in KB's. What statement could I used to convert from KB to GB?Thanks a bunch!
Divide twice by 1024.0.
June 8, 2014 at 1:57 am
Lynn Pettis (6/7/2014)
Welsh Corgi (6/7/2014)
That is sweet. I have the sizes stored in KB's. What statement could I used to convert from KB to GB?Thanks a bunch!
Divide twice by 1024.0.
Syntax please? :unsure:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 8, 2014 at 2:33 am
Welsh Corgi (6/8/2014)
Lynn Pettis (6/7/2014)
Welsh Corgi (6/7/2014)
That is sweet. I have the sizes stored in KB's. What statement could I used to convert from KB to GB?Thanks a bunch!
Divide twice by 1024.0.
Syntax please? :unsure:
select 123456789/1024
select 123456789/1024/1024
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 8, 2014 at 3:29 am
For fun, a quick demonstration
😎
/* 250 Gb */
DECLARE @MY_NumberOfBytes NUMERIC(12,0) = 268435456000;
/* bytes */
SELECT
'Bytes' AS UNIT
,@MY_NumberOfBytes AS NUMBER
UNION ALL
/* Kilobytes, divide by 2^10 (1024) */
SELECT
'Kilobytes' AS UNIT
,@MY_NumberOfBytes / POWER(2.0,10.0) AS NUMBER
UNION ALL
/* Megabytes, divide by 2^20 (1048576) */
SELECT
'Megabytes' AS UNIT
,@MY_NumberOfBytes / POWER(2.0,20.0) AS NUMBER
UNION ALL
/* Gigabytes, divide by 2^30 (1073741824) */
SELECT
'Gigabytes' AS UNIT
,@MY_NumberOfBytes / POWER(2.0,30.0) AS NUMBER
UNION ALL
/* Terabytes, divide by 2^40 (1099511627776) */
SELECT
'Terabytes' AS UNIT
,@MY_NumberOfBytes / POWER(2.0,40.0) AS NUMBER;
Results
UNIT NUMBER
--------- ----------------------
Bytes 268435456000.000000000
Kilobytes 262144000.000000000
Megabytes 256000.000000000
Gigabytes 250.000000000
Terabytes 0.244140625
June 8, 2014 at 4:14 am
Thanks everyone.:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 8, 2014 at 4:15 am
Eirikur Eiriksson (6/8/2014)
For fun, a quick demonstration😎
/* 250 Gb */DECLARE @MY_NumberOfBytes NUMERIC(12,0) = 268435456000;
/* bytes */
SELECT
'Bytes' AS UNIT
,@MY_NumberOfBytes AS NUMBER
UNION ALL
/* Kilobytes, divide by 2^10 (1024) */
SELECT
'Kilobytes' AS UNIT
,@MY_NumberOfBytes / POWER(2.0,10.0) AS NUMBER
UNION ALL
/* Megabytes, divide by 2^20 (1048576) */
SELECT
'Megabytes' AS UNIT
,@MY_NumberOfBytes / POWER(2.0,20.0) AS NUMBER
UNION ALL
/* Gigabytes, divide by 2^30 (1073741824) */
SELECT
'Gigabytes' AS UNIT
,@MY_NumberOfBytes / POWER(2.0,30.0) AS NUMBER
UNION ALL
/* Terabytes, divide by 2^40 (1099511627776) */
SELECT
'Terabytes' AS UNIT
,@MY_NumberOfBytes / POWER(2.0,40.0) AS NUMBER;
Results
UNIT NUMBER--------- ----------------------
Bytes 268435456000.000000000
Kilobytes 262144000.000000000
Megabytes 256000.000000000
Gigabytes 250.000000000
Terabytes 0.244140625
Pretty slick/:cool:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 5, 2017 at 1:15 pm
Igor Micev - Saturday, June 7, 2014 3:54 PMHiTry thisSELECT DB.name, SUM(CASE WHEN type = 0 THEN MF.size * 8 / 1024.0 /1024.0 ELSE 0 END) AS DataFileSizeGB, SUM(CASE WHEN type = 1 THEN MF.size * 8 / 1024.0 /1024.0 ELSE 0 END) AS LogFileSizeGBFROM sys.master_files MF JOIN sys.databases DB ON DB.database_id = MF.database_idWHERE DB.source_database_id is null -- exclude snapshotsGROUP BY DB.nameORDER BY DataFileSizeGB DESC
Hats of to a job well done. I really appreciate T SQL code that works. It works perfectly since Microsoft two methods do not work.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply