question

balanarasimhac-1495 avatar image
0 Votes"
balanarasimhac-1495 asked Yufeishao-msft commented

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

Hi Team,

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

Thanks in advance

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Please do not forget to mark as accepted answer wherever information provided to you were helpful.

Regards

0 Votes 0 ·
pituach avatar image
0 Votes"
pituach answered pituach edited

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



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yufeishao-msft avatar image
0 Votes"
Yufeishao-msft answered

Hi @balanarasimhac-1495

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/

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.