Che dimensioni hanno i file di log dei miei DB ?

Risorse
Davide Mauri

Questo  piccolo script (per Sql2005) riporta, per ciascun database, le dimensione dei dati, del file di log, la percentuale di utilizzo del log ed il modello di recovery attivo.

select
  rtrim (pc1.instance_name) as database_name,
  pc3.cntr_value/1024.0 as database_size_mb,
  pc1.cntr_value/1024.0 as log_size_mb,
  cast (pc2.cntr_value*100.0/pc1.cntr_value as dec (5,2)) as log_space_used_perc,
  db.recovery_model_desc
from
  sys.databases as db
inner join
  sys.dm_os_performance_counters as pc1 on pc1.instance_name = db.name
inner join
  sys.dm_os_performance_counters as pc2 on pc1.instance_name = pc2.instance_name
inner join
  sys.dm_os_performance_counters as pc3 on pc1.instance_name = pc3.instance_name
where
  pc1.counter_name = ‘Log file(s) Size (KB)’
and
  pc2.counter_name = ‘Log file(s) Used Size (KB)’
and
  pc3.counter_name = ‘Data File(s) Size (KB)’
and
  pc1.cntr_value > 0
order by
  pc1.instance_name