Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Server Mount Point Space Query Expand / Collapse
Author
Message
Posted Tuesday, December 17, 2013 11:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:28 AM
Points: 2, Visits: 23
Hey Everyone, new to the forums. Ive been having trouble with the below query for getting mount point spaces using volume_mount_point etc. I know this is only supported in 2008 R2 SP1+ and was wondering if there is a query method to do something similar to the query below in 2005 SP4 and 2008 SP3 without having to do powershell or create any tables etc. Any help is appreciated even if its to confirm that It cant be done without powershell etc. Thanks in Advance! - Matt

SELECT DB_NAME(f.database_id) DatabaseName,
f.FILE_ID, size DBSize, file_system_type,
volume_mount_point,
CAST(total_bytes / 1073741824.0E AS DECIMAL(10, 3)) AS total_size_gb,
CAST(available_bytes / 1073741824.0E AS DECIMAL(10, 3)) AS available_size_gb,
((CAST((CAST(available_bytes / 1073741824.0E AS DECIMAL(10, 3))/CAST(total_bytes / 1073741824.0E AS DECIMAL(10, 3))) AS DECIMAL(10, 3)))*100) AS '% Free'
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.FILE_ID);

Post #1523825
Posted Tuesday, December 17, 2013 10:01 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 3:54 PM
Points: 604, Visits: 501
Something like the code below? This is from Glenn Berry's site.

SELECT DB_NAME([database_id])AS [Database Name], 
[file_id], name, physical_name, type_desc, state_desc,
CONVERT( bigint, size/128.0) AS [Total Size in MB]
FROM sys.master_files WITH (NOLOCK)
WHERE [database_id] > 4
AND [database_id] <> 32767
OR [database_id] = 2
ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);

Glenn Berry has a wonderful set of diagnostic queries. The link to that is here.


.
Post #1523938
Posted Wednesday, December 18, 2013 1:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:15 AM
Points: 2,232, Visits: 2,672
Getting mountpoint info is a bit harder in older versions of SQL Server. I used to get the info by using XP_CMDSHELL and WMIC. I changed the output of WMIC from CSV to fixed length because Windows 2k8R2 didn't support the CSV output anymore. The result is inserted in a temporary table so it's easier to remove unwanted rows and format the final output.
CREATE TABLE #dir (result varchar(1000))
--request drive info (result in fixed length)
SET @cmd = 'c:\windows\system32\wbem\wmic volume list brief'
INSERT INTO #dir (result)
EXEC master..xp_cmdshell @cmd
SELECT * FROM #dir



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1523985
Posted Wednesday, December 18, 2013 6:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:28 AM
Points: 2, Visits: 23
Hey Guys, thanks for the help, Bill that looks like kind of what I would need but my main goal for this query would be to get the total allocated space for a mount point and the free space left in that mount point so it doesnt look like I can get that info from just sys.master_files or sys.database_files. Is there perhaps another system table I could query for mount point info in 2005 or 2008? Ill take a look at Glenn Berry's site to see if anything exists but its looking more and more like im going to have to create a temp table and/or powershell script for it. thanks!

-Matt
Post #1524099
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse