August 31, 2015 at 10:23 pm
Comments posted to this topic are about the item Mount Points
September 1, 2015 at 12:10 am
This was removed by the editor as SPAM
September 1, 2015 at 2:26 am
To get it for each db and files:
declare @sql nvarchar(max) = 'select 0 as dbid,0 as fileid WHERE 0=1'
SELECT @sql = @sql + '
UNION ALL SELECT '''+RIGHT(database_id,10)+''',fileid FROM '+name+'.sys.sysfiles'
FROM sys.databases
Select @sql = 'select DISTINCT volume_mount_point,volume_id,logical_volume_name,file_system_type,total_bytes,available_bytes,supports_compression,supports_alternate_streams,supports_sparse_files,is_read_only,is_compressed from (' + @sql + ') AS A cross apply sys.dm_os_volume_stats(dbid,fileid)'
execute( @sql )
September 1, 2015 at 7:26 am
I guess I don't know what a mount point is well enough, but can't you get that information by looking at the file name and path in sys.master_files?
Be still, and know that I am God - Psalm 46:10
September 1, 2015 at 7:29 am
Great question. I love the system catalog and DMVs.
September 1, 2015 at 7:57 am
david.gugg (9/1/2015)
I guess I don't know what a mount point is well enough, but can't you get that information by looking at the file name and path in sys.master_files?
That's a way to explore resources where to store database.
Same as at command prompt: "dir c:\ & dir d:\ & dir e:\"
Disk name and free space
September 2, 2015 at 2:11 am
Thanks for the question.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 3, 2015 at 4:06 pm
I write PS query to get the details.
Thanks.
September 6, 2015 at 6:18 pm
Nice question about a topic that I am only beginning to study.
I knew the DMV sys.dm_os_volume_stats as I have used it , but only to know if the volume is supporting sparse files or is compressed. As I was curious , I have discovered the mount points and I kept this information in my own memory. It was last year.
September 6, 2015 at 8:48 pm
SQL-DBA-01 (9/3/2015)
I write PS query to get the details.
Cool. Please share it.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 6, 2015 at 10:13 pm
Carlo Romagnano (9/1/2015)
To get it for each db and files:
declare @sql nvarchar(max) = 'select 0 as dbid,0 as fileid WHERE 0=1'
SELECT @sql = @sql + '
UNION ALL SELECT '''+RIGHT(database_id,10)+''',fileid FROM '+name+'.sys.sysfiles'
FROM sys.databases
Select @sql = 'select DISTINCT volume_mount_point,volume_id,logical_volume_name,file_system_type,total_bytes,available_bytes,supports_compression,supports_alternate_streams,supports_sparse_files,is_read_only,is_compressed from (' + @sql + ') AS A cross apply sys.dm_os_volume_stats(dbid,fileid)'
execute( @sql )
Much easier to query the dmv's, no need for dynamic sql
😎
SELECT
SMF.database_id
,SMF.name
,SMF.type_desc
,SMF.state_desc
,SMF.size
,OVS.available_bytes / POWER(2,30) AS GB_FREE
,OVS.total_bytes / POWER(2,30) AS BG_TOTAL
,OVS.volume_mount_point
,OVS.file_system_type
,OVS.logical_volume_name
,OVS.volume_id
FROM sys.master_files SMF
CROSS APPLY sys.dm_os_volume_stats(SMF.database_id,SMF.file_id) AS OVS;
September 8, 2015 at 2:33 pm
Thanks for the question.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply