SQL Server Performance Forum – Threads Archive

sp_fixeddrives

All , The above sp_ will return drive info on a server .Is there any way to differentiate between local drives and network shared drives . Thanks
rajiv

xp_fixeddrives shouldn’t return any network drives as I believe the SQL Service cannot see mapped network drives – these are assigned to the user when they log onto the server. For example if you map a network drive as drive X: and then try to perform a backup to it remotely through query analyser it should fail with an invalid path message.

You mean xp_fixeddrives? [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] Theoretically you should be able to specify the drive type by executing this with <pre id="code"> xp_fixeddrives 0, [1, 2] </pre id="code"> where 0 means hard disks, 1 means network drives, and 2 means floppy or zip drives. However, for me all return the same. [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Wow Wow !!! Thanks frank .

Is it working for you? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Rajiv
Most of your questions asking about undocumented stored procedures and it is not advisable to rely totally on them as MS can change or drop during next release or hotfix release. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Yes i tried all the options, works fine .Thanks Frank . rajiv

USE [master]
GO /****** Object: StoredProcedure [dbo].[sp_fixeddrives] Script Date: 11/19/2014 11:55:18 AM ******/
SET ANSI_NULLS ON
GO SET QUOTED_IDENTIFIER ON
GO /*
EXEC sp_fixeddrives – it will Print just fixeddrive(mount and SAN) info with free space
EXEC sp_fixeddrives 1 — it willPrint more detail per DB
*/ CREATE PROCEDURE [dbo].[sp_fixeddrives]
@format tinyint = 0
AS
BEGIN SET ARITHIGNORE ON
SET NOCOUNT ON DECLARE @SQL NVARCHAR(1000) CREATE TABLE #DrvLetter (
Drive VARCHAR(500),
)
CREATE TABLE #DrvInfo (
Drive VARCHAR(500) null,
[MB free] DECIMAL(20,2),
[MB TotalSize] DECIMAL(20,2),
[Volume Name] VARCHAR(64)
) INSERT INTO #DrvLetter
EXEC xp_cmdshell ‘wmic volume where drivetype="3" get caption, freespace, capacity, label’ DELETE
FROM #DrvLetter
WHERE drive IS NULL OR len(drive) < 4 OR Drive LIKE ‘%Capacity%’
OR Drive LIKE ‘%\\%\Volume%’ DECLARE @STRLine VARCHAR(8000)
DECLARE @Drive varchar(500)
DECLARE @TotalSize REAL
DECLARE @Freesize REAL
DECLARE @VolumeName VARCHAR(64) WHILE EXISTS(SELECT 1 FROM #DrvLetter)
BEGIN
SET ROWCOUNT 1
SELECT @STRLine = drive FROM #DrvLetter — Get TotalSize
SET @TotalSize= CAST(LEFT(@STRLine,CHARINDEX(‘ ‘,@STRLine)) AS REAL)/1024/1024
–SELECT @TotalSize — Remove Total Size
SET @STRLine = REPLACE(@STRLine, LEFT(@STRLine,CHARINDEX(‘ ‘,@STRLine)),”)
— Get Drive SET @Drive = LEFT(LTRIM(@STRLine),CHARINDEX(‘ ‘,LTRIM(@STRLine)))
–SELECT @Drive SET @STRLine = RTRIM(LTRIM(REPLACE(LTRIM(@STRLine), LEFT(LTRIM(@STRLine),CHARINDEX(‘ ‘,LTRIM(@STRLine))),”))) SET @Freesize = LEFT(LTRIM(@STRLine),CHARINDEX(‘ ‘,LTRIM(@STRLine)))
–SELECT @Freesize/1024/1024 SET @STRLine = RTRIM(LTRIM(REPLACE(LTRIM(@STRLine), LEFT(LTRIM(@STRLine),CHARINDEX(‘ ‘,LTRIM(@STRLine))),”)))
SET @VolumeName = @STRLine
— INSERT INTO #DrvInfo
SELECT @Drive, @Freesize/1024/1024 , @TotalSize, @VolumeName DELETE FROM #DrvLetter
END SET ROWCOUNT 0 — POPULATE TEMP TABLE WITH LOGICAL DISKS
— This is FIX/Workaround for Windows 2003 bug that WMIC doesn’t return volume name that is over X number of charactors.
SET @SQL =’wmic /FailFast:ON logicaldisk where (drivetype ="3" and volumename!="RECOVERY" AND volumename!="System Reserved") get deviceid,volumename /Format:csv’
if object_id(‘tempdb..#output1’) is not null drop table #output1
CREATE TABLE #output1 (Col1 VARCHAR(2048))
INSERT INTO #output1
EXEC master..xp_cmdshell @SQL
DELETE #output1 where ltrim(col1) is null or len(col1) = 1 or Col1 like ‘Node,DeviceID,VolumeName%’ if object_id(‘tempdb..#logicaldisk’) is not null drop table #logicaldisk
CREATE TABLE #logicaldisk (DeviceID varchar(128),VolumeName varchar(256)) DECLARE @NodeName varchar(128)
SET @NodeName = (SELECT TOP 1 LEFT(Col1, CHARINDEX(‘,’,Col1)) FROM #output1) — Clean up server name
UPDATE #output1 SET Col1 = REPLACE(Col1, @NodeName, ”) INSERT INTO #logicaldisk
SELECT LEFT(Col1, CHARINDEX(‘,’,Col1)-2), SUBSTRING(COL1, CHARINDEX(‘,’,Col1)+1, LEN(col1))
FROM #output1 UPDATE dr
SET dr.[Volume Name] = ld.VolumeName
FROM #DrvInfo dr RIGHT OUTER JOIN #logicaldisk ld ON left(dr.Drive,1) = ld.DeviceID
WHERE LEN([Volume Name]) = 1 IF @format = 0
BEGIN
SELECT CASE
WHEN LEN(drive) = 3 THEN LEFT(drive,1)
ELSE drive
END AS drive,
[MB free],[MB TotalSize], [Volume Name] FROM #DrvInfo
ORDER BY 1
END ELSE IF @format = 1
BEGIN CREATE TABLE #DBInfo2
( ServerName VARCHAR(100),
DatabaseName VARCHAR(100),
FileSizeMB INT,
LogicalFileName sysname,
PhysicalFileName NVARCHAR(520),
Status sysname,
Updateability sysname,
RecoveryMode sysname,
FreeSpaceMB INT,
FreeSpacePct VARCHAR(7),
FreeSpacePages INT,
PollDate datetime) DECLARE @command VARCHAR(5000) SELECT @command = ‘Use [‘ + ‘?’ + ‘] SELECT
@@servername as ServerName,
‘ + ”” + ‘?’ + ”” + ‘ AS DatabaseName,
CAST(sysfiles.size/128.0 AS int) AS FileSize,
sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName,
CONVERT(sysname,DatabasePropertyEx(”?”,”Status”)) AS Status,
CONVERT(sysname,DatabasePropertyEx(”?”,”Updateability”)) AS Updateability,
CONVERT(sysname,DatabasePropertyEx(”?”,”Recovery”)) AS RecoveryMode,
CAST(sysfiles.size/128.0 – CAST(FILEPROPERTY(sysfiles.name, ‘ + ”” +
‘SpaceUsed’ + ”” + ‘ ) AS int)/128.0 AS int) AS FreeSpaceMB,
CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name,
‘ + ”” + ‘SpaceUsed’ + ”” + ‘ ) AS int)/128.0)/(sysfiles.size/128.0))
AS decimal(4,2))) AS varchar(8)) + ‘ + ”” + ”” + ‘ AS FreeSpacePct
FROM dbo.sysfiles’
INSERT INTO #DBInfo2
(ServerName,
DatabaseName,
FileSizeMB,
LogicalFileName,
PhysicalFileName,
Status,
Updateability,
RecoveryMode,
FreeSpaceMB,
FreeSpacePct)
EXEC sp_MSForEachDB @command SELECT
db.DatabaseName as DBName,
db.LogicalFileName as DBLogicalFileName,
db.PhysicalFileName as DBPhysicalFileName,
db.RecoveryMode as DBRecoveryMode,
db.FileSizeMB AS DBFileSizeMB,
db.FreeSpaceMB as DBFreeSpaceMB,
db.FreeSpacePct as DBFreeSpacePct,
CASE
WHEN LEN(dr.drive) = 3 THEN LEFT(dr.drive,1)
ELSE dr.drive
END AS Drive,
dr.[MB free] as DriveFreeSpaceMB,
dr.[MB TotalSize] as DriveTotalSizeMB,
CAST((dr.[MB free]/dr.[MB TotalSize]) * 100 AS NUMERIC(5,2)) as DriveFreeSpacePct,
dr.[Volume Name]
FROM #DBInfo2 db
JOIN #DrvInfo dr ON LEFT(db.PhysicalFileName,LEN(dr.drive)) = LEFT(dr.drive,LEN(dr.drive))
WHERE db.DatabaseName not in (
SELECT DatabaseName
FROM #DBInfo2 DB
JOIN (SELECT drive FROM #DrvInfo WHERE LEN(drive) > 3) DR on LEFT(db.PhysicalFileName, LEN(drive)) = DR.drive)
UNION ALL
SELECT
db.DatabaseName,
db.LogicalFileName,
db.PhysicalFileName,
db.RecoveryMode,
db.FileSizeMB,
db.FreeSpaceMB as DBFreeSpaceMB,
db.FreeSpacePct as DBFreeSpacePct,
CASE
WHEN LEN(dr.drive) = 3 THEN LEFT(dr.drive,1)
ELSE dr.drive
END AS drive,
dr.[MB free] AS DriveFreeSpaceMB,dr.[MB TotalSize] as DriveTotalSizeMB,
CAST((dr.[MB free]/dr.[MB TotalSize]) * 100 AS NUMERIC(5,2)) as DriveFreeSpacePct,
dr.[Volume Name] FROM #DBInfo2 db
JOIN #DrvInfo dr ON LEFT(db.PhysicalFileName,LEN(dr.drive)) = LEFT(dr.drive,LEN(dr.drive))
WHERE LEN(dr.drive) > 3
ORDER BY
db.DatabaseName DROP TABLE #DBInfo2
END DROP TABLE #logicaldisk
DROP TABLE #DrvLetter
DROP TABLE #DrvInfo END GO

]]>