Post reply

Time and Space: How to Monitor Drive Space in SQL Server

  • September 18, 2019 at 9:34 pm

    #3681240

    cteDailyGrowth AS (

    --using the row number, join the drive free table to itself, offset by one day. this gets the free

    -- space for each drive on that day and also on the next day.

    SELECT di1.RowNum, di1.EntryDate, di1.DataFree CurrentData, di1.LogFree CurrentLog, di1.BackupFree CurrentBackup,

    di2.DataFree NextData, di2.LogFree NextLog, di2.BackupFree NextBackup

    FROM cteDriveInfo di1

    INNER JOIN cteDriveInfo di2 ON di2.RowNum = di1.RowNum + 1

  • April 21, 2024 at 9:58 pm

    #4413836

    Sam Catchesides wrote:

    I implemented a similar solution at work, logging the disk free space (along with database free space) to a table along with when it was recorded. Simple SSRS report front end to graph out the last 30 days is emailed every morning, and the report allows selection of longer time periods and more specific drives/servers. Works well for us, and we can at a glance see if there is a worrying trend in disk usage occurring, and then we can drill down and see if a specific database is causing it. Has saved our bacon a couple of times.

    I know this is an older post but it's still highly appropriate.  This is a cool idea and I think you should write an article on it, if you haven't already.  If you already have, I'd love to see the link.  If you haven't I'd love to goad you into doing so.  It would help a lot of folks out.

    --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 22, 2024 at 7:39 am

    #4413952

    Jeff Moden wrote:

    Sam Catchesides wrote:

    I implemented a similar solution at work, logging the disk free space (along with database free space) to a table along with when it was recorded. Simple SSRS report front end to graph out the last 30 days is emailed every morning, and the report allows selection of longer time periods and more specific drives/servers. Works well for us, and we can at a glance see if there is a worrying trend in disk usage occurring, and then we can drill down and see if a specific database is causing it. Has saved our bacon a couple of times.

    I know this is an older post but it's still highly appropriate.  This is a cool idea and I think you should write an article on it, if you haven't already.  If you already have, I'd love to see the link.  If you haven't I'd love to goad you into doing so.  It would help a lot of folks out.

    SQL agent job running posh cmd is best way these days, to get any OS related info including drive space.

    ive started building a warehouse that uses CMS and each remote instance has a linked server and agent jobs which fire into tables on a db on the CMS instance

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • April 22, 2024 at 1:08 pm

    #4414081

    Perry Whittle wrote:

    CMS

    I'm unfamiliar with this acronym.

    412-977-3526 call/text

  • April 22, 2024 at 1:23 pm

    #4414087

    Central Management Server

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • April 22, 2024 at 1:29 pm

    #4414091

    Thanks.

    Is there a standard schema for the tables?

    412-977-3526 call/text

  • April 22, 2024 at 6:54 pm

    #4414203

    Whatever schema you want them to be

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • April 23, 2024 at 1:20 am

    #4414303

    Perry Whittle wrote:

    Jeff Moden wrote:

    Sam Catchesides wrote:

    I implemented a similar solution at work, logging the disk free space (along with database free space) to a table along with when it was recorded. Simple SSRS report front end to graph out the last 30 days is emailed every morning, and the report allows selection of longer time periods and more specific drives/servers. Works well for us, and we can at a glance see if there is a worrying trend in disk usage occurring, and then we can drill down and see if a specific database is causing it. Has saved our bacon a couple of times.

    I know this is an older post but it's still highly appropriate.  This is a cool idea and I think you should write an article on it, if you haven't already.  If you already have, I'd love to see the link.  If you haven't I'd love to goad you into doing so.  It would help a lot of folks out.

    SQL agent job running posh cmd is best way these days, to get any OS related info including drive space.

    ive started building a warehouse that uses CMS and each remote instance has a linked server and agent jobs which fire into tables on a db on the CMS instance

    Is that Posh command a call to WMIC VOLUME?  If so, can you demonstrate how you're parsing the data to get it to a table?

     

    • This reply was modified 1 week, 6 days ago by  Jeff Moden.

    --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 23, 2024 at 8:59 am

    #4414432

    many different ways of doing it using various cmdlets, had been trying get-volume and get-wmiobject, get-ciminstance seems to be preferred though, looking to make use of the sql table target cmdlets to get the data into a database

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • April 23, 2024 at 2:42 pm

    #4414587

    what would have been a better way to ask this question?

    Is there a standard schema for the tables?

     

    412-977-3526 call/text

  • April 23, 2024 at 2:52 pm

    #4414590

    it all depends on what data you wish to harvest and build the schema to match, there's no offering out of the box that i've seen

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 11 posts - 46 through 55 (of 55 total)

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