August 25, 2008 at 9:49 am
Hello,
I am working in an environment where disk space frequently runs out.
I need to create a report that will tell FREE DISK SPACE, as well as TOTAL DISK SPACE, for every disk on the system.
xp_fixeddrives returns FREE disk space, but not TOTAL disk space.
xp_cmdshell will not work because this is a high security place.
sp_OACreate, sp_OAMethod, etc. will not work for the same security reasons.
How can I find TOTAL disk space?
Thanks much.
August 25, 2008 at 10:16 am
Unfortunately the WMI counters accessible in SQL do not include this, but your best bet would be to poll the WMI counters and dump them into a table.
August 25, 2008 at 11:31 am
can you point to why the space runs out so frequently?
maybe you just need to perform maintenance on the db.
August 25, 2008 at 11:53 am
I would suggest that you periodically run a collection agent as a BAT job on the server, but outside of SQL Server that collects this information and then use SQLCMD to insert into your tables (or an Import job to bring it in).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 25, 2008 at 12:49 pm
princess.lipscomb (8/25/2008)
can you point to why the space runs out so frequently?maybe you just need to perform maintenance on the db.
Disk space is set to be low...to conserve resources until they are needed.
At the same time, we have growing data stores, as we do a lot of historial logging & trend analysis.
We also have new applications, which are doing more logging & trend analysis.
Lastly, I have about 25 servers to keep track of, and I'd like one sweeping report that gathers & stores free/total disk space, for all server drives, & stores it all into a table so I can query it & produce trends.
So the "Total Space" bit is pretty important.
August 25, 2008 at 12:50 pm
Declare @BytesUsed Varchar(1000),
@BytesFree Varchar(1000),
@TotalBytes BIGINT,
@IDENTITY INT,
@drive Char(1),
@sql Varchar(1000)
SET NOCOUNT ON
Create table ##DiskSpace ( Drive Char(1), TotalSpace Bigint, FreeSpace Bigint,
PercentageFree as (FreeSpace*100 / TotalSpace ) )
Create table #Fixeddrives ( Drive Char(1), FreeSpace Bigint)
create table ##Dir ( ID INT IDENTITY , DriveSize Varchar(2000))
Insert into #Fixeddrives exec master.dbo.xp_fixeddrives
--select * from #Fixeddrives
insert into ##DiskSpace ( Drive , FreeSpace)
select Drive , FreeSpace from #Fixeddrives
-- select * from ##DiskSpace
DECLARE Drive_cursor CURSOR FOR
SELECT Drive from ##DiskSpace
OPEN Drive_cursor
FETCH NEXT FROM Drive_cursor INTO @drive
WHILE @@FETCH_STATUS = 0
BEGIN
select @sql = 'insert into ##Dir exec master.dbo.xp_cmdshell ''dir '+ @drive+':\ /S /-C'''
exec(@sql)
SELECT @IDENTITY = @@IDENTITY
delete from ##Dir where ID < @IDENTITY - 4
select @BytesUsed = substring (drivesize, charIndex ('File(s)', drivesize, 0)+ 9 , 1000)
from ##Dir where drivesize like '%File(s)%'
while patindex('%[^0-9]%', @BytesUsed) > 0
begin
set @BytesUsed = stuff( @BytesUsed, patindex('%[^0-9]%', @BytesUsed), 1, '' )
end
select @BytesFree = substring (drivesize, charIndex ('Dir(s)', drivesize, 0)+ 9 , 1000)
from ##Dir where drivesize like '%Dir(s)%'
while patindex('%[^0-9]%', @BytesFree) > 0
begin
set @BytesFree = stuff( @BytesFree, patindex('%[^0-9]%', @BytesFree), 1, '' )
end
select @TotalBytes = Convert(bigint, @BytesUsed)+ Convert(bigint, @BytesFree)
select @TotalBytes = (@TotalBytes/ 1024)/1024 -- Coverting to MB....
-- select @TotalBytes
Update ##DiskSpace set TotalSpace = @TotalBytes
WHERE Drive = @drive
TRUNCATE TABLE ##Dir
FETCH NEXT FROM Drive_cursor INTO @drive
END
CLOSE Drive_cursor
DEALLOCATE Drive_cursor
select * from ##DiskSpace
HTH
MJ
August 25, 2008 at 12:51 pm
rbarryyoung (8/25/2008)
I would suggest that you periodically run a collection agent as a BAT job on the server, but outside of SQL Server that collects this information and then use SQLCMD to insert into your tables (or an Import job to bring it in).
Okay, you caught my eye. I have questions. 🙂
- What is a collection agent?
- What is a BAT job - a DOS batch file?
- SQLCMD....I think I heard of this years ago. Is that like bcp, reading a text file and bulk-inserting into a raw table?
August 25, 2008 at 1:55 pm
Jason Wisdom (8/25/2008)
Okay, you caught my eye. I have questions. 🙂- What is a collection agent?
It is just something that you write that can run independently of SQL Server.
- What is a BAT job - a DOS batch file?
Yes. Powershell would be even better, if you have it. WHS (Windows Hosting Script) is fine too (that's for VBScript/Javascript).
- SQLCMD....I think I heard of this years ago. Is that like bcp, reading a text file and bulk-inserting into a raw table?
No. SQLCMD is the SQL 2005 command line equivalent of a query window. It replaces OSQL from SQL 2000.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 25, 2008 at 2:00 pm
You know, I had forgotten about this, but you can also setup a Perfmon process to log to SQL Server.
If you set it up to run at system startup and log Logical Disk stats to SQL Server every, say 5 minutes (300 seconds), that should give you what you want.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 26, 2008 at 6:26 am
have you tried sp__diskspace?:unsure:
August 26, 2008 at 6:51 am
CREATE PROCEDURE sp_diskspace
AS
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint ; SET @MB = 1048576
CREATE TABLE #drives (ServerName varchar(15),
drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL,
FreespaceTimestamp DATETIME NULL)
INSERT #drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive
OPEN dcur
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
UPDATE #drives
SET TotalSize=@TotalSize/@MB, ServerName = host_name(), FreespaceTimestamp = (GETDATE())
WHERE drive=@drive
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
SELECT ServerName,
drive,
TotalSize as 'Total(MB)',
FreeSpace as 'Free(MB)',
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)',
FreespaceTimestamp
FROM #drives
ORDER BY drive
DROP TABLE #drives
RETURN
GO
=====
this is a stored proc that was published on this site a few weeks back. lol this is also where i remember seeing the stored proc name "sp_disk__space". try hopefully it will work for you:smooooth:
August 26, 2008 at 9:02 am
all the solutions given use:
xp_cmdshell, sp_OACreate, sp_OAMethod
which you state is not an option.
what about clr procedures? you can access performance counters this way:
PerformanceCounterCategory pcc = new PerformanceCounterCategory("LogicalDisk", SERVER);
PerformanceCounter pcPercentFree = new PerformanceCounter("LogicalDisk", "% Free Space", INSTANCE, SERVER);
PerformanceCounter pcFreeMbytes = new PerformanceCounter("LogicalDisk", "Free Megabytes", INSTANCE, SERVER);
float percentfree = pcPercentFree.NextValue();
float freespace = pcFreeMbytes.NextValue();
float capacity = (freespace * 100) / percentfree;
float usedspace = capacity - freespace;
August 26, 2008 at 9:47 am
rbarryyoung (8/25/2008)
You know, I had forgotten about this, but you can also setup a Perfmon process to log to SQL Server.If you set it up to run at system startup and log Logical Disk stats to SQL Server every, say 5 minutes (300 seconds), that should give you what you want.
Let me know if you want to know how to set this up.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 26, 2008 at 11:44 am
Let me know if you want to know how to set this up.
Actually, yes that would be incredible -
I use Performance Monitor very often, and I usually have 20 counters running.
So far it has always been at the real-time level.
How would I insert its values into a database table?
August 26, 2008 at 9:05 pm
Okay:
First you have to make sure that you have a System DSN that points to the Database where you want to save the performance data. You can set this up unde the Data Source applet in the Control Panel, under Administration Tools.
Then do the following:
1. Start Performance Monitor.
2. Select "Counter Logs" under "Performance Logs and Alerts"
3. From the Action Menu, click "New Log Settings..."
4. Enter a name like "Disk Stats"
5. Click "Add Objects..." on the Dialog that pops-up
6. On the Add Objects dialog, Select the Server Name (use local if you are actually on the server, which is better), then select the "Logical Disk" performance object and click Add, then Close..
7. Change the interval to 3600 seconds (once an hour).
8. Change the Run As to either the local Admin or your username and set the password.
9. Go to the Log Files tab, change the Log File Type to SQL Database
10. Click the Configure... button and select the System DSN that points to your Server & Database.
11. Go to the Schedule tab and set it up to run immediately and to stop Manually.
12. click OK.
The log should start now. If it does not, this is almost always due to a problem with the "Run As.." authorization. Event Viewer can be helpful here.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 44 total)
You must be logged in to reply to this topic. Login to reply