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

Bala Narasimha Challa 466 Reputation points
2021-10-08T10:41:35.117+00:00

Hi Team,

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

Thanks in advance

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,483 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Ronen Ariely 15,081 Reputation points
    2021-10-08T13:04:26.89+00:00

    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
    
    0 comments No comments

  2. Tom Phillips 17,716 Reputation points
    2021-10-08T16:22:54.99+00:00
    0 comments No comments

  3. YufeiShao-msft 7,046 Reputation points
    2021-10-11T07:59:18.793+00:00

    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/

    0 comments No comments