| Author |
Topic |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
 Posted - 2007-11-13 : 11:32:27
|
[code]-- Initialize Control Mechanism
DECLARE @Drive TINYINT,
@SQL VARCHAR(100)
SET @Drive = 97
-- Setup Staging Area
DECLARE @Drives TABLE
(
Drive CHAR(1),
Info VARCHAR(80)
)
WHILE @Drive <= 122
BEGIN
SET @SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR(@Drive) + ':'''
INSERT @Drives
(
Info
)
EXEC (@SQL)
UPDATE @Drives
SET Drive = CHAR(@Drive)
WHERE Drive IS NULL
SET @Drive = @Drive + 1
END
-- Show the expected output
SELECT Drive,
SUM(CASE WHEN Info LIKE 'Total # of bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TotalBytes,
SUM(CASE WHEN Info LIKE 'Total # of free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FreeBytes,
SUM(CASE WHEN Info LIKE 'Total # of avail free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS AvailFreeBytes
FROM (
SELECT Drive,
Info
FROM @Drives
WHERE Info LIKE 'Total # of %'
) AS d
GROUP BY Drive
ORDER BY Drive[/code]
E 12°55'05.25"
N 56°04'39.16" |
|
|
gvphubli
Yak Posting Veteran
54 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-18 : 15:26:10
|
The problem with both of these approaches is that they do not get the information for mount points, which is now supported in SQL Server 2005. I had to create a .NET CLR function that called Performance Monitor counters to get the disk information since that's the only place where I could find the information about mount points.
I'll eventually get around to posting my solution on my blog.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/ |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-18 : 15:31:36
|
http://en.wikipedia.org/wiki/Volume_Mount_Point
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
RyanAustin
Yak Posting Veteran
50 Posts |
Posted - 2008-03-25 : 19:03:35
|
| The compiled dll works great for a 2005 SQL instance, but is there anything that would work for a 2000 instance?? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-25 : 20:32:16
|
As long as you create the CLR on a 2005 server and then point it at a 2000 instance, it will be able to pull the information since I'm just getting the data from a Performance Monitor object.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/ |
|
|
|
PABluesMan
Starting Member
26 Posts |
Posted - 2008-03-26 : 12:00:54
|
Another big problem is that it requires the use of [xp_cmdshell]. I generally like to keep this disabled for security purposes, so this procedure would not work at all in those instances.
I've come up with an alternative that does not require [xp_cmdshell]. It's definitely not pretty, but it works ... sort of (some of the properties it tries to get can't be retrieved, depending on whether or not the file is local, the OS version, etc.). However, it's been working for me for some time.
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=99653[/url]
Hope this helps!
I geek, therefore I am |
|
|
|
Haywood
Posting Yak Master
221 Posts |
Posted - 2008-03-26 : 12:29:53
|
Using the sp_OA methods...this can be saved to a file and then used by osql/sqlcmd which can in turn be used in a FOR loop from the cmdshell...
/******************************************************************************
**
**
** Author: G. Rayburn
**
** Date: 11/01/2005
**
** ToDo:
**
**
*******************************************************************************
** Modification History
*******************************************************************************
**
** Initial Creation: 11/01/2005 G. Rayburn
**
*******************************************************************************
**
******************************************************************************/
SET NOCOUNT ON
IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveSpace')
DROP TABLE ##_DriveSpace
IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveInfo')
DROP TABLE ##_DriveInfo
DECLARE @Result INT
, @objFSO INT
, @Drv INT
, @cDrive VARCHAR(13)
, @Size VARCHAR(50)
, @Free VARCHAR(50)
, @Label varchar(10)
CREATE TABLE ##_DriveSpace
(
DriveLetter CHAR(1) not null
, FreeSpace VARCHAR(10) not null
)
CREATE TABLE ##_DriveInfo
(
DriveLetter CHAR(1)
, TotalSpace bigint
, FreeSpace bigint
, Label varchar(10)
)
INSERT INTO ##_DriveSpace
EXEC master.dbo.xp_fixeddrives
-- Iterate through drive letters.
DECLARE curDriveLetters CURSOR
FOR SELECT driveletter FROM ##_DriveSpace
DECLARE @DriveLetter char(1)
OPEN curDriveLetters
FETCH NEXT FROM curDriveLetters INTO @DriveLetter
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SET @cDrive = 'GetDrive("' + @DriveLetter + '")'
EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @objFSO OUTPUT
IF @Result = 0
EXEC @Result = sp_OAMethod @objFSO, @cDrive, @Drv OUTPUT
IF @Result = 0
EXEC @Result = sp_OAGetProperty @Drv,'TotalSize', @Size OUTPUT
IF @Result = 0
EXEC @Result = sp_OAGetProperty @Drv,'FreeSpace', @Free OUTPUT
IF @Result = 0
EXEC @Result = sp_OAGetProperty @Drv,'VolumeName', @Label OUTPUT
IF @Result <> 0
EXEC sp_OADestroy @Drv
EXEC sp_OADestroy @objFSO
SET @Size = (CONVERT(BIGINT,@Size) / 1048576 )
SET @Free = (CONVERT(BIGINT,@Free) / 1048576 )
INSERT INTO ##_DriveInfo
VALUES (@DriveLetter, @Size, @Free, @Label)
END
FETCH NEXT FROM curDriveLetters INTO @DriveLetter
END
CLOSE curDriveLetters
DEALLOCATE curDriveLetters
PRINT 'Drive information for server ' + @@SERVERNAME + '.'
PRINT ''
-- Produce report.
SELECT DriveLetter
, Label
, FreeSpace AS [FreeSpace MB]
, (TotalSpace - FreeSpace) AS [UsedSpace MB]
, TotalSpace AS [TotalSpace MB]
, ((CONVERT(NUMERIC(9,0),FreeSpace) / CONVERT(NUMERIC(9,0),TotalSpace)) * 100) AS [Percentage Free]
FROM ##_DriveInfo
ORDER BY [DriveLetter] ASC
GO
DROP TABLE ##_DriveSpace
DROP TABLE ##_DriveInfo
|
|
|
|
mikeyw
Starting Member
1 Post |
Posted - 2013-01-10 : 09:23:35
|
in SQL2008 R2 SP1 onwards just use the new DMF / DMV :
SELECT DB_NAME(f.database_id) DatabaseName,
f.FILE_ID, size DBSize, file_system_type,
volume_mount_point, total_bytes, available_bytes
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.FILE_ID); |
|
|
|
HGanesh
Starting Member
1 Post |
Posted - 2013-04-29 : 06:00:34
|
Hi Haywood,
The above written script is just awesome. But i have one query here...
In our environment there is one monitoring server from where all the server are being monitored. When I use this query it just fetches the current server's drive details. Could you please throw some light on how i run this OLE Automation scripts for all the servers. I want pass @server as server name which i can get from master database from another SP. |
|
|
|
Rasta Pickles
Posting Yak Master
174 Posts |
Posted - 2013-06-01 : 11:40:14
|
Haywood's script generates
SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.
This is an instance of SSMS 2008R2 running on my own pc?!
What "security configuration"? This is my machine FFS? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-06-04 : 15:58:20
|
quote:
Originally posted by Rasta Pickles
Haywood's script generates
SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.
This is an instance of SSMS 2008R2 running on my own pc?!
What "security configuration"? This is my machine FFS?
You would run sp_configure to enable the option. You can also do it in Surface Area Configuration. The error message tells you what to enable.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Subscribe to my blog |
|
|
|
gsa
Starting Member
2 Posts |
Posted - 2013-12-02 : 12:23:25
|
Hi,
Please I wish to exclude from the drive list one drive disk - D : who is possible?
Thanks
Hadrian |
|
|
|
gsa
Starting Member
2 Posts |
Posted - 2013-12-03 : 05:44:48
|
Hi,
Please I wish to exclude a disk from the drive list.It's possible?
Thanks,
Hadrian |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-12-03 : 12:19:33
|
quote:
Originally posted by gsa
Hi,
Please I wish to exclude a disk from the drive list.It's possible?
Thanks,
Hadrian
There are several scripts in this thread, so we are unsure which one you are referring to. It would be best if you started a new thread on this topic and showed us which query you want to use/modify.
Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/ |
|
|
|
Goondla Siva
Starting Member
1 Post |
|
|
|
Get total disk size and free disk space