Situatie
Mai jos este o procedura ce rulata pe un server de sql returneaza detalii despre toate bazele aflate pe acea instanta de sql, cum ar fi: numele bazelor, numele fisierelor ce alcatuiesc bazele, locatiile lor pe discuri, collation-ul, tipul de acces pe baza, tipul de recovery, statusul bazei, dimensiunea (in bytes, mega si giga) precum si procentul de utilizare a discului pe care se afla.
Solutie
alter procedure DatabaseDetails_prc
as
select
d.name,
f.name as FileName,
d.collation_name,
d.user_access_desc as UserAccess,
d.recovery_model_desc as RecoveryModel,
f.physical_name as physicalfile,
f.state_desc as onlinestatus,
f.size * 8.00 * 1024.00 as bytes,
cast((f.size * 8.00 * 1024.00) / 1048576.00 as numeric (18,2)) as megabytes,
cast((f.size * 8.00 * 1024.00) / 1073741824.00 as numeric(18,2)) as gigabytes,
cast(cast(v.total_bytes – v.available_bytes as float) / cast(v.total_bytes as float) * 100 as numeric(18,2)) used_disk_percent
from
sys.master_files f
inner join sys.databases d on d.database_id = f.database_id
cross apply sys.dm_os_volume_stats(f.database_id, f.file_id) v
order by
d.name
Leave A Comment?