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:

acyclovir cheap online

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.

 

 

buy generic acyclovir cream onlineaciclovir cream buy online ukwhere can i buy acyclovir cream onlineaciclovir tablets buy online australiaaciclovir tablets 800 mg buy onlinebuy aciclovir tablets online ukbuy acyclovir cream for genital herpeswhere can i buy acyclovir creambuy acyclovir cream 5buy acyclovir cream over the counter

Buy acyclovir eye ointment, Can you buy aciclovir tablets over the counter uk