????(4) ????ж??????????fileproperty()??filegroup_name()??????????????????????Ч????????￡?
if object_id('tempdb..#tmp_filesize') is not null
drop table #tmp_filesize
GO
create table #tmp_filesize
(
server_name          varchar(256)??
database_name        varchar(256)??
file_group           varchar(256)??
logical_name         varchar(256)??
physical_name        varchar(1024)??
type_desc            varchar(128)??
used_size_mb         float??
allocated_size_mb    float??
max_size_mb          float??
disk_free_mb         float??
free_space_percent   float??
growth               int??
is_percent_growth    int
)
GO
exec sp_msforeachdb 'use [?]
insert into #tmp_filesize
select @@SERVERNAME as server_name
??DB_NAME() as database_name
??case when data_space_id = 0 then ''LOG''
else FILEGROUP_NAME(data_space_id)
end as file_group
??name as logical_name
??physical_name
??type_desc
??FILEPROPERTY(name??''SpaceUsed'')/128.0 as used_size_mb
??size/128.0 as allocated_size_mb
??case when max_size = -1 then max_size
else max_size/128.0
end as max_size_mb
??vs.available_bytes/1024.0/1024 as disk_free_mb
??case when growth = 0 then  (size - FILEPROPERTY(name??''SpaceUsed''))*1.0/size
when growth > 0 and max_size = -1 then ((size/128.0 + vs.available_bytes/1024.0/1024) - FILEPROPERTY(name??''SpaceUsed'')/128.0)/(size/128.0 + vs.available_bytes/1024.0/1024)
when growth > 0 and max_size <> -1 and (max_size/128.0 - vs.available_bytes/1024.0/1024) >= 0 then ((size/128.0 + vs.available_bytes/1024.0/1024) - FILEPROPERTY(name??''SpaceUsed'')/128.0)/(size/128.0 + vs.available_bytes/1024.0/1024)
when growth > 0 and max_size <> -1 and (max_size/128.0 - vs.available_bytes/1024.0/1024) <  0 then (max_size - FILEPROPERTY(name??''SpaceUsed''))*1.0/max_size
else null
end as free_space_percent
??growth
??CAST(is_percent_growth as int) as is_percent_growth
from sys.database_files df
cross apply sys.dm_os_volume_stats(DB_ID()??df.file_id) vs
where state_desc = ''ONLINE'''
select * from #tmp_filesize
????2. ??????????澯
????2.1 ?澯????
??????????????澯????????澯????????????澯??????????????????е??????/?????????????????????????п??????????????LOG????顣
????(1) Oracle???????????????磬?????????????????????????????????????????????????
????(2) SQL Server ????????????????磬?????????????????????????磬?????????????????????????е??????????????
???????????????/LOG?μ???????????????￡?????????????????(??20%???)?????????澯??????????￡?
????--#tmp_filesize ?????????????????
????select server_name??
????database_name??
????file_group??
????MAX(free_space_percent) as max_free_space_percent
????from #tmp_filesize
????group by server_name??database_name??file_group
????having MAX(free_space_percent) <= 0.2 --20%
????????澯???????????
??????????????????????????????????????@@servername?????????SQL Server???????
????????????????? ”file group name” ??????????20%??
????2.2 ?澯????δ????
????(1) ?澯?е????????????????????????п????
????exec xp_fixeddrives
???????????????????????????????????????????????μ?????????????????????????????????
??????????д??????????????????????????????????????????????????(????/???)???????????????(?????)??
????(2) ????????п?????????????????????
?????????????????????????????????size????500G?????????????????????
????ALTER DATABASE test
????ADD FILE
????(
????NAME = test_02??
????FILENAME = 'D:Program Files (x86)Microsoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATA est_02.ndf'??
????SIZE = 500 GB??
????FILEGROWTH = 0
????)
????TO FILEGROUP [PRIMARY];
????GO
????(3) ????????п??????????????????????????????????
?????????????????????????????????????????????????????????????500G????????????????????????????????????????
????С??
?????????м?????????????????????????洢?????????????????????????????飬???澯?????/??????п???????2??????? ??
????(1) ??????????????
????(2) ????????????????????/??????п????????澯??