How ro get alerts if mdf and ldf file size more than threshold size

Question

Hi Team,

How to get alerts if mdf and ldf file size more than threshold size.

Thanks in advance

Answer 1

Good day,

Option 1: (push approach - more common) CREATE Extended Events on the event sqlserver.database_file_size_change

This means that each time the file size changed the EE will be fired and the size of the file will be stored in the TARGET.

Full example:

(1) Create folder: `C:_RonenExtendedEventResult``

(2) Give the permission on the folder to the user which is running the SQL Server service. For default user you probably uses: NT Service\MSSQL$SQL2019

(3) Open SSMS and let's create the scenario and solution

USE master
GO

DROP DATABASE IF EXISTS RonenSizeFIlesCheck
GO

CREATE DATABASE RonenSizeFIlesCheck
ON( 
 NAME = Sales_dat,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\RonenSizeFIlesCheck.mdf',
    SIZE = 512KB,
    MAXSIZE = 50MB,
    FILEGROWTH = 50KB
)
LOG ON(
 NAME = Sales_log,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\RonenSizeFIlesCheck.ldf',
    SIZE = 512KB,
    MAXSIZE = 50MB,
    FILEGROWTH = 50KB
) ;
GO

--DROP EVENT SESSION [DB_Size_Tracking]  ON SERVER
--GO

-- CREATE EVENT SESSION 
CREATE EVENT SESSION [DB_Size_Tracking] ON SERVER 
 -- Using Package `sqlserver` to get event `database_file_size_change`
 ADD EVENT sqlserver.database_file_size_change(
 SET collect_database_name=(1)
 ACTION(
 sqlserver.client_app_name,
 sqlserver.client_hostname,
 sqlserver.database_name,
 sqlserver.nt_username,
 sqlserver.plan_handle,
 sqlserver.query_hash,
 sqlserver.query_plan_hash,
 sqlserver.server_principal_name,
 sqlserver.session_id,
 sqlserver.session_nt_username,
 sqlserver.sql_text,
 sqlserver.username
 )
 WHERE(
 [database_name]=N'RonenSizeFIlesCheck'
 )
 )
-- Create folder and give permission to the user which runnning the SQL Server service
-- For default user you probably uses: NT Service\MSSQL$SQL2019
ADD TARGET package0.event_file(SET filename=N'C:\_RonenExtendedEventResult\DB_Size_Tracking.xel')
WITH (
 MAX_MEMORY=4096 KB,
 EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
 MAX_DISPATCH_LATENCY=30 SECONDS,
 MAX_EVENT_SIZE=0 KB,
 MEMORY_PARTITION_MODE=NONE,
 TRACK_CAUSALITY=OFF,
 STARTUP_STATE=ON
)
GO

ALTER EVENT SESSION DB_Size_Tracking ON SERVER STATE = START;
GO
---------------------------------------------------------

use RonenSizeFIlesCheck
GO

SELECT -- DB_NAME(database_id) AS DatabaseName, Physical_Name, 
 [Name] AS Logical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'RonenSizeFIlesCheck' -- filter specific database
GO
/*
Logical_Name SizeMB
Sales_dat 8
Sales_log 0
*/

create table T(id int identity(2,2), txt nchar(4000))
GO

INSERT T(txt) VALUES (N'Ronen')
GO

SELECT * FROM T
GO

INSERT T(txt) 
 SELECT TOP 1000 (N'Ronen Ariely')
 FROM sys.all_objects t1
 CROSS JOIN sys.all_objects t2
GO -- file should grow

SELECT -- DB_NAME(database_id) AS DatabaseName, Physical_Name, 
 [Name] AS Logical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'RonenSizeFIlesCheck' -- filter specific database
GO
/*
Logical_Name SizeMB
Sales_dat 10
Sales_log 12
*/

-- Checvk the EE folder. 
-- A new event file should be created there

You can use service broker in order to send an alert if the size of the file is more than threshold size

Option 2: (Pull approach) You can create a job which check the size of the file each X time

In the job, you can You can get the size of the file using the following query

SELECT DB_NAME(database_id) AS DatabaseName, [Name] AS Logical_Name, Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
-- WHERE DB_NAME(database_id) = 'AdventureWorks' -- filter specific database
GO

Answer 2

Please see:
https://wikidba.net/2018/08/30/log-files-growth-alert-after-defined-threshold/

Answer 3

Hi @Bala Narasimha Challa ，

You can use a SQL Server Job to monitor database file, please open SSMS and right-click the Jobs folder under the SQL Server Agent heading and select new job
Here is the script used in the job step:

IF OBJECT_ID('DatabaseFiles') IS NULL  
 BEGIN  
     SELECT TOP 0 * INTO DatabaseFiles  
     FROM sys.database_files     
     ALTER TABLE DatabaseFiles  
     ADD CreationDate DATETIME DEFAULT(GETDATE())  
 END  
TRUNCATE TABLE DatabaseFiles  
EXECUTE sp_msforeachdb 'INSERT INTO DatabaseFiles SELECT *, GETDATE() FROM [?].sys.database_files'  
IF EXISTS  
(  
        SELECT SizeInMB = CAST(((SIZE * 8.00)/1024.00) AS DECIMAL(18,2))  
        from DatabaseFiles  
        WHERE CAST(((SIZE * 8.00)/1024.00) AS DECIMAL(18,2)) > 5000  
)  
BEGIN  
        EXECUTE msdb.dbo.sp_send_dbmail  
        @recipients=N'chapman.tim@gmail.com',  
        @body='The size of one or more databases have grown outside of the expected bounds.',  
        @subject ='Database(s) possibly need attention.',  
        @profile_name ='Database-mailProfile';  
END  

https://www.techrepublic.com/blog/the-enterprise-cloud/monitor-database-file-sizes-with-sql-server-jobs/