Finding the total size of all your databases may be a bit difficult when your number of instances and your database count increases. Sometimes your manager can want to information from you about the total size of sql server databases.
In this case, if you have a few number of instance and you are not using one of the high availability solutions, you can find the database sizes by running the following query in each instance using the following script.
I will give the script that find total size of all databases at the end of the article.
Database Sizes in a Standalone Instance:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
WITH X AS ( select database_id, type, size * 8.0 / 1024/1024 size from sys.master_files ) SELECT NAME AS Database_Name, (SELECT SUM(size) from X where type = 0 and X.database_id = db.database_id) + (SELECT SUM(size) from X where type = 1 and X.database_id = db.database_id) Database_Size_GB FROM sys.databases db WHERE (SELECT SUM(size) from X where type = 1 and X.database_id = db.database_id) is not null ORDER BY Database_Size_GB desc |
Some systems that use availability group also have databases that are not included in the availability group. That’s why we need to modify the script a little.
When we change the script as follows, it lists the names and sizes of all active databases on that instance.
A database included in the Availability Group is listed if it is a primary. Standalone databases are also included in the list.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
WITH X AS ( SELECT database_id, type, size * 8.0 / 1024/1024 size FROM sys.master_files where database_id IN ( SELECT DISTINCT DB_ID(dbcs.database_name) AS database_id FROM master.sys.availability_groups AS AG LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates ON AG.group_id = agstates.group_id INNER JOIN master.sys.availability_replicas AS AR ON AG.group_id = AR.group_id INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1 INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs ON arstates.replica_id = dbcs.replica_id LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id WHERE ISNULL(arstates.role, 3) = 1 UNION ALL SELECT DB_ID(name) database_id from sys.databases WHERE replica_id is null and database_id >4 ) ) SELECT NAME, (SELECT SUM(size) from X where type = 0 and X.database_id = db.database_id) + (select SUM(size) from X where type = 1 and X.database_id = db.database_id) Database_Size_GB FROM sys.databases db WHERE (select SUM(size) from X where type = 1 and X.database_id = db.database_id) is not null ORDER BY Database_Size_GB DESC |
You must run this script in both the primary and secondary instance in instances that use availability group, and you must sum the result.
Let’s make our work a little easier. Instead of calculating the total database size on that instance manually, calculate it with the cursor. The following script gives the total size of all databases on an instance.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
DECLARE @size int DECLARE @total_size int set @total_size=0 DECLARE MyCursor CURSOR FOR with X as ( SELECT database_id, type, size * 8.0 / 1024/1024 size FROM sys.master_files WHERE database_id IN ( SELECT DISTINCT DB_ID(dbcs.database_name) AS database_id FROM master.sys.availability_groups AS AG LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates ON AG.group_id = agstates.group_id INNER JOIN master.sys.availability_replicas AS AR ON AG.group_id = AR.group_id INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1 INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs ON arstates.replica_id = dbcs.replica_id LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id WHERE ISNULL(arstates.role, 3) = 1 union all SELECT DB_ID(name) database_id FROM sys.databases WHERE replica_id is null and database_id >4 ) ) SELECT (SELECT SUM(size) FROM X WHERE type = 0 and X.database_id = db.database_id) + (SELECT SUM(size) FROM X WHERE type = 1 and X.database_id = db.database_id) Database_Size_GB FROM sys.databases db WHERE (SELECT SUM(size) FROM X WHERE type = 1 and X.database_id = db.database_id) is not null ORDER BY Database_Size_GB DESC OPEN MyCursor FETCH NEXT FROM MyCursor INTO @size WHILE @@FETCH_STATUS = 0 BEGIN SET @total_size= @total_size+@size FETCH NEXT FROM MyCursor INTO @size END CLOSE MyCursor DEALLOCATE MyCursor SELECT @total_size AS Total_Database_Size_GB |
You can see the screen output of the result of the above script in the screenshot below. By calculating the sum of the results from the previous script, the total size on that instance is returned as a result.
If you have more instances, you can use the Registered Server instead of running this script by connecting to each instance. You can find the article about the Registered Server below.
“How To Execute a Script on Multiple Instance with Registered Server”
