Disk space report

  • We need to collect the disk space utilization on weekly basis and we have 50+ servers . I need a report of all the servers how much the disk space is utilized , total and free space .

    Please assist on this ..

  • Hi

    I think Management Data Warehouse (MDW) will help you. If you have not much disk space, you can disable query statistics and server activity data collectors (especially query statistics consumes a lot of disk space).

    If you need any help with configuration you can write me PM.

  • ramyours2003 (11/16/2016)


    We need to collect the disk space utilization on weekly basis and we have 50+ servers . I need a report of all the servers how much the disk space is utilized , total and free space .

    Please assist on this ..

    Start by reading this article: Time and Space: How to Monitor Drive Space in SQL Server By Ed Wagner[/url]

    😎

  • Eirikur Eiriksson (11/16/2016)


    ramyours2003 (11/16/2016)


    We need to collect the disk space utilization on weekly basis and we have 50+ servers . I need a report of all the servers how much the disk space is utilized , total and free space .

    Please assist on this ..

    Start by reading this article: Time and Space: How to Monitor Drive Space in SQL Server By Ed Wagner[/url]

    😎

    Thanks for the referral, Eirikur.

  • Ed Wagner (11/16/2016)


    Eirikur Eiriksson (11/16/2016)


    ramyours2003 (11/16/2016)


    We need to collect the disk space utilization on weekly basis and we have 50+ servers . I need a report of all the servers how much the disk space is utilized , total and free space .

    Please assist on this ..

    Start by reading this article: Time and Space: How to Monitor Drive Space in SQL Server By Ed Wagner[/url]

    😎

    Thanks for the referral, Eirikur.

    You are very welcome Ed,

    doesn't make sense writing something up when one has such a good article to refer to;-)

    😎

  • Hi,

    exec xp_fixeddrives;

    This returns the amount of free space on your local drives. It is a good place to start....

    Regards,

    Kev

  • kevaburg (11/16/2016)


    Hi,

    exec xp_fixeddrives;

    This returns the amount of free space on your local drives. It is a good place to start....

    Regards,

    Kev

    It's really not. It only shows free space. It doesn't tell you even tell you the total space available on the drive,

    --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)

  • You can also take the help of this link http://www.sqlserverlogexplorer.com/monitor-sql-server-disk-space-and-implement-alert/[/url] it will show you how to monitor disk space in SQL Server and implement alert system.

  • Jeff Moden (11/16/2016)


    kevaburg (11/16/2016)


    Hi,

    exec xp_fixeddrives;

    This returns the amount of free space on your local drives. It is a good place to start....

    Regards,

    Kev

    It's really not. It only shows free space. It doesn't tell you even tell you the total space available on the drive,

    OK, I should have mentioned that is only free space. I shouldn't respond to threads with a beer in my hand.... 🙂

    But I do have to contend the Point you don't think it is useful. When I read "disk utilisation" I understand that the requirement to be one of several things:

    1. How is the database growing?

    2. How often do I need to add space to a LUN / Partition to keep free space above a certain threshold?

    3. Is the purpose of my Monitor to assess whether too much white space (unnecessary allocation of resources) is available

    4. .....and the list could go on

    The stored procedure can be used (and is by myself) as part of a mechanism to alert me by Email when a threshold has been reached and that additional resources need to be allocated. This information is stored in a permanent table that lets me see how much space over a given time period is consumed.

    It might not be what the OP is looking for but that is my interpretation of the question.....

  • kevaburg (11/17/2016)


    Jeff Moden (11/16/2016)


    kevaburg (11/16/2016)


    Hi,

    exec xp_fixeddrives;

    This returns the amount of free space on your local drives. It is a good place to start....

    Regards,

    Kev

    It's really not. It only shows free space. It doesn't tell you even tell you the total space available on the drive,

    OK, I should have mentioned that is only free space. I shouldn't respond to threads with a beer in my hand.... 🙂

    But I do have to contend the Point you don't think it is useful. When I read "disk utilisation" I understand that the requirement to be one of several things:

    1. How is the database growing?

    2. How often do I need to add space to a LUN / Partition to keep free space above a certain threshold?

    3. Is the purpose of my Monitor to assess whether too much white space (unnecessary allocation of resources) is available

    4. .....and the list could go on

    The stored procedure can be used (and is by myself) as part of a mechanism to alert me by Email when a threshold has been reached and that additional resources need to be allocated. This information is stored in a permanent table that lets me see how much space over a given time period is consumed.

    It might not be what the OP is looking for but that is my interpretation of the question.....

    Take a look at the article Eirikur referenced. It doesn't look at database space, but drive space. You can also use the data it collects to extrapolate when you're going to need to add more space based on N days of history. Since the rate of growth is a constant, I thought it was important to allow using a variable number of days.

  • kevaburg (11/17/2016)


    Jeff Moden (11/16/2016)


    kevaburg (11/16/2016)


    Hi,

    exec xp_fixeddrives;

    This returns the amount of free space on your local drives. It is a good place to start....

    Regards,

    Kev

    It's really not. It only shows free space. It doesn't tell you even tell you the total space available on the drive,

    OK, I should have mentioned that is only free space. I shouldn't respond to threads with a beer in my hand.... 🙂

    But I do have to contend the Point you don't think it is useful. When I read "disk utilisation" I understand that the requirement to be one of several things:

    1. How is the database growing?

    2. How often do I need to add space to a LUN / Partition to keep free space above a certain threshold?

    3. Is the purpose of my Monitor to assess whether too much white space (unnecessary allocation of resources) is available

    4. .....and the list could go on

    The stored procedure can be used (and is by myself) as part of a mechanism to alert me by Email when a threshold has been reached and that additional resources need to be allocated. This information is stored in a permanent table that lets me see how much space over a given time period is consumed.

    It might not be what the OP is looking for but that is my interpretation of the question.....

    Heh... I thought having a beer in your hand while responding to posts was a prerequisite and, if it's not, it should be. 😀

    The reason I suggest that it's not-so-good is that relatively speaking, it means relatively nothing because there is no comparison to the total amount available on the drive nor what the drive is used for.

    For example... our servers are typically setup with a 100GB C: Drive at work. The ONLY thing we put on those drives are OS related software. We don't even put the binaries for SQL Server or the SWAP File there. That means that the size of that disk won't grow by much (if ever after the logs reach the roll-off point) and may show only (for example) 5GB unused. Without knowing more about the total size and use of the disk, you can either take that as a very good number or a very bad number (as in coming close to being out of space). In most cases for us, that's a good number for the C: drive.

    If that number (5GB) of free space occurs on our M: drive, then we're in deep Kimchi because the M: drive is for all the SQL Binaries and the MDF files for all of the databases and is usually slated out as a 2TB drive.

    Either way, though, just knowing the freespace isn't good enough for anything. You have to know a lot more and that's why I say that xp_fixeddrives is mostly a waste of time, especially considering things like WMIC and other things. The only thing that xp_fixeddrives is really good for is knowing whether or not something of a given size has room to fit on a drive. It's not good for alerts, growth monitoring, etc, etc. Yep, it could be combined with other information from other sources but why bother since most of those other sources contain all of the information needed.

    --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)

  • Attached is what I use. The attached code executes from a SQL Server agent job. You will need to add your own instance list processing, and update the smtp server and email information. I have the reporting threshold set at 20, for 20%, but you can adjust that. Setting it to 100 gets you everything.

    I also have a job that collects this information, and stores it in a table, on a weekly basis, to track growth, as well as a reporting script, to create Excel spreadsheets.

    Leonard

  • I have a scheduled job on my SQL 2008 server alert me by email if disk space or % free falls below a threshold. Not sure if powershell is different on 2016.

    CREATE TABLE [dbo].[DiskSpace](

    [drivename] [varchar](255) NULL,

    [capacity_GB] [int] NULL,

    [freespace_GB] [int] NULL,

    [Pct_Free] [decimal](4, 1) NULL

    ) ON [PRIMARY]

    go

    CREATE procedure [dbo].[usp_FreeDiskSpace] as

    /*

    Get free space, put in a table. Another job will call this SP and check the table.

    If the percent free space is low, it will send an email

    */

    declare @svrName varchar(255)

    declare @sql varchar(400)

    --by default it will take the current server name, we can the set the server name as well

    set @svrName = @@SERVERNAME

    set @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'

    --creating a temporary table

    CREATE TABLE #output

    (line varchar(255))

    --inserting disk name, total space and free space value in to temporary table

    insert #output

    EXEC xp_cmdshell @sql

    select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename

    ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,

    (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'capacity_GB'

    ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,

    (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'freespace_GB'

    ,(round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,

    (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)) / (round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,

    (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0)) * 100 as 'Pct_Free'

    into #TempResults

    from #output

    truncate table Maint_Database_Name..DiskSpace -- Maint_Database_Name (MY_Server); Maint_Database_Name (231) or execunet_data_monitor (17)

    insert into Maint_Database_Name..DiskSpace

    select * from #TempResults

    --script to drop the temporary table

    drop table #output

    drop table #TempResults

    --=========================== Email Results if free space is less than 'X'

    declare @RecCount int

    DECLARE @tableHTML NVARCHAR(MAX)

    declare @SubjectText varchar(150)

    declare @BodyText varchar(150)

    declare @PctFree int, @FreeSpace int

    set @PctFree = 10

    set @FreeSpace = 20

    set @SubjectText = 'Disk Space Alert: MY_Server ' + CONVERT(varchar(32), GETDATE(), 101) + ' @ ' + CONVERT(varchar(32), GETDATE(), 108)

    set @BodyText = ' '

    set @BodyText = 'Free Space < ' + cast(@FreeSpace as varchar(4)) + ' Gig or Percent < ' + cast(@PctFree as varchar(4)) + ' %'

    set @RecCount = 0

    set @RecCount = (select COUNT(*) from Maint_Database_Name..DiskSpace

    where freespace_GB < @FreeSpace or pct_free < @PctFree)

    if @RecCount > 0 begin

    SET @tableHTML = @BodyText +

    N'<H1>Disk Space Alert: MY_Server</H1>' +

    N'<table border="1">' +

    N'<tr><th>Drive</th><th>Capacity_GB</th><th>Free_GB</th>' +

    N'<th>PCT_Free</th></tr>' +

    CAST ( ( select td = drivename ,'', td = cast(capacity_GB as char(7)), ' ',

    td = cast(freespace_GB as char(7)) , ' ',

    td = cast(pct_free as char(7)) , ' '

    from Maint_Database_Name..DiskSpace

    where freespace_GB < @FreeSpace or pct_free < @PctFree

    FOR XML PATH('tr'), TYPE

    ) AS NVARCHAR(MAX) ) +

    N'</table>'

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'DBA_Alerts',

    @recipients= 'MyName@MyCompany.com',

    @subject = @SubjectText,

    @body = @tableHTML,

    @body_format = 'HTML',

    @importance = 'High'

    end

  • Jeff Moden (11/18/2016)


    kevaburg (11/17/2016)


    Jeff Moden (11/16/2016)


    kevaburg (11/16/2016)


    Hi,

    exec xp_fixeddrives;

    This returns the amount of free space on your local drives. It is a good place to start....

    Regards,

    Kev

    It's really not. It only shows free space. It doesn't tell you even tell you the total space available on the drive,

    OK, I should have mentioned that is only free space. I shouldn't respond to threads with a beer in my hand.... 🙂

    But I do have to contend the Point you don't think it is useful. When I read "disk utilisation" I understand that the requirement to be one of several things:

    1. How is the database growing?

    2. How often do I need to add space to a LUN / Partition to keep free space above a certain threshold?

    3. Is the purpose of my Monitor to assess whether too much white space (unnecessary allocation of resources) is available

    4. .....and the list could go on

    The stored procedure can be used (and is by myself) as part of a mechanism to alert me by Email when a threshold has been reached and that additional resources need to be allocated. This information is stored in a permanent table that lets me see how much space over a given time period is consumed.

    It might not be what the OP is looking for but that is my interpretation of the question.....

    Heh... I thought having a beer in your hand while responding to posts was a prerequisite and, if it's not, it should be. 😀

    The reason I suggest that it's not-so-good is that relatively speaking, it means relatively nothing because there is no comparison to the total amount available on the drive nor what the drive is used for.

    For example... our servers are typically setup with a 100GB C: Drive at work. The ONLY thing we put on those drives are OS related software. We don't even put the binaries for SQL Server or the SWAP File there. That means that the size of that disk won't grow by much (if ever after the logs reach the roll-off point) and may show only (for example) 5GB unused. Without knowing more about the total size and use of the disk, you can either take that as a very good number or a very bad number (as in coming close to being out of space). In most cases for us, that's a good number for the C: drive.

    If that number (5GB) of free space occurs on our M: drive, then we're in deep Kimchi because the M: drive is for all the SQL Binaries and the MDF files for all of the databases and is usually slated out as a 2TB drive.

    Either way, though, just knowing the freespace isn't good enough for anything. You have to know a lot more and that's why I say that xp_fixeddrives is mostly a waste of time, especially considering things like WMIC and other things. The only thing that xp_fixeddrives is really good for is knowing whether or not something of a given size has room to fit on a drive. It's not good for alerts, growth monitoring, etc, etc. Yep, it could be combined with other information from other sources but why bother since most of those other sources contain all of the information needed.

    I took my time to answer.....I needed to go inside myself a bit.

    Thanks for your Input Jeff.....time to rework the solution I had and get it to produce Information I can actually use.....

Viewing 14 posts - 1 through 13 (of 13 total)

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