When I am not blogging, I am typically working on SQL Server Optimization projects. Time and again, I only have access to SQL Server Management Studio that I can remotely connect to server but do not have access to Operating System, and it works just fine.
At one point in optimization project, I have to decide on index filegroup placement as well TempDB files (.ldf and .mdf) placement. It is commonly known that system gives enhanced performance when index and tempdb are on separate drives than where the main database is placed.
As I do not have access to OS I use the following Stored Procedure to figure out the number of fixed drives (hard drive) a system has along with free space on each of those drives. Based on this result, I decide my strategy of where to put my indexes and tempdb.
EXEC master..xp_fixeddrives
Reference : Pinal Dave (https://blog.sqlauthority.com)
39 Comments. Leave new
This is a nice handy way. But be aware that if any network drives are mapped on server then those will not be shown through this procedure.
Hi Pinal,
i’m in love all the articles you write.
I’ve one question regarding View:
its just a plain view:
Select ID, ‘1’ as group, Name From T_Base;
Output
———
ID Group Co_Name
1 1 ABC
2 1 PQR
1 1 ABC
3 1 MNO
1 1 ABC
Now, i would like to update / increment the group value by 1 if the ID value gets repeated. I wanted to do everything in that particualr VIEW only.
Expected Output:
———————–
ID Group Co_Name
1 1 ABC
2 1 PQR
1 2 ABC
3 1 MNO
1 3 ABC
Any help would be appreciated.
Thanks in Advance,
Raj
Which version of SQL Server are you using?
If you use version 2005, use row_number() function
Refer this post
SELECT ID,ROW_NUMBER() OVER (PARTITION BY ID,Co_Name ) AS Group, Co_Name
FROM [your_table]
Network drives shouldn’t be used to host SQL Server databases, so that shouldn’t be a problem.
@Pinal Dave
Thank You once again for this nice post. The post is really nice….
@Atif.
Is there any way we can check Hard Drive Free space in network drives if it’s there?
Thanks in advance.
and again, Pinal neverending story ;) with (not only) tips is going on ;)
Thx, fot that! This one can be handy for some scripting..
Hi Sir,
Nice Knowledge Sharing.
Raj,
Is this what you are looking for?
DECLARE @t TABLE (ID INT, Name CHAR(3))
INSERT INTO @t (ID, Name) SELECT 1, ‘ABC’
INSERT INTO @t (ID, Name) SELECT 2, ‘PQR’
INSERT INTO @t (ID, Name) SELECT 1, ‘ABC’
INSERT INTO @t (ID, Name) SELECT 3, ‘MNO’
INSERT INTO @t (ID, Name) SELECT 1, ‘ABC’
SELECT
ID,
ROW_NUMBER() OVER(
PARTITION BY ID ORDER BY ID) AS [Group],
Name
FROM @t
/*
ID Group Name
———– ——————– —-
1 1 ABC
1 2 ABC
1 3 ABC
2 1 PQR
3 1 MNO
*/
Mount Points don’t show up with this… standard practice used for VLDB’s… So, this shows that I have an E drive for example that is a couple hundred MB…but, I have 3 TB worth of mountpoints hanging off that E drive.
https://docs.microsoft.com/en-us/collaborate/connect-redirect
Thank you very much Jacob for your reply. It resolved my problem.
Now i have some different requirement. Could you please help me out with it as well /
This is the query rewult which im getting as of now
======================
Code Name Group
——————————–
AX001 ABC 1
AX002 XXX 1
AX001 MNO 1
AX001 ABC 1
AX003 YYY 1
AX001 PQR 1
AX004 ZZZ 1
AX003 YYY 1
AX003 ZZZ 1
AX002 ABC 1
I need the result in the below format.. When Code & Name are same then it shud start with 1, if same code & diff Name then the Gropu value should increment by 1.
Expected Output
======================
Code Name Group
——————————–
AX001 ABC 1
AX001 ABC 1
AX001 MNO 2
AX001 PQR 3
AX002 ABC 1
AX002 XXX 2
AX003 YYY 1
AX003 YYY 1
AX003 ZZZ 2
AX004 ZZZ 1
Can you please reply to me on my id. I sent you the Linkedin request as well.
Thanks,
Awaiting your feedback / reply ASAP (TIA)
Raj,
You can use the DENSE_RANK() function to achieve this. I am posting it here (even though you asked me to send by email) so that it can help other people as well.
DECLARE @t TABLE (Code CHAR(5), Name CHAR(3))
INSERT INTO @t (Code, Name) SELECT ‘AX001′,’ABC’
INSERT INTO @t (Code, Name) SELECT ‘AX001′,’ABC’
INSERT INTO @t (Code, Name) SELECT ‘AX001′,’MNO’
INSERT INTO @t (Code, Name) SELECT ‘AX001′,’PQR’
INSERT INTO @t (Code, Name) SELECT ‘AX002′,’ABC’
INSERT INTO @t (Code, Name) SELECT ‘AX002′,’XXX’
INSERT INTO @t (Code, Name) SELECT ‘AX003′,’YYY’
INSERT INTO @t (Code, Name) SELECT ‘AX003′,’YYY’
INSERT INTO @t (Code, Name) SELECT ‘AX003′,’ZZZ’
INSERT INTO @t (Code, Name) SELECT ‘AX004′,’ZZZ’
SELECT
Code, Name,
DENSE_RANK() OVER(PARTITION BY Code ORDER BY Name) AS[ Group]
FROM @t
/*
Code Name Group
—– —- ——————–
AX001 ABC 1
AX001 ABC 1
AX001 MNO 2
AX001 PQR 3
AX002 ABC 1
AX002 XXX 2
AX003 YYY 1
AX003 YYY 1
AX003 ZZZ 2
AX004 ZZZ 1
*/
Dear Pinal & Other members,
I want to find out full hard disk detail with space utilized (In MB) and percentage, space utilized in MB and % for a system.
it’s really urgent so please co-operate.
Thanks in Advance.
Regards,
Bipin Singh
Try
EXEC xp_cmdshell ‘Dir “Driver”‘
Dear Madhivanan,
It returns something like this,
Volume in drive C has no label.
Volume Serial Number is 1CB7-C6C4
NULL
Directory of C:\WINDOWS\system32
NULL
File Not Found
NULL
which is not my desired output. I discovered something like this which is satisfying my requirement.
Script:
Alter PROCEDURE Proc_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 = @@servername, 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
Exec Proc_diskspace
——————————————————————–
It results like this,
Servername C 38154 26468 69 2010-05-05 09:43:02.300
Thanks,
Bipin Singh
Hi Pinal,
Wat about mounted volume?
Thanks
Good to know the T-SQL way of doing it but this is where PowerShell comes into picture. It is quite easy!
Get-WmiObject -Class Win32_LogicalDisk | Select -Property DeviceID, @{Name=’FreeSpaceMB’;Expression={$_.FreeSpace/1MB} } | Format-Table -AutoSize
This gives an output similar to:
DeviceID FreeSpaceMB
——– ———–
C: 258795.73046875
D: 0
F: 3000.8125
H: 122916.5
Q: 0
Now, you can easily extend this to query a remote server by just adding the -ComputerName parameter. For example:
Get-WmiObject -ComputerName Remoteserver -Class Win32_LogicalDisk | Select -Property DeviceID, @{Name=’FreeSpaceMB’;Expression={$_.FreeSpace/1MB} } | Format-Table -AutoSize
Simple!
Hi Pinal and All commentors,
The values in sql output are different from the OS drives actual values.
eg:D drive is showing 19313 MB in sql result.But OS drives shows 18.8 GB free..
How can we rectify the difference?
@Raj
19313/1024 = 18.8GB
Thanks Raj..
how to get the free space in GB
Is there a way toQuery using XP_CmdShell to collect the used space and free space on a UNC path or mapped network drive?
As a previous poster commented, mount points do not display correctly using the fixed drive stored procedure. As a work-around for my current situation, I found that I can create a sql agent job to run a CmdExec type of job which just does a “dir” of the drive and stores it to a file.
I would really like to hear more about your approach as I have the same issue with Mount Points in my environment.
For people having problems finding mount points:
Hi All,
TRY THIS: but this will not give us the infomation about volumes.
IF OBJECT_ID(‘#AvailableDiskSpace’) IS NULL
BEGIN
CREATE TABLE dbo.#AvailableDiskSpace
(
PK INT IDENTITY CONSTRAINT PK_AvailableDiskSpace PRIMARY KEY,
DriveLetter CHAR(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
VolumeName VARCHAR(32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
PercentFree AS (CONVERT(DECIMAL(15, 4), AvailableMB) / CONVERT(DECIMAL(15, 4), TotalDiskSpaceMB) * 100),
AvailableMB BIGINT NOT NULL,
TotalDiskSpaceMB BIGINT NOT NULL,
DTStamp DATETIME NOT NULL
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX NCndx_DTStamp
ON dbo.#AvailableDiskSpace (DTStamp)
END
— Vars
DECLARE
@AgeOutDate DATETIME, — To prune the [AvailableDiskSpace] table
@BATCHDATE DATETIME, — Constant
@DriveSize BIGINT, — Drive size in bytes
@DriveLetter VARCHAR(2), — FSO drive letter
@ErrorMessage VARCHAR(500), — Error message
@FSO INT, — File System Object
@AvailableMB INT, — Available space MB
@NO BIT, — Constant
@oDrive INT, — FSO drive
@OLE_AUTOMATION_PROCEDURES INT, — Constant
@PK TINYINT, — Work-table primary key
@RawFree VARCHAR(20), — Available space bytes
@RetVal INT, — Return value
@SQL NVARCHAR(255), — Dynamic T-SQL
@TwoToTheTwentieth INT, — Convert bytes to MB
@ValueInUse SQL_VARIANT, — To test configuration settings
@VolumeName VARCHAR(32), — FSO volume name
@YES BIT — Constant
— Constants
SET @BATCHDATE = GETDATE()
SET @OLE_AUTOMATION_PROCEDURES = 16388 — SELECT configuration_id FROM master.sys.configurations
SET @NO = 0
SET @TwoToTheTwentieth = POWER(2, 20)
SET @YES = 1
— Create temp tables for disk space info
IF OBJECT_ID(‘tempdb.dbo.#Space’) IS NOT NULL DROP TABLE #Space
CREATE TABLE #Space
(
PK TINYINT IDENTITY CONSTRAINT Space_PK PRIMARY KEY,
DriveLetter VARCHAR(2) NOT NULL,
VolumeName VARCHAR(32) NULL,
DriveSize BIGINT CONSTRAINT DF_Space_DriveSize DEFAULT(0),
AvailableMB BIGINT NOT NULL,
Processed BIT CONSTRAINT DF_Space_Processed DEFAULT (0)
)
— We need OLE automation enabled to succeed.
BEGIN TRY
— Enable advanced options
EXEC sp_configure ‘show advanced option’, ‘1’;
RECONFIGURE;
— Ensure OLE Automation enabled
EXEC sp_configure ‘OLE Automation Procedures’, @YES;
RECONFIGURE
— Test…
SELECT @ValueInUse = Value_In_Use
FROM master.sys.configurations
WHERE configuration_id = @OLE_AUTOMATION_PROCEDURES
IF CONVERT(INT, @ValueInUse) @YES
BEGIN
SET @ErrorMessage = CHAR(13) + ‘Although we attempted to enable the ”OLE Automation Procedures” server option, this option is still disabled.’ + CHAR(13)
+ ‘This script depends upon this extended stored procedure”s availability. Please investigate.’
+ CHAR(13) + ‘NOTE: ”OLE Automation Procedures” execution requires ”CONTROL SERVER” permission (See SQL Server Books Online for more information).’
RAISERROR(@ErrorMessage, 16, 1)
END
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_LINE() AS ErrorLine
,ERROR_PROCEDURE() AS ErrorProcedure
GOTO Cleanup
END CATCH
— 1) Remove records older than user-provided number of days
BEGIN TRY
— By default, I’d like to keep 2 years of data
SET @AgeOutDate = DATEADD(dd, (730 * -1), GETDATE())
DELETE dbo.#AvailableDiskSpace
WHERE DTStamp < @AgeOutDate
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_LINE() AS ErrorLine
,ERROR_PROCEDURE() AS ErrorProcedure
GOTO Cleanup
END CATCH
— 2) Get free space on drives
BEGIN TRY
— Populate temp table; this system proc gives us space in MB
INSERT INTO #Space (DriveLetter, AvailableMB)
EXEC master.dbo.xp_fixeddrives
— 3) Loop through drives and get various attributes using OLE automation
WHILE EXISTS (SELECT TOP 1 1 FROM #Space WHERE Processed = @No)
BEGIN
SELECT TOP 1
@PK = PK,
@DriveLetter = DriveLetter,
@AvailableMB = AvailableMB,
@DriveSize = DriveSize
FROM #Space
WHERE Processed = @No
BEGIN TRY
EXEC master.dbo.sp_OACreate 'Scripting.FileSystemObject', @FSO OUT
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_LINE() AS ErrorLine
,ERROR_PROCEDURE() AS ErrorProcedure
GOTO Cleanup
END CATCH
BEGIN TRY
— Get disk info
SET @DriveLetter = @DriveLetter + ':\'
EXEC master.dbo.sp_OAMethod @FSO, 'GetDrive', @oDrive OUT, @DriveLetter
EXEC master.dbo.sp_OAMethod @oDrive, 'TotalSize', @DriveSize OUT
EXEC master.dbo.sp_OAMethod @oDrive, 'AvailableSpace', @RawFree OUT
EXEC master.dbo.sp_OAGetProperty @oDrive, 'VolumeName', @VolumeName OUT
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_LINE() AS ErrorLine
,ERROR_PROCEDURE() AS ErrorProcedure
GOTO Cleanup
END CATCH
BEGIN TRY
— Update table
UPDATE #Space
SET
VolumeName =
CASE LOWER(@DriveLetter)
WHEN 'c:' THEN 'System'
ELSE @VolumeName
END,
— Convert bytes to megabytes
DriveSize = CONVERT(BIGINT, @DriveSize) / @TwoToTheTwentieth,
— Convert bytes to megabytes
AvailableMB = CONVERT(BIGINT, @RawFree) / @TwoToTheTwentieth
WHERE PK = @PK;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_LINE() AS ErrorLine
,ERROR_PROCEDURE() AS ErrorProcedure
GOTO Cleanup
END CATCH
BEGIN TRY
— Destroy oDrive
EXEC master.dbo.sp_OADestroy @oDrive
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_LINE() AS ErrorLine
,ERROR_PROCEDURE() AS ErrorProcedure
GOTO Cleanup
END CATCH
UPDATE #Space
SET Processed = @YES
WHERE PK = @PK
END
BEGIN TRY
— Destroy FSO
EXEC master.dbo.sp_OADestroy @FSO
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_LINE() AS ErrorLine
,ERROR_PROCEDURE() AS ErrorProcedure
GOTO Cleanup
END CATCH
— 4) Load results in table
— Return disk-space info
INSERT dbo.#AvailableDiskSpace
(
DriveLetter,
VolumeName,
AvailableMB,
TotalDiskSpaceMB,
DTStamp
)
SELECT
DriveLetter DriveLetter,
CASE
WHEN LEN(RTRIM(LTRIM(VolumeName))) = 0 THEN 'Unknown'
ELSE VolumeName
END VolumeName,
AvailableMB AvailableMB,
DriveSize TotalDiskSpaceMB,
@BATCHDATE LoadDate
FROM #Space
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_LINE() AS ErrorLine
,ERROR_PROCEDURE() AS ErrorProcedure
GOTO Cleanup
END CATCH
Cleanup:
— Ensure xp_cmdshell's off
EXEC sp_configure 'OLE Automation Procedures', @NO;
RECONFIGURE
Select * From #AvailableDiskSpace
drop table #AvailableDiskSpace
drop table #Space
I am using XP_fixeddrive for space monitoring of all the drives on a db server. But I want to exclude one drive from monitoring..How can I do so using xp_fixedrive? Suggestion would be highly appreciated.
Export the result to a temp table and exclude rows like where col not like ‘your_Drive%’