Monitoring of the size of database files is one of the important DBA tasks and this process should be automated. This article will show you how you can achieve this. Why is it important? Most likely you keep an eye of the size of the database and its files to make sure they don`t exceed the size of the hard drive. But do you track the amount of free space inside the databases files? For example you may have a 100GB database with one data file which is 95Gb and log file of 5Gb. But how much space is free in the file itself? Out of those 95Gb it may be that only 5Gb are filled with the data or may be already 94Gb are used. In the last case your database file may grow soon. To be able to predict such situation and prepare to it in advance let`s setup monitoring.
Firstly, we should create database DBA for different database administration purposes if you haven`t done that before, for example it will be used to collect the data about size of database files. Execute the script below in the Management Studio:
USE [master]
GO
CREATE DATABASE [DBA]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'DBA', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DBA.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512000KB )
LOG ON
( NAME = N'DBA_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DBA_log.ldf' , SIZE = 1024 , MAXSIZE = 2048GB , FILEGROWTH = 512000KB )
GO
Don`t forget to change paths to the files as it is better not to keep the database on the OS disk.
Then we will create table DBA_MonitorDbFilesSpace to collect the data about size of the database files and free space inside of them. The table DBA_MonitorDbFilesSpace will include six columns as shown below:
CREATE TABLE dbo.DBA_MonitorDbFilesSpace (
Id int IDENTITY(1,1) NOT NULL,
the_date datetime NOT NULL,
DatabaseName varchar(255) NOT NULL,
[FileName] varchar(255) NOT NULL,
FileSize float NOT NULL CONSTRAINT DF_FileSize DEFAULT 0,
FreeSpace float NOT NULL CONSTRAINT DF_FreeSpace DEFAULT 0,
CONSTRAINT PK_DBA_MonitorDbFilesSpace PRIMARY KEY
(
Id ASC
)
)
GO
CREATE PROCEDURE dbo.usp_DBA_MonitorDbFilesSpace
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql varchar(max)
, @DBName varchar(255)
DECLARE cur CURSOR FOR
SELECT name
FROM master.sys.databases
WHERE state = 0
OPEN cur
FETCH NEXT FROM cur
INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'USE [' + @DBName + '];
INSERT INTO [DBA].[dbo].[DBA_MonitorDbFilesSpace](the_date, DatabaseName, FileName, FileSize, FreeSpace)
SELECT GETDATE() as the_date, DB_NAME() AS DatabaseName,
name AS FileName,
ROUND(size/128.0, 2) AS FileSizeMB,
ROUND(size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0, 2) AS FreeSpaceMB
FROM sys.database_files;'
EXEC (@sql)
FETCH NEXT FROM cur
INTO @DBName
END
CLOSE cur
DEALLOCATE cur
END
GO
As you can see from the stored procedure code to select information about size of database file we are using the system table sys.database_files for each database and after selecting we are inserting this information into the table DBA_MonitorDbFilesSpace which we created earlier.
As the last step we can create job to execute stored procedure onschedule. Run the following script to create the job, which will execute stored procedure DBA_MonitorDbFilesSpace every hour:
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DBA_MonitorDbFilesSpace',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'XAND\SQLServer',
@notify_email_operator_name=N'dba_group', @job_id = @jobId OUTPUT
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Collect Info About Database Size',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'USE [DBA]
GO
EXECUTE dbo.usp_DBA_MonitorDbFilesSpace
GO',
@database_name=N'DBA',
@flags=0
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Schedule_Every_1_Hour',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=1,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20150901,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'af8bdcd3-4558-40df-b38c-6401b344bd10'
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
When the job is completed successfully, you will see data about size of database files in the table:
Now you are collecting the information about the size of database files and free space inside of them in one place and using this information you can create different reports and alerts for monitoring. So database file growth will not be unexpected.
