December 17, 2013 at 11:05 am
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);
December 17, 2013 at 10:01 pm
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[/url] has a wonderful set of diagnostic queries. The link to that is here.
.
December 18, 2013 at 1:19 am
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
December 18, 2013 at 6:37 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply