Post reply

Data and Log Space Alerts

  • May 11, 2011 at 11:42 am

    #389503

    Looking for a short article on setting up an alert in SQL Agent that pings someone when a data file reaches 80% full (or a log file). Short example, walk them through in SSMS and/or T-SQL

  • May 11, 2011 at 11:48 am

    #1323293

    Close to what you need?

    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

    --

    ROLLBACK

  • May 11, 2011 at 12:37 pm

    #1323313

    I can do the article if Ninja's solution isn't what you're looking.

    ----------------------------------------------------------------------------
    Sacramento SQL Server users group - http://sac.sqlpass.org
    Follow me on Twitter - @SQLDCH
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.

  • May 11, 2011 at 1:32 pm

    #1323343

    That isn't an article, and I'm looking to use the alerts that are built into SQL Server. It's more about alerts than space, and it's to put the content out there for beginners.

  • May 11, 2011 at 2:10 pm

    #1323358

    ok, I had left out the job part of it but if this is too much, I'll let it go.

  • May 17, 2011 at 6:38 pm

    #1326205

    SQLDCH (5/11/2011)

    I can do the article if Ninja's solution isn't what you're looking.

    It looks like you're on! 🙂

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 18, 2011 at 5:24 am

    #1326338

    Yup...

  • May 18, 2011 at 11:05 pm

    #1326886

    I'm on it, thanks dudes.

    ----------------------------------------------------------------------------
    Sacramento SQL Server users group - http://sac.sqlpass.org
    Follow me on Twitter - @SQLDCH
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.

  • January 31, 2012 at 9:46 am

    #1440762

    My apologies, this fell through the cracks. It's back on the radar again, will have something soon.

    ----------------------------------------------------------------------------
    Sacramento SQL Server users group - http://sac.sqlpass.org
    Follow me on Twitter - @SQLDCH
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.

  • April 10, 2012 at 12:46 pm

    #1471433

    Hey, hope I am not stepping on anyone's toes here.

    I have been looking into setting this up, so documented my steps and submitted an article.

    It is my 1st, so please be gentle. 🙂

    Hope it is along the lines of what you were looking for.

    Thanks.

  • April 10, 2012 at 12:58 pm

    #1471448

    Thanks, we'll take a look in a week or so. No one's submitted one yet.

  • April 10, 2012 at 1:20 pm

    #1471474

    I snooze, I lose!

    I'm just glad it got written. Take all the feedback with an open mind, it'll only make you better in the long run.

    ----------------------------------------------------------------------------
    Sacramento SQL Server users group - http://sac.sqlpass.org
    Follow me on Twitter - @SQLDCH
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.

  • April 10, 2012 at 4:04 pm

    #1471583

    simonrichards74 (4/10/2012)

    Hey, hope I am not stepping on anyone's toes here.

    I have been looking into setting this up, so documented my steps and submitted an article.

    It is my 1st, so please be gentle. 🙂

    Hope it is along the lines of what you were looking for.

    Thanks.

    I'd be happy to review what you have if you'd like to forward it as a WORD (2003 or less, please) document.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 10, 2012 at 5:58 pm

    #1471629

    Jeff Moden (4/10/2012)

    simonrichards74 (4/10/2012)

    Hey, hope I am not stepping on anyone's toes here.

    I have been looking into setting this up, so documented my steps and submitted an article.

    It is my 1st, so please be gentle. 🙂

    Hope it is along the lines of what you were looking for.

    Thanks.

    I'd be happy to review what you have if you'd like to forward it as a WORD (2003 or less, please) document.

    Thanks Jeff, I have dropped you a PM, any advice / critique / recommendations greatly appreciated.

  • April 10, 2012 at 6:01 pm

    #1471632

    SQLDCH (4/10/2012)

    I snooze, I lose!

    I'm just glad it got written. Take all the feedback with an open mind, it'll only make you better in the long run.

    Thanks, I will welcome any constructive criticism with open arms, Happy to take help where I can.

    Simon

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy