How to calculate total size of the database

In my previous articles i have given the basic idea about the converting seconds in to time format and viceversa. In this article i would like to give you multiple examples of how to calculate total size of the database in multiple databases. There are various types of ways and queries we require to use to calculate the total size of the database.

Total size of database : Oracle

In this section i would like you to give information about the total size of database for oracle database. You might know that in oracle we have data files,redo log files,control files and temporary files.The size of databasses is nothing but the total size of all the control files,temporary files,redo log files and data files.

We can calculate the total size of oracle database with using query. The query consist of sum,nvl with using some system tables as follows :

dba_data_files : This is used to take information for Data files of oracle database.

dba_temp_files : These files are used to fetch the total memory information for temporary files.

log_files : We require to use the system view named v_$log to get information about total memory consumed by log_files.

control file : We need to use the memory size for control files with using view named v$controlfile.

We are calculating the size of database in GB.

Following query is very useful to calculate the size of database

select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) “Database_Size_GB”
from
dual;

Total size of database : MySQL

In this section i would like to explain the information to calculate the total size of database : MySQL. The steps are quite different than oracle but in mysql database also we can calculate the total size with using only one query. Following are steps to calculate the total size of the mysql database

Step 1 : Log in to mysql database

Query :

mysql -u username -p

Step 2 :Following query is used to determine the exact size of the database.

SELECT table_schema AS “Database”, ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS “Database size in MB” FROM information_schema.TABLES GROUP BY table_schema;

Step 3 : If user wants to calculate the database information schemawise kindly use following query

SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size in MB"
FROM information_schema.TABLES
WHERE table_schema = "database_name"
ORDER BY (data_length + index_length) DESC;

Here we require to give the table schema name so as to calculate the database size.

Total size of database : Postgresql

Unlike the database size of oracle and mysql we can calculate the total size of the postgresql database.The query is quite simple as compare to other databases.

Query :

SELECT pg_size_pretty( pg_database_size(‘database_name’) );

The database_name is nothing but the name of database for which we need to calculate size.

Total size of database : SQL Server

We can calculate the total size of database in SQL server using multiple ways.

1.Executing the stored procedure named sp_spaceused :

The following query will give information about database size

USE Amit_database;
EXEC sp_spaceused;

Output :
database_name database_size unallocated spac
Amit_database  3172.00 MB   2511.76 MB

2.Executing sp_database procedure

The second stored procedure is sp_databases stored procedure with using which we can get information about databases and its size.

EXEC sp_databases;

Output :

DATABASE_NAME       DATABASE_SIZE  REMARKS
------------------  -------------  -------
Amit_db              9848           null   
Customer             56384          null   
Knowledge            45616          null   
Student              76384          null  

3.Using system views

We can use the system view to fetch the information about size the database.

Query :

SELECT name, size, size * 8/1024 ‘Size_in_MB’, max_size FROM sys.master_files;

output :

name               Size            Size_in_MB
------------------  -------------  -------
Amit_db              9848           9848   
Customer             56384          56384
Knowledge            45616          45616
Student              76384          76384

There is another view named sys.database_files which also will give information about size of database in SQL server.

USE Amit_database;
SELECT
    name,
    size,
    size * 8/1024 'Size_in_MB',
    max_size
FROM sys.database_files;

These are some most important queries in multiple databases which are used to calculate the size of database.