I am trying to get the total of file size using following query but don't know how to combine it?
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:
I need total of Tempdev + tempLog so 1024 + 64 = 1088
Thanks Jeff for quick response.
I also tried other as i need to pivot also so i tried as below:
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,
WHEN TYPE = 0 THEN 'DataFile'
END AS FileType
FROM sys.master_files) D
PIVOT ( MAX(SizeInMB)
FOR FileType IN (DataFile,
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:
DB1, DB2, DB3
SELECT [collect_date], DB_NAME(database_id) AS 'DatabaseName', [SIZE] AS 'DBSize'
) AS SourceTable
FOR DatabaseName IN
(DB1, DB2, DB3)
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.
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.
if OBJECT_ID('tempdb..#dbstats') is not null
drop table #dbstats;
declare @dbname varchar(50), @cols AS NVARCHAR(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
from sys.databases d
FETCH NEXT FROM db_cursor INTO @dbname
IF @@FETCH_STATUS <> 0
PRINT ' <<None>>'
WHILE @@FETCH_STATUS = 0
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)
FETCH NEXT FROM db_cursor INTO @dbname
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(d.dbname)
FROM #dbstats d
FOR XML PATH(''), TYPE
set @query = 'SELECT collect_date, ' + @cols + ' from
for dbname in (' + @cols + ')
) p '
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)
GROUP BY database_id,type
ORDER BY DBName