November 16, 2016 at 12:28 am
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 ..
November 16, 2016 at 1:55 am
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.
November 16, 2016 at 3:12 am
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]
😎
November 16, 2016 at 5:22 am
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.
November 16, 2016 at 6:27 am
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;-)
😎
November 16, 2016 at 11:24 am
Hi,
exec xp_fixeddrives;
This returns the amount of free space on your local drives. It is a good place to start....
Regards,
Kev
November 16, 2016 at 7:47 pm
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
Change is inevitable... Change for the better is not.
November 16, 2016 at 10:09 pm
November 17, 2016 at 4:10 am
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.....
November 17, 2016 at 5:16 am
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.
November 18, 2016 at 12:09 am
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
Change is inevitable... Change for the better is not.
November 18, 2016 at 6:22 am
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
November 18, 2016 at 8:34 am
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
December 2, 2016 at 6:06 am
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