How to Automate Data Collection on the SQL Server Database Growth

Introduction

Often, there is a need to control the growth of all tables and files of all databases.

In this article, we are going to explore an example of how to automate data collection on the growth of SQL Server database tables and files.

Solution

  1. Create a view about the size of all tables for each database
  2. Create a specific database and determine a table for storing information about the growth of all database tables:
    TotalPageSizeKB indicates the table size.

    The total of TotalPageSizeKB of all the database tables + the size of system tables = the size of database data.

  3. Determine the procedure for collecting the information:

    This solution can be modified to collect data on the sizes of tables of all the databases from all the required instances of MS SQL Server.

  4. Define the view by the collected information:

    Here I would like to draw your attention to Diff. If it is higher than 0, it means that the table grows faster every day.

    The collection is supposed to be made once every 24 hours.

    In the same way, we can automate the collection of the file growth of all the databases using the following view:

Result

In this article, we explored the example of automating data collection about the size and growth of all SQL Server tables and files of all the databases. It provides us with the complete control over changing the size of both the database files and its tables, as well as taking timely measures to reduce the table or file, to increase the data storage device or to split information into multiple data storage devices.

 

Evgeniy Gribkov

Evgeniy Gribkov

Evgeniy is a MS SQL Server database analyst, developer and administrator. He is involved in development and testing of tools for SQL Server database management. Evgeniy also writes SQL Server-related articles.
Evgeniy Gribkov

Evgeniy Gribkov

Evgeniy is a MS SQL Server database analyst, developer and administrator. He is involved in development and testing of tools for SQL Server database management. Evgeniy also writes SQL Server-related articles.