Nisarg Upadhyay
Auto growth occurred on SQL Database

Monitor the growth of SQL databases using the Default Trace

July 23, 2020 by

Monitoring the growth of the SQL Database is one of the essential tasks of the SQL Server DBA. In this article, I am going to explain how we can monitor the growth of the SQL database using the default trace. First, let me explain the default trace in SQL Server.

Default Trace

SQL Server default trace was added as a feature in SQL Server 2005. It is a lightweight trace, and it contains five trace files. The default trace captures the following events:

Database events

It captures the following database events:

  1. Data file auto grow events
  2. Data file auto shrink events
  3. Logfile auto grow events
  4. Logfile auto shrink events

Object events

It captures the following object events:

  1. The object is created
  2. The object is deleted
  3. The object is altered
  4. An index is created, and statistics updates
  5. The database is deleted

Warnings and errors

It captures the following warnings and errors:

  1. The SQL Server error log
  2. The statistics are missing on the column
  3. The hash warning and sort warning
  4. The missing join predicates

It also captures other SQL database events, and you can see the entire list by executing the following query:

The following is the output:

List of trace events

If the default trace is running, then you can view the schema change report from the SQL Server management studio (SSMS). To do that, launch SQL Server management studio -> connect to the database engine -> right-click on the desired database -> hover on Reports -> hover on Standard Reports -> select Schema Changes History“. See the following image:

SQL Server management reports

The report contains a list of objects that have been created, altered, or deleted. See the following image:

Schema change report

As mentioned, the default trace is lightweight, but if you want to disable it, you can do it by executing the following queries.

You can view the location of the trace (*.trc) file by executing the following query.

The following is the output:

Default trace details

Monitor growth of databases

To demonstrate the scenario, I have created a database named Employee on my workstation. The size of the data file is 8 MB. The auto-growth value of the data file is 1 MB means that once the data file reaches the maximum size, an auto-growth event occurs, and it allocates 1MB in data file size.

The following is the output.

Datafile size of SQL Database

I have created a table named EmployeeData in the Employees database. The following script creates a table named EmployeeData in Employees database.

Now, to simulate the auto-growth event, execute the following query to insert data in the table.

Now, execute the following query to identify the data file auto-growth events that occurred in the Employees database.

See the following output:

List of auto growth events of SQL Database

As you can see, after inserting the data, the data file events have occurred.

To track the auto-growth, we will create a SQL job that will email the list of the databases that have grown at the end of the day. To implement that, I have created a table named tblautogrowth in the DBA database. The following is the code.

I have created a stored procedure named sp_getdbautogrowth in the DBA database. The stored procedure inserts the output generated by the following query in the tblautogrowth table.

We will use the SQL Server database mail to send the email of the auto-growth event to DBA. The email is in tabular format so, code embeds the output of the SELECT query in HTML formatted string and stores it in @HTMLCommand variable. The datatype of the @HTMLCommand is nvarchar(max). The following is the code:

Below is the entire code of the stored procedure.

To automate the process, we will use the SQL Server agent job. This job executes every day at 11 PM and sends the list of databases. To create a SQL job, Open SQL Server management studio Connect to the database engine Right-click on SQL Server Agent Right-click on Jobs and click on New Job. See the following image:

Create SQL Job

In a new job dialog box, provide the desired name of the job. In the New Job Step dialog box, provide the desired name of the job step In Type drop-down box, select Transact-SQL script In the command text box, enter the following query.

See the following image:

Job step

As mentioned, the job will be executed every day at 11 PM; the schedule will be configured as shown below image:

Job Schedule

Once the job is created, let us test the job. To do that, right-click on the job and choose Start Job at Step

Test the SQL Job

If job executes successfully, you will receive the email as shown in the following image:

Auto growth occurred on SQL Database

Summary

In this article, I have explained the default trace in SQL Server and how we can utilize it to monitor the growth of SQL databases and automate the process to get the list of SQL databases on which the auto-growth has occurred.

Nisarg Upadhyay
Jobs, Maintenance, Monitoring

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

222 Views