How To Find Total Size of All Databases in All Instance

 

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:

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.

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.

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

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *