1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52
|
CREATE VIEW [info].[get_files_space] AS
SELECT
TAB_B.Drive_B as Drive,
TAB_B.Drive_total_GB,
TAB_B.Drive_available_GB,
TAB_B.Drive_DiskUsage_percent,
CASE WHEN TAB_B.Drive_B = 'F' THEN 'TEMP_DB' ELSE TAB_C.group_name END AS group_name,
CASE WHEN TAB_B.Drive_B = 'F' THEN 'TEMP_DB' ELSE TAB_C.file_name END AS file_name,
TAB_C.file_size_GB,
TAB_C.file_space_used_GB,
TAB_C.file_free_space_GB,
TAB_C.[file_%_used],
TAB_C.file_path
FROM
(
SELECT ISNULL(groupname,'TLOG') group_name, name as file_name, file_size/1024 as file_size_GB, space_used/1024 as file_space_used_GB, free_space/1024 as file_free_space_GB,CAST(ROUND((space_used/(free_space+space_used))*100,2) as float) as 'file_%_used', filename as file_path, LEFT(filename,1) as Drive,SUBSTRING(filename,1,CHARINDEX('\',filename,(charindex('\',filename)+1))) as begin_file_path
FROM
(
SELECT
DB_NAME() as database_name
,sysfilegroups.groupid
,sysfilegroups.groupname
,fileid
,convert(decimal(12,2),round(sysfiles.size/128.000,2)) as file_size
,convert(decimal(12,2),round(fileproperty(sysfiles.name,'SpaceUsed')/128.000,2)) as space_used
,convert(decimal(12,2),round((sysfiles.size-fileproperty(sysfiles.name,'SpaceUsed'))/128.000,2)) as free_space
,sysfiles.name
,sysfiles.filename
FROM sys.sysfiles WITH(NOLOCK)
LEFT OUTER JOIN sys.sysfilegroups WITH(NOLOCK) ON sysfiles.groupid = sysfilegroups.groupid
)TB
WHERE database_name ='ma_db'
)TAB_C FULL JOIN
(
SELECT *,ROUND(100-((CAST(Drive_available_GB as float) /CAST(Drive_total_GB as float))*100),2) as 'Drive_DiskUsage_percent'
FROM
(
SELECT DISTINCT
SUBSTRING(volume_mount_point, 1, 1) AS Drive_B
,SUBSTRING(physical_name,1,CHARINDEX('\',physical_name,(charindex('\',physical_name)+1))) as begin_file_path
,total_bytes/1024/1024/1024 AS Drive_total_GB
,available_bytes/1024/1024/1024 AS Drive_available_GB
FROM
sys.master_files AS f WITH(NOLOCK)
CROSS APPLY
sys.dm_os_volume_stats(f.database_id, f.file_id)
WHERE (type_desc = 'ROWS' OR SUBSTRING(physical_name,1,CHARINDEX('\',physical_name,(charindex('\',physical_name)+1))) = 'E:\SQL_LOGS\')
AND SUBSTRING(physical_name,1,CHARINDEX('\',physical_name,(charindex('\',physical_name)+1))) != 'D:\SQL_ROOT\'
)TAB
)TAB_B
ON TAB_C.Drive =TAB_B.Drive_B AND TAB_C.begin_file_path = TAB_B.begin_file_path |
Partager