Free SQL Help for Devs, DBAs, and the Curious

One of the most critical tasks of a database administrator (DBA) is monitoring and managing database growth. It can be daunting, especially when dealing with large databases and complex systems, like SQL Server 2016, SQL Server 2017, MySQL 5.7, DB2 11.1, or Azure SQL. However, with T-SQL or Transact-SQL, a set of programming extensions from Sybase and Microsoft that add several features to the Structured Query Language (SQL), this task becomes manageable and efficient.

To monitor database growth over time, DBAs can create a scheduled job that runs a T-SQL script, then saves the results to a table. For SQL Server, the script can use the system view sys.master_files to retrieve the size of the database. Below is a simple T-SQL script that gets the database size in SQL Server:

-- Get the size of a database in SQL Server

SELECT DB_NAME(database_id) AS DatabaseName,

    Name AS Logical_Name,

    Physical_Name, (size*8)/1024 SizeMB

FROM sys.master_files

WHERE DB_NAME(database_id) = 'YourDatabaseName'

For MySQL, you can use the information_schema.TABLES to get the database size. Here's a simple script to do this:

-- Get the size of a database in MySQL

SELECT table_schema AS "Database",

    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"

FROM information_schema.TABLES

GROUP BY table_schema;

For DB2, you can use the syscat.tables catalog view to get the size of the database. Here's a simple script to do this:

-- Get the size of a database in DB2

SELECT TABSCHEMA, TABNAME,

    (DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE) AS TOTAL_SIZE

FROM SYSIBMADM.ADMINTABINFO

WHERE TABSCHEMA NOT LIKE 'SYS%';

Azure SQL provides a dynamic management view sys.dm_db_file_space_usage that can be used to monitor database size. Here is a simple script to do this:

-- Get the size of a database in Azure SQL

SELECT DB_NAME() AS [Database Name],

    SUM(size * 8.0 / 1024) AS [Total Size (MB)]

FROM sys.master_files

WHERE type = 0 AND state = 0

GROUP BY database_id;

The output from these scripts will help DBAs to understand the size of their databases, and if scheduled to run regularly, they will provide insights into how the database size is changing over time. This data can then be used to plan for future growth and capacity needs. Monitoring the database size and growth is a critical aspect of database management, and with these T-SQL scripts, you can ensure that your databases continue to perform optimally and efficiently.