I want to configure an alert which notifies me if any of my database log files size exceeds a certain threshold percentage.

and can u suggest me a best way to do it?

Hi Sindhursai,

According to your description, you want to create an alert to notify yourself when your database log files size exceeds a certain threshold percentage.

I have made a test that configure an alert to alert me when my transaction log files size exceeds a certain threshold percentage, it is possible for you to do so. You can determine the percentage space in a log or data file before create the alert in management studio, code shows as below:

SELECT  name AS 'File Name' , 
 physical_name AS 'Physical Name', 
 size/128 AS 'Total Size in MB',
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB',
round((CAST(FILEPROPERTY(name, 'SpaceUsed') AS float)/size)* 100 ,2) AS  'Percentage Used',
*
FROM sys.database_files;

Detail steps of set up an alert according to the percentage space on a given file.

1.Create procedure that sets counter with value, code shows as below:

DECLARE @FreePercent int

SELECT @FreePercent = 100 - round((CAST(FILEPROPERTY(name, 'SpaceUsed') AS float)/size)* 100 ,2)
FROM sys.database_files
WHERE sys.database_files.name = 'NameOfYourLogOrDataFileHere';

2. Create a scheduled job to run the above procedure.

3. Create SQL agent alert with counter, such that it executes when the free percentage drops below a certain threshold.

4. Configure database mail, test it, and create at least one operator.

5. Enable SQL server agent alert E-mail (properties of agent), and restart the agent.

For more information of percentage of used space for transaction log files of database and alert definition, please refer to this article.

Best Regards,

Ice Fan

Hi,

Refer below links.

Database Log File Alert when used > 70%

Configure Alert for log space used/disk space used in SQL 2005/2008