May 10, 2011 at 12:00 pm
Hi,
I need to set up an alert.
That alert should send an email to me when the data or log files is full upto 70 % in all of the databases on a server. Can any one please let me know how to set that up.
Thank You,
May 10, 2011 at 12:02 pm
I have SQL Server 2008 R2 enterprise edition.on windows server 2008 enterprise 64 bit
May 10, 2011 at 9:36 pm
Check this Link
http://www.mssqltips.com/tip.asp?tip=1523
You need to use
SQL Server Performance Condition Alert Type
And Object = SQL Server Databases
Counters : DataFile(s) size (KB)
Percentage Log Used
Instance : DB_Name
Thank You,
Best Regards,
SQLBuddy
May 11, 2011 at 11:34 am
when you do that you have to do for each database separately.I know to do that but i need an alert which checks the 70% space for all of the databases . 70 % can be either on data file or on log file
May 11, 2011 at 11:39 am
It's close to what you need. Doing the logs from this would be a bit harder because the logs are always going up and down (unless you forgot to do log backups).
IF OBJECT_ID('tempdb..#dbs') > 0
DROP TABLE #dbs
IF OBJECT_ID('tempdb..#Drives') > 0
DROP TABLE #Drives
IF OBJECT_ID('tempdb..#Results') > 0
DROP TABLE #Results
CREATE TABLE #dbs
(
DBNAME sysname
, DBID INT
, [Total Size in MB] INT
, [Available Space In MB] INT
, DriveLetter CHAR(1)
)
INSERT INTO
#dbs
(
DBNAME
, DBID
, [Total Size in MB]
, [Available Space In MB]
, DriveLetter
)
EXEC sp_MSforeachdb '
USE [?];
SELECT
DB_NAME() As DBNAME
, DB_ID() AS DBID
, SUM(size / 128) AS ''Total Size in MB''
, SUM(size / 128 - CAST(FILEPROPERTY(name , ''SpaceUsed'') AS int) / 128) AS ''Available Space In MB''
, LEFT(physical_name, 1) AS DriveLetter
FROM
[?].sys.database_files
WHERE
type_desc = ''ROWS''
GROUP BY LEFT(physical_name, 1) '
CREATE TABLE #Drives
(
DriverLetter CHAR(1) PRIMARY KEY CLUSTERED
, FreeMBs INT NOT NULL
, FreeGBs AS CONVERT(DECIMAL(18 , 2) , FreeMBs / 1024.0)
)
INSERT INTO
#Drives ( DriverLetter , FreeMBs )
EXEC xp_fixeddrives
--
--SELECT
-- DB_NAME() As DBNAME
-- , DB_ID() AS DBID
-- , SUM(size / 128) AS 'Total Size in MB'
-- , SUM(size / 128 - CAST(FILEPROPERTY(name , 'SpaceUsed') AS int) / 128) AS 'Available Space In MB'
--FROM
-- sys.database_files
--WHERE
-- type_desc = 'ROWS'
--Rémi : I deleted 4 logging tables I had build on March 25th, hence the ±350 MB drop.
;
WITH CTE_Backups ( database_name, BackupDate, MinutesForBackup, GB_backup_size, seqFirst, seqLast )
AS (
SELECT
bs.database_name
, DATEADD(D , 0 , DATEDIFF(D , 0 , bs.backup_start_date)) AS BackupDate
, CONVERT(DECIMAL(18 , 1) , DATEDIFF(s , bs.backup_start_date ,
bs.backup_finish_date)
/ 60.0) AS MinutesForBackup
, CONVERT(DECIMAL(18 , 3) , bs.backup_size / 1024 / 1024 / 1024) AS GB_backup_size
, ROW_NUMBER() OVER ( PARTITION BY bs.database_name ORDER BY bs.backup_start_date ) AS seqFirst
, ROW_NUMBER() OVER ( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC ) AS seqLast
FROM
msdb.dbo.backupset bs
WHERE
name IS NULL
AND bs.[type] = 'D'
)
SELECT
CONVERT(INT , dtBackups.[Available Space In GB]
/ CASE WHEN dtBackups.GB_ExpectedDailyGrowth <> 0
THEN dtBackups.GB_ExpectedDailyGrowth
ELSE 0.0001
END) AS DaysUntillDBGrowth
, *
-- INTO
-- #Results
FROM
(
SELECT
a.database_name
, dbs.DriveLetter
, drv.FreeGBs AS FreeGBs_Drive
, CONVERT(DECIMAL(18 , 1) , ( drv.FreeGBs * 0.85 )
/ CONVERT(DECIMAL(18 , 3) , ( b.GB_backup_size - a.GB_backup_size )
/ DATEDIFF(dd , a.BackupDate , b.BackupDate) * 30.468)) AS FreeGBs_Drive_InMonths_WithExpected_DB_Growth
, a.BackupDate AS BackupDate_First
, b.BackupDate AS BackupDate_Last
, DATEDIFF(dd , a.BackupDate , b.BackupDate) AS DaysPeriod
, a.MinutesForBackup AS MinutesForBackup_First
, b.MinutesForBackup AS MinutesForBackup_Last
, b.MinutesForBackup - a.MinutesForBackup AS MinutesForBackup_Delta
, a.GB_backup_size AS GB_backup_size_First
, b.GB_backup_size AS GB_backup_size_Last
, b.GB_backup_size - a.GB_backup_size AS GB_BackupGrowth
--, a.seqLast - a.seqFirst AS QtyofBackups
, CONVERT(DECIMAL(18 , 3) , ( b.GB_backup_size - a.GB_backup_size )
/ DATEDIFF(dd , a.BackupDate , b.BackupDate)) AS GB_ExpectedDailyGrowth
, CONVERT(DECIMAL(18 , 3) , ( b.GB_backup_size - a.GB_backup_size )
/ DATEDIFF(dd , a.BackupDate , b.BackupDate) * 365.256) AS GB_ExpectedAnnualGrowth
, CONVERT(DECIMAL(18 , 3) , dbs.[Total Size in MB] / 1024.0) AS [Total Size in GB]
, CONVERT(DECIMAL(18 , 3) , dbs.[Available Space In MB] / 1024.0) AS [Available Space In GB]
FROM
CTE_Backups a
INNER JOIN CTE_Backups b
ON a.seqFirst = b.seqLast
AND a.seqLast = b.seqFirst
AND a.database_name = b.database_name
INNER JOIN #dbs dbs
ON b.database_name = dbs.DBNAME
INNER JOIN #Drives drv
ON dbs.DriveLetter = drv.DriverLetter
WHERE
a.seqFirst = 1
AND a.seqFirst < a.seqLast
) dtBackups
ORDER BY
database_name
--
--IF EXISTS ( SELECT
-- *
-- FROM
-- #Results R
-- WHERE
-- R.FreeGBs_Drive_InMonths_WithExpected_DB_Growth >= 0 AND R.FreeGBs_Drive_InMonths_WithExpected_DB_Growth < 1
-- OR R.DaysUntillDBGrowth >= 0 AND R.DaysUntillDBGrowth <= 30 )
-- BEGIN
-- INSERT INTO
-- dbo.RPT_Space_Warnings
-- (
-- [DaysUntillDBGrowth]
-- , [Warning_Description]
-- , [database_name]
-- , [DriveLetter]
-- , [FreeGBs_Drive]
-- , [FreeGBs_Drive_InMonths_WithExpected_DB_Growth]
-- , [BackupDate_First]
-- , [BackupDate_Last]
-- , [DaysPeriod]
-- , [MinutesForBackup_First]
-- , [MinutesForBackup_Last]
-- , [MinutesForBackup_Delta]
-- , [GB_backup_size_First]
-- , [GB_backup_size_Last]
-- , [GB_BackupGrowth]
-- , [GB_ExpectedDailyGrowth]
-- , [GB_ExpectedAnnualGrowth]
-- , [Total Size in GB]
-- , [Available Space In GB]
-- )
-- SELECT
-- [DaysUntillDBGrowth]
-- , CASE WHEN R.FreeGBs_Drive_InMonths_WithExpected_DB_Growth < 1 THEN 'HD IS FULL' ELSE 'AUTOGROWTH WARNING' END AS Warning_Description
-- , [database_name]
-- , [DriveLetter]
-- , [FreeGBs_Drive]
-- , [FreeGBs_Drive_InMonths_WithExpected_DB_Growth]
-- , [BackupDate_First]
-- , [BackupDate_Last]
-- , [DaysPeriod]
-- , [MinutesForBackup_First]
-- , [MinutesForBackup_Last]
-- , [MinutesForBackup_Delta]
-- , [GB_backup_size_First]
-- , [GB_backup_size_Last]
-- , [GB_BackupGrowth]
-- , [GB_ExpectedDailyGrowth]
-- , [GB_ExpectedAnnualGrowth]
-- , [Total Size in GB]
-- , [Available Space In GB]
-- FROM
-- #Results R
-- WHERE
-- R.DaysUntillDBGrowth >= 0 AND R.DaysUntillDBGrowth <= 30
--UNION ALL -- I want to see 2 warnings in the same day when it's the case... those are the really critical events.
-- SELECT
-- [DaysUntillDBGrowth]
-- , CASE WHEN R.FreeGBs_Drive_InMonths_WithExpected_DB_Growth < 1 THEN 'HD IS FULL_' ELSE 'AUTOGROWTH WARNING_' END AS Warning_Description
-- , [database_name]
-- , [DriveLetter]
-- , [FreeGBs_Drive]
-- , [FreeGBs_Drive_InMonths_WithExpected_DB_Growth]
-- , [BackupDate_First]
-- , [BackupDate_Last]
-- , [DaysPeriod]
-- , [MinutesForBackup_First]
-- , [MinutesForBackup_Last]
-- , [MinutesForBackup_Delta]
-- , [GB_backup_size_First]
-- , [GB_backup_size_Last]
-- , [GB_BackupGrowth]
-- , [GB_ExpectedDailyGrowth]
-- , [GB_ExpectedAnnualGrowth]
-- , [Total Size in GB]
-- , [Available Space In GB]
-- FROM
-- #Results R
-- WHERE
-- R.FreeGBs_Drive_InMonths_WithExpected_DB_Growth >= 0 AND R.FreeGBs_Drive_InMonths_WithExpected_DB_Growth <= 1
-- END
--
--IF OBJECT_ID('tempdb..#dbs') > 0
-- DROP TABLE #dbs
--
--IF OBJECT_ID('tempdb..#Drives') > 0
-- DROP TABLE #Drives
--
--IF OBJECT_ID('tempdb..#Results') > 0
-- DROP TABLE #Results
--
May 11, 2011 at 11:00 pm
I am not doing any transactional log backups in my environment
May 12, 2011 at 4:48 am
sql2k8 (5/10/2011)
I have SQL Server 2008 R2 enterprise edition.on windows server 2008 enterprise 64 bit
I did the same in my environment but i don't have the script right now.
You may look this "master.dbo.tbl_get_logfiles_details" and edit the script as you want .Use the "sp_send_dbmail" to get a mail.
If you can't let me know i will write some time.
Edit : fix the url
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
May 12, 2011 at 5:22 am
sql2k8 (5/11/2011)
I am not doing any transactional log backups in my environment
Then you either need to set the db to simple (not bulk nor full) or start taking log backups. There's no 3rd option unless ou want to fill up your drive(s).
May 12, 2011 at 9:29 pm
my databases are already in simple mode
May 12, 2011 at 9:37 pm
Thank You very much for taking your valuable time to help me
May 13, 2011 at 2:47 am
Then you don't need to check the logs at all.
The script I provided an be adapted to warn for 70%.
The only snippet missing is the sendmail which I can provide if you want.
May 13, 2011 at 4:59 pm
Ninja's_RGR'us (5/13/2011)
Then you don't need to check the logs at all.
Maybe. I can think of a number of reasons why log files might fill up, at least temporarily.
My employer's servers use the Simple Recovery model for all databases and I see the log file utilizations expand and contract like accordians.
LC
May 13, 2011 at 6:06 pm
ya but they contract, so the net result is 0 change. And most likely by the time you get the alert there's nothing left to do.
May 14, 2011 at 7:18 pm
Hi,
I am trying to use the information in this link
http://www.mssqltips.com/tip.asp?tip=2359
I will update you once I set this up
Thank You
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply