locked
database size in real time? RRS feed

  • Question

  • How many databases we maintain in real time and what is the approximate size of each database?
    • Moved by Tom Phillips Thursday, September 8, 2011 1:45 PM TSQL question (From:SQL Server Database Engine)
    Thursday, September 8, 2011 11:58 AM

Answers

  • Hi,

    I do not know how many databases managed by us and their size :), but I can share a script to see how much data is used by each database in an instance.

    USE msdb;
    GO
    
    IF EXISTS (SELECT 1 FROM sys.objects WHERE [type] = 'U' AND [object_id] = OBJECT_ID('[dbo].[scsDatabaseSize]'))
    	DROP TABLE [dbo].[scsDatabaseSize];
    GO
    
    CREATE TABLE [dbo].[scsDatabaseSize]
    (
    	[DbName] nvarchar(255),
    	[DataFile] bigint,
    	[LogFile] bigint
    )
    
    DECLARE @DbName nvarchar(255);
    
    
    DECLARE DB CURSOR FOR
    SELECT [name] FROM sys.databases;
    OPEN DB;
    FETCH NEXT FROM DB INTO @DbName;
    WHILE @@FETCH_STATUS = 0
    	BEGIN
    		EXEC ('INSERT INTO [msdb].[dbo].[scsDatabaseSize] ([DbName], [DataFile])
    			  SELECT ''' + @DbName + ''', 
    					SUM([size])
    				FROM ' + @DbName + '.sys.database_files
    				WHERE
    					[type_desc] = ''ROWS''
    			  ');
    		
    		EXEC ('INSERT INTO [msdb].[dbo].[scsDatabaseSize] ([DbName], [LogFile])
    			  SELECT ''' + @DbName + ''', 
    					SUM([size])
    				FROM ' + @DbName + '.sys.database_files
    				WHERE
    					[type_desc] = ''LOG''
    			  ');
    		FETCH NEXT FROM DB INTO @DbName;
    	END
    CLOSE DB;
    DEALLOCATE DB;
    
    
    SELECT
    	[DbName] AS [Database Name],
    	SUM([DataFile]) AS [Data size (Kb)],
    	SUM([LogFile]) AS [Log size (Kb)]
    FROM
    	[dbo].[scsDatabaseSize]
    GROUP BY
    	[DbName]
    	
    --cleanup
    DROP TABLE [dbo].[scsDatabaseSize];
    

     

    I hope it helps.

    J.


    There are 10 type of people. Those who understand binary and those who do not.
    • Proposed as answer by Naomi N Thursday, September 8, 2011 1:56 PM
    • Marked as answer by Kalman Toth Tuesday, September 13, 2011 4:52 PM
    Thursday, September 8, 2011 12:39 PM

All replies

  • Hi,

    I do not know how many databases managed by us and their size :), but I can share a script to see how much data is used by each database in an instance.

    USE msdb;
    GO
    
    IF EXISTS (SELECT 1 FROM sys.objects WHERE [type] = 'U' AND [object_id] = OBJECT_ID('[dbo].[scsDatabaseSize]'))
    	DROP TABLE [dbo].[scsDatabaseSize];
    GO
    
    CREATE TABLE [dbo].[scsDatabaseSize]
    (
    	[DbName] nvarchar(255),
    	[DataFile] bigint,
    	[LogFile] bigint
    )
    
    DECLARE @DbName nvarchar(255);
    
    
    DECLARE DB CURSOR FOR
    SELECT [name] FROM sys.databases;
    OPEN DB;
    FETCH NEXT FROM DB INTO @DbName;
    WHILE @@FETCH_STATUS = 0
    	BEGIN
    		EXEC ('INSERT INTO [msdb].[dbo].[scsDatabaseSize] ([DbName], [DataFile])
    			  SELECT ''' + @DbName + ''', 
    					SUM([size])
    				FROM ' + @DbName + '.sys.database_files
    				WHERE
    					[type_desc] = ''ROWS''
    			  ');
    		
    		EXEC ('INSERT INTO [msdb].[dbo].[scsDatabaseSize] ([DbName], [LogFile])
    			  SELECT ''' + @DbName + ''', 
    					SUM([size])
    				FROM ' + @DbName + '.sys.database_files
    				WHERE
    					[type_desc] = ''LOG''
    			  ');
    		FETCH NEXT FROM DB INTO @DbName;
    	END
    CLOSE DB;
    DEALLOCATE DB;
    
    
    SELECT
    	[DbName] AS [Database Name],
    	SUM([DataFile]) AS [Data size (Kb)],
    	SUM([LogFile]) AS [Log size (Kb)]
    FROM
    	[dbo].[scsDatabaseSize]
    GROUP BY
    	[DbName]
    	
    --cleanup
    DROP TABLE [dbo].[scsDatabaseSize];
    

     

    I hope it helps.

    J.


    There are 10 type of people. Those who understand binary and those who do not.
    • Proposed as answer by Naomi N Thursday, September 8, 2011 1:56 PM
    • Marked as answer by Kalman Toth Tuesday, September 13, 2011 4:52 PM
    Thursday, September 8, 2011 12:39 PM
  • SELECT      @@SERVERNAME AS SqlServerInstance,
                db.name AS DatabaseName,
                SUM(CASE WHEN af.groupid = 0 THEN 0 ELSE 8192.0E * af.size / 1048576.0E END) AS DatabaseSize,
                SUM(CASE WHEN af.groupid = 0 THEN 8192.0E * af.size / 1048576.0E ELSE 0 END) AS LogSize,
                SUM(8192.0E * af.size / 1048576.0E) AS TotalSize
    FROM        master..sysdatabases AS db
    INNER JOIN master..sysaltfiles AS af ON af.[dbid] = db.[dbid]
    WHERE       db.name NOT IN('distribution', 'Resource', 'master', 'tempdb', 'model', 'msdb')       -- System databases
    GROUP BY    db.name

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, September 8, 2011 12:42 PM
    Answerer