videos
video collection of sqlschool.gr
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