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:
- Data file auto grow events
- Data file auto shrink events
- Logfile auto grow events
- Logfile auto shrink events
Object events
It captures the following object events:
- The object is created
- The object is deleted
- The object is altered
- An index is created, and statistics updates
- The database is deleted
Warnings and errors
It captures the following warnings and errors:
- The SQL Server error log
- The statistics are missing on the column
- The hash warning and sort warning
- The missing join predicates
It also captures other SQL database events, and you can see the entire list by executing the following query:
|
1 |
select * from sys.trace_events order by category_id asc |
The following is the output:
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:
The report contains a list of objects that have been created, altered, or deleted. See the following image:
As mentioned, the default trace is lightweight, but if you want to disable it, you can do it by executing the following queries.
|
1 2 3 4 |
EXEC sp_configure 'default trace enabled', 0; GO RECONFIGURE; GO |
You can view the location of the trace (*.trc) file by executing the following query.
|
1 |
SELECT * FROM :: fn_trace_getinfo(default) |
The following is the output:
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.
|
1 2 3 4 5 6 |
use employees go select file_id, name as [Datafile name], physical_name as [Datafile location], growth*8/1024 as [Datafile growth] from sys.database_files Go |
The following is the output.
I have created a table named EmployeeData in the Employees database. The following script creates a table named EmployeeData in Employees database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
USE EMPLOYEES GO CREATE TABLE EMPLOYEEDATA ( EMPLOYEEID INT, EMPLOYEE_NAME VARCHAR(20), EMPLOYEE_JOB_DESC VARCHAR(20), EMPLOYEE_MANAGER_ID INT, EMPLOYEE_HIRE_DATE DATE, EMPLOYEE_SALARY MONEY, EMPLOYEE_COMMISION MONEY, EMPLOYEE_DEPARTMENT INT ); GO |
Now, to simulate the auto-growth event, execute the following query to insert data in the table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
INSERT INTO [dbo].EmployeeData VALUES (7369, 'NISARG UPADHYAY', 'DBA', 7902, '20-DEC-1980', 8000, NULL, 20); Go 10000 INSERT INTO [dbo].EmployeeData VALUES (7499, 'NIRALI UPADHYAY', 'SALESMAN', 7698, '20-FEB-1981', 1600, 5000, 30); Go 10000 INSERT INTO [dbo].EmployeeData VALUES (7521, 'DIXIT UPADHYAY', 'SENIOR MANAGER', 7698, '05-FEB-1981', 12500, 500, 30); Go 10000 INSERT INTO [dbo].EmployeeData VALUES (7566, 'HEMANT BHATT', 'MANAGER', 7839, '1-APR-1981', 29750, NULL, 20); Go 10000 INSERT INTO [dbo].EmployeeData VALUES (7654, 'USHA BHATT', 'SALESMAN', 7698, '8-SEP-1981', 12500, 1400, 30); Go 10000 INSERT INTO [dbo].EmployeeData VALUES (7698, 'OMKAR BHATT', 'MANAGER', 7839, '10-MAY-1981', 28500, NULL, 30); Go 10000 INSERT INTO [dbo].EmployeeData VALUES (7782, 'SONALI BHATT', 'MANAGER', 7839, '19-JUN-1981', 24500, NULL, 10); Go 10000 INSERT INTO [dbo].EmployeeData VALUES (7788, 'SACHINE BHATT', 'ANALYST', 7566, '01-DEC-1982', 30000, NULL, 20); Go 1000 |
Now, execute the following query to identify the data file auto-growth events that occurred in the Employees database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT TRACEINFO.DATABASENAME , TRACEINFO.NTDOMAINNAME , TRACEINFO.APPLICATIONNAME , TRACEINFO.LOGINNAME , (TRACEINFO.INTEGERDATA*8) AS [GROWTH IN KB], TRACEINFO.STARTTIME AS [TIME WHEN AUTOGROWTH WAS OCCURED] FROM SYS.FN_TRACE_GETTABLE(CONVERT(VARCHAR(150), ( SELECT TOP 1 F.[VALUE] FROM SYS.FN_TRACE_GETINFO(NULL) F WHERE F.PROPERTY = 2 )), DEFAULT) TRACEINFO JOIN SYS.TRACE_EVENTS TRACEEVENTS ON TRACEINFO.EVENTCLASS = TRACEEVENTS.TRACE_EVENT_ID WHERE TRACEEVENTS.NAME = 'DATA FILE AUTO GROW' AND DATABASEID=8 ORDER BY TRACEINFO.STARTTIME ; |
See the following output:
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
USE DBA GO CREATE TABLE TBLAUTOGROWTH ( ID INT IDENTITY(1, 1), DBNAME VARCHAR(150), DOMAINNAME VARCHAR(150), APPLICATIONNAME VARCHAR(500), LOGINNAME VARCHAR(150), DBGROWTH INT, GROWTHOCCUREDTIME DATETIME ) |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
INSERT INTO TBLAUTOGROWTH (DBNAME, DOMAINNAME, APPLICATIONNAME, LOGINNAME, DBGROWTH, GROWTHOCCUREDTIME) SELECT TRACEINFO.DATABASENAME, TRACEINFO.NTDOMAINNAME, TRACEINFO.APPLICATIONNAME, TRACEINFO.LOGINNAME, ( TRACEINFO.INTEGERDATA * 8 ) AS [GROWTH IN KB], TRACEINFO.STARTTIME AS [TIME WHEN AUTOGROWTH WAS OCCURED] FROM SYS.FN_TRACE_GETTABLE(CONVERT(VARCHAR(150), (SELECT TOP 1 F.[VALUE] FROM SYS.FN_TRACE_GETINFO(NULL) F WHERE F.PROPERTY = 2)), DEFAULT) TRACEINFO JOIN SYS.TRACE_EVENTS TRACEEVENTS ON TRACEINFO.EVENTCLASS = TRACEEVENTS.TRACE_EVENT_ID WHERE TRACEEVENTS.NAME = 'DATA FILE AUTO GROW' AND CONVERT(DATE, STARTTIME) = CONVERT(DATE, GETDATE()) ORDER BY TRACEINFO.STARTTIME; |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
DECLARE @SUBJECT NVARCHAR(MAX) DECLARE @TABLEHTML NVARCHAR(MAX) DECLARE @ERRORCOUNT_120 INT SET @SUBJECT = 'DATABASE AUTOGROWTH EVENT IS OCCURED ON : ' + @@SERVERNAME SET @TABLEHTML = ' <HTML> <BODY> <STYLE TYPE="TEXT/CSS"> TABLE {FONT-SIZE:9.0PT;FONT-FAMILY:VERDANA;TEXT-ALIGN:LEFT;} TR {TEXT-ALIGN:LEFT;} H3 { DISPLAY: BLOCK; FONT-SIZE: 15.0PT; FONT-WEIGHT: BOLD; FONT-FAMILY: VERDANA; TEXT-ALIGN:LEFT; } </STYLE> <H3>AUTO-GROWTH EVENT HAS BEEN OCCURED ON FOLLOWING DATABASE OF SERVER '+ @@SERVERNAME + '</H3>' + N'<TABLE BORDER="1">' + N'<TR> <TH>'+ @@SERVERNAME +'</TH> <TH>DBNAME</TH> <TH>APPLICATIONNAME </TH> <TH>LOGINNAME</TH> <TH>DBGROWTH</TH> </TR>' + CAST((SELECT ISNULL(@@SERVERNAME, '') AS 'TD','', ISNULL(DBNAME, '') AS 'TD', '', ISNULL(APPLICATIONNAME, '') AS 'TD', '', ISNULL(LOGINNAME,'')AS'TD', '', ISNULL(DBGROWTH, '') AS 'TD', '' FROM TBLAUTOGROWTH WHERE CONVERT(DATE,GROWTHOCCUREDTIME)=CONVERT(DATE,GETDATE()) FOR XML PATH ( 'TR' ), ROOT) AS NVARCHAR(MAX)) + N'</TABLE> </HTML> </BODY>' EXEC MSDB..SP_SEND_DBMAIL @PROFILE_NAME = 'OUTLOOKMAIL' ,@RECIPIENTS = 'NISARGUPADHYAY87@OUTLOOK.COM' ,@SUBJECT = @SUBJECT ,@IMPORTANCE = 'HIGH' ,@BODY = @TABLEHTML ,@BODY_FORMAT = 'HTML'; |
Below is the entire code of the stored procedure.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
CREATE Procedure sp_getdbautogrowth as begin insert into tblautogrowth (DBName,DomainName,ApplicationName,LoginName,DBGrowth,GrowthOccuredTime) SELECT TraceInfo.DatabaseName , TraceInfo.NTDomainName , TraceInfo.ApplicationName , TraceInfo.LoginName , (TraceInfo.IntegerData*8) as [Growth in KB], TraceInfo.StartTime as [Time when autogrowth was occured] FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1 f.[value] FROM sys.fn_trace_getinfo(NULL) f WHERE f.property = 2 )), DEFAULT) TraceInfo JOIN sys.trace_events TraceEvents ON TraceInfo.EventClass = TraceEvents.trace_event_id WHERE TraceEvents.name = 'Data File Auto Grow' and convert(date,StartTime)=convert(date,getdate()) ORDER BY TraceInfo.StartTime ; DECLARE @subject NVARCHAR(max) DECLARE @tableHTML NVARCHAR(max) DECLARE @ErrorCount_120 INT SET @subject = 'Database Autogrowth event is occured on : ' + @@servername SET @tableHTML = ' <html> <Body> <style type="text/css"> table {font-size:9.0pt;font-family:verdana;text-align:left;} tr {text-align:left;} h3 { display: block; font-size: 15.0pt; font-weight: bold; font-family: verdana; text-align:left; } </style> <H3>Auto-growth event has been occured on following database of server '+ @@servername + '</H3>' + N'<table border="1">' + N'<tr> <th>'+ @@servername +'</th> <th>DBName</th> <th>ApplicationName </th> <th>LoginName</th> <th>DBGrowth</th> </tr>' + Cast((SELECT ISNull(@@ServerName, '') AS 'TD','', Isnull(DBName, '') AS 'TD', '', Isnull(ApplicationName, '') AS 'TD', '', Isnull(LoginName,'')AS'TD', '', Isnull(DBGrowth, '') AS 'TD', '' FROM tblautogrowth WHERE convert(date,GrowthOccuredTime)=convert(date,getdate()-1) FOR xml path ( 'tr' ), root) AS NVARCHAR(max)) + N'</table> </html> </Body>' EXEC msdb..sp_send_dbmail @profile_name = 'Outlookmail' ,@recipients = 'nisargupadhyay87@outlook.com' ,@subject = @subject ,@importance = 'High' ,@body = @tableHTML ,@body_format = 'HTML'; End |
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:
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.
|
1 2 3 |
USE DBA GO EXEC SP_GETDBAUTOGROWTH |
See the following image:
As mentioned, the job will be executed every day at 11 PM; the schedule will be configured as shown below image:
Once the job is created, let us test the job. To do that, right-click on the job and choose Start Job at Step…
If job executes successfully, you will receive the email as shown in the following image:
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.
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
- Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022