Total of log file and data file to count DB size

1

I am trying to get the total of file size using following query but don't know how to combine it?
SELECT
[database_id],
[name],
[size]
FROM sys.master_files
So For name, we have two file one is Datafile and other one is Log file and i need to add both size combined as like:
tempdev
1024
templog
64
I need total of Tempdev + tempLog so 1024 + 64 = 1088

Thanks for your help!

2 
 SELECT  DBID   = database_id
        ,DBName = DB_NAME(database_id)
        ,SizeMB = CONVERT(DECIMAL(9,3),SUM(size) /128.0)
   FROM sys.master_files
  GROUP BY database_id
;
3

Thanks Jeff for quick response.
I also tried other as i need to pivot also so i tried as below:

SELECT DBName,
DataFile AS DataFileSizeInMB,
LogFile AS LogFileInMB,
( DataFile + LogFile ) / 1024.0 AS DataBaseSizeInGB
FROM (SELECT DB_NAME(Database_id) AS DBName,
size * 8.0 / 1024 AS SizeInMB,
CASE
WHEN TYPE = 0 THEN 'DataFile'
ELSE 'LogFile'
END AS FileType
FROM sys.master_files) D
PIVOT ( MAX(SizeInMB)
FOR FileType IN (DataFile,
LogFile)) PivotTable

4

I am trying to collect the Database growth records for each day in a PIVOT Table like but it's only pulling Datafile size not log file size so my DB size coming wrong:

SELECT
DB1, DB2, DB3
FROM (
SELECT [collect_date], DB_NAME(database_id) AS 'DatabaseName', [SIZE] AS 'DBSize'
FROM [DatabaseFileSize]
) AS SourceTable
PIVOT (
MAX ([DBsize])
FOR DatabaseName IN
(DB1, DB2, DB3)
)AS PivotTable

My Result should be:

CollectionDate DB1 DB2 DB3
4/2/20 12:31 PM 8283648 10912 3456
4/3/20 4:00 PM 8283649 10912 3459
4/4/20 4:00 PM 8283649 10915 3476
4/5/20 4:00 PM 8283650 10917 3496
4/6/20 4:00 PM 8283658 10919 3556
4/7/20 4:00 PM 8283660 10900 3656
5

Thanks for your help Jeff.
Above my query getting only Data file size, not adding log file size but if i like your query also but how can i make the pivot was so i can get the same results?
I have created stored procedure and inserting data running the job and i have written above query to get the pivot report by data for each databases.

6

You've basically specified 3 different reports now. So, let me ask you some straight up questions.

  1. Do you want the size of the log files to be included in the sum of the size or not. Your first post said you did and now you're saying you don't.
  2. Do you realize that you can have a 1TB file size with less than 1MB of space used?
  3. Please post your final set of immutable requirements. Or are these your final requirements below?

And, if those are your requirements, do you want the log file sizes included or not? You say one thing but then you change your mind. Please post your final requirements.

7

Thanks Jeff and Sorry for the confusion.
I want to include Log file too so that's way i can have right database size.
I was showing my report which i realized that it;s including only Data file when i compare the actual Db size.
In My report, i have generated PIVOT report but somehow it shows only data file size and i am pulling from sys.master_files.
When i add DB File and log file then it won't be Database size correctly?
I am trying to capture the regular Database growth.

Thanks

8

Thanks Jeff for your help!
Actually i am looking database growth track so i can use your query to get for all the databases for the server?
Appreciate your help!

9 
if OBJECT_ID('tempdb..#dbstats') is not null
	drop table #dbstats;

declare @dbname varchar(50), @cols AS NVARCHAR(MAX), 
@query varchar(max), 
@querydate varchar(50) = convert(varchar(50), getdate(), 120);


	create table #dbstats(collect_date datetime, dbname varchar(50), size int)

	DECLARE db_cursor CURSOR FOR   
    SELECT d.Name  
     from sys.databases d
  
    OPEN db_cursor  
    FETCH NEXT FROM db_cursor INTO @dbname  
  
    IF @@FETCH_STATUS <> 0   
        PRINT '         <<None>>'       
  
    WHILE @@FETCH_STATUS = 0  
    BEGIN  
		set @query = 'select ''' + @querydate + ''' as collect_date, ''' 
+ @dbname + ''', sum(coalesce(size,0)) as size
				  from ' + @dbname + '.sys.database_files '
		
		insert into #dbstats(collect_date, dbname, size)
		exec(@query)

        PRINT @query  
        FETCH NEXT FROM db_cursor INTO @dbname  
        END  
  
    CLOSE db_cursor  
    DEALLOCATE db_cursor  

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(d.dbname) 
            FROM #dbstats d
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT collect_date, ' + @cols + ' from 
            (
                select collect_date
                    , size
                    , dbname
                from #dbstats
           ) x
            pivot 
            (
                 max(size)
                for dbname in (' + @cols + ')
            ) p '

exec(@query)
10

Ok... let me give it to you straight. If you store the data horizontally in presentation format, there will be a bit of hell to pay to display it in other manners especially if you also want to display the log file space. I'll also tell you there are 3 other "space" types that you have to be concerned with and you can find out what those are if you lookup sys.master_files in BOL.

My recommendation is to store the data in a normalize form instead of a presentation form and then you can query that data any way that you'd like. If you want some fancy pivot for display purposes, then go "Full Monty" on it and copy the data into Excel (or have Excel read it from a proc) and Pivot it, chart it, and chew it there.

Here's the code I recommend you use, which can be stored in a history table to provide you with plenty of growth data in a really small space.

 SELECT  SampleDT           = GETDATE()
        ,DBID               = database_id
        ,DBName             = DB_NAME(database_id)
        ,DataSizeMB         = CONVERT(DECIMAL(19,3),SUM(CASE WHEN type = 0 THEN size ELSE 0 END)/128.0)
        ,LogSizeMB          = CONVERT(DECIMAL(19,3),SUM(CASE WHEN type = 1 THEN size ELSE 0 END)/128.0)
        ,FileStreamSizeMB   = CONVERT(DECIMAL(19,3),SUM(CASE WHEN type = 2 THEN size ELSE 0 END)/128.0)
        ,FullTextSizeMB     = CONVERT(DECIMAL(19,3),SUM(CASE WHEN type = 4 THEN size ELSE 0 END)/128.0)
        ,InfoSizeMB         = CONVERT(DECIMAL(19,3),SUM(CASE WHEN type = 3 THEN size ELSE 0 END)/128.0)
        ,TotalSizeMB        = CONVERT(DECIMAL(19,3),SUM(size)/128.0)
   FROM sys.master_files
  GROUP BY database_id,type
  ORDER BY DBName   
;
1 Like