Summary

Σε αυτό το επεισόδιο SQL Server in Greek θα δούμε πως μπορούμε να κατανοήσουμε το τρόπο με τον οποίο μεγαλώνουν τα αρχεία της κάθε database κάνοντας χρήση του default trace.

Video

Code

with check_growth as
(
       select DatabaseID,filename,sum(IntegerData*8) as growth,Duration,StartTime,EndTime 
       from fn_trace_gettable((select left(path, CHARINDEX('_',path)-1)+'.trc' from sys.traces where is_default=1),default)
       where EventClass in (92,93,94,95)
       group by DatabaseID,filename,Duration,StartTime,EndTime 
) 
, dsFiles as
(
    select DB_NAME(g.databaseid) as database_name,
                  f.name as logical_name,
                  f.type_desc,
                  f.size*8 as current_size_mb,
                  g.growth / 1014  as growth_mb,
                  f.is_percent_growth,
                  g.Duration / 1000 as duration_ms,
                  g.StartTime,
                  g.EndTime
    from sys.master_files as f
    left join check_growth as g on g.DatabaseID=f.database_id and g.FileName=f.name
    where g.growth > 0
)
select *,
current_size_mb - sum(growth_mb) over ( partition by database_name,logical_name
                      order by starttime desc
                      rows between unbounded preceding and current row
                    ) + growth_mb as size
                    from dsfiles


//antonch