Monitoring of free space in database files

Forrards
March 30, 2023

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.

Forrards
March 30, 2023
Share
Recent articles:
Database consultancy: SaaS database considerations
March 31, 2023

Utilizing given approach each client get his own database. In such case every client`s data stays isolated from others. It simplifies backing up the data, installing new clients but causes higher cost of maintenance.

More
Database consultancy: tree-like structure
March 31, 2023

Subject recursion is well covered in the literature, but, nevertheless, the problem of output “tree” does not mean the client and SQL Server many baffled. So, put the problem: there is a table with the name and record id field indicating the parent identifier. Immediately fill in this table, some sort of test data:

More
Database consultancy: Hierarchy in MS SQL Server
March 31, 2023

In our time of greatest prevalence of databases were relational databases, which are the main targets of the tables and the relationships between them. Tables can solve most problems for data storage and manipulation. But in the real world entity requiring storage is not always presented in a tabular form. One of these very common types of data structures other than the table is a tree structure, where each data element is the parent and the offspring. An example of such a structure may be the structure of state enterprises, which is headed by the director (the root of the tree), his deputies, heads of departments from which are subject to certain deputies, employees of departments, which are subject to the rulers.

More

Subscribe to our blog

    

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Database consultancy: SaaS database considerations

Utilizing given approach each client get his own database. In such case every client`s data stays isolated from others. It simplifies backing up the data, installing new clients but causes higher cost of maintenance.

March 31, 2023
Database consultancy: tree-like structure

Subject recursion is well covered in the literature, but, nevertheless, the problem of output “tree” does not mean the client and SQL Server many baffled. So, put the problem: there is a table with the name and record id field indicating the parent identifier. Immediately fill in this table, some sort of test data:

March 31, 2023

Discuss your project with us

    

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.