Another way to track the database data growth
Sometimes it is required to track growth of the data in the database. Google returned several ways of doing it but they all use msdb.dbo.backupset table. This approach is very good for historical analysis but may not work if you need to check the data growth daily over a short period. msdb.dbo.backupset records the size of the data file but not the actual size of data inside. Hence, even though the data is growing, size of the data file will be shown same until it will be full and grow again.
First script shows actual size of the data in the database, as well as size of allocated storage space. By default, script ignores other file types then ROWS; this can be changed by commenting out line 16.
Second script gives additional breakdown by filegroups.
Note to editor:
Steve,
This is second time I am posting script on your website and second time having the same issue. There is a problem running scripts after copying them from the web page because of the weird Unicode characters added instead of spaces. It is not visible until script is saved as ANSI text. I order to execute the script I need to save it as ANSI txt first, and then replace "?" symbols and only then script is "understood" correctly by SSMS. Can this be looked into please?
Thanks in advance.
CREATE PROCEDURE #uspGetFileStats
@dbName sysname
AS
DECLARE @sqlCmd NVARCHAR(max)
SELECT @sqlCmd=
'Use '+@dbName+'; '+
'SELECT '''+@dbName +''' [DBName]'+
',sum(ROUND((cast ([size] as numeric)*8/1024),2)) [FileSizeMb] '+
',sum(CASE '+
'WHEN FILEPROPERTY([name], ''SpaceUsed'') IS NULL THEN 0 '+/*file is empty*/
'ELSE '+
'ROUND(CAST (FILEPROPERTY([name], ''SpaceUsed'')as numeric)*8/1024,2) '+
'END) [UsedMb] '+
',case when type = 0 then ''ROWS'' when type =1 then ''LOG'' when type=4 then ''FULLTEXT'' end [FileType] '+/*2&3 reserved*/ 'FROM [sys].[database_files] '+
'WHERE type=0 '+
'GROUP BY type'
EXECUTE (@sqlCmd)
GO
DECLARE databaseNamesArr CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT [name]
FROM sys.databases
ORDER BY [name] ASC
DECLARE @dbName NVARCHAR(128)
DECLARE @tFileStats TABLE ([DBName] SYSNAME,[FileSizeMb] NUMERIC(18,2),[UsedSpaceMb]NUMERIC(18,2),[FileType] NVARCHAR (60))
OPEN databaseNamesArr
FETCH NEXT
FROM databaseNamesArr
INTO @dbName
WHILE @@FETCH_STATUS=0
BEGIN
INSERT @tFileStats
(
[DBName]
, [FileSizeMb]
, [UsedSpaceMb]
, [FileType]
)
EXECUTE #uspGetFileStats @dbName
FETCH NEXT
FROM databaseNamesArr
INTO @dbName
END
CLOSE databaseNamesArr
DEALLOCATE databaseNamesArr
SELECT [DBName]
,[UsedSpaceMb]
,[FileSizeMb]
,[FileType]
FROM @tFileStats
GO
DROP PROCEDURE #uspGetFileStats
/********************************************************************/CREATE PROCEDURE #uspGetFileStats
@dbName sysname
AS
DECLARE @sqlCmd NVARCHAR(max)
SELECT @sqlCmd=
'Use '+@dbName+'; '+
'SELECT '''+@dbName +''' [DBName]'+
',[NAME] [FileName] '+
',(ROUND((cast ([size] as numeric)*8/1024),2)) [FileSizeMb] '+
',CASE '+
'WHEN FILEPROPERTY([name], ''SpaceUsed'') IS NULL THEN 0 '+/*file is empty*/
'ELSE '+
'ROUND(CAST (FILEPROPERTY([name], ''SpaceUsed'')as numeric)*8/1024,2) '+
'END [UsedMb] '+
',case when type = 0 then ''ROWS'' when type =1 then ''LOG'' when type=4 then ''FULLTEXT'' end [FileType] '+/*2&3 reserved*/ 'FROM [sys].[database_files] '+
'WHERE type=0 '
EXECUTE (@sqlCmd)
GO
DECLARE databaseNamesArr CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT [name]
FROM sys.databases
ORDER BY [name] ASC
DECLARE @dbName NVARCHAR(128)
DECLARE @tFileStats TABLE ([DBName] SYSNAME,[FileName] sysname, [FileSizeMb] NUMERIC(18,2),[UsedSpaceMb]NUMERIC(18,2),[FileType] NVARCHAR (60))
OPEN databaseNamesArr
FETCH NEXT
FROM databaseNamesArr
INTO @dbName
WHILE @@FETCH_STATUS=0
BEGIN
INSERT @tFileStats
(
[DBName]
, [FileName]
, [FileSizeMb]
, [UsedSpaceMb]
, [FileType]
)
EXECUTE #uspGetFileStats @dbName
FETCH NEXT
FROM databaseNamesArr
INTO @dbName
END
CLOSE databaseNamesArr
DEALLOCATE databaseNamesArr
SELECT [DBName]
,[FileName]
,[UsedSpaceMb]
,[FileSizeMb]
,[FileType]
FROM @tFileStats
GO
DROP PROCEDURE #uspGetFileStats
