Monitoring of free space in database files

,

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:

Database file free space

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.

 

 

Rate

Share

Share

Rate