July 17, 2013 at 6:09 am
I use below query to get auto-growth event occurred to databases in a sql server.
DECLARE @trcfilename VARCHAR(1000);
SELECT @trcfilename = path FROM sys.traces WHERE is_default = 1
SELECT COUNT(*)as no_of_autogrowths,
sum(duration/(1000*60)) as time_in_min,
Filename
FROM ::fn_trace_gettable(@trcfilename, default)
WHERE (EventClass = 92 OR EventClass = 93)
GROUP BY Filename
It outputs number of auto-growths,time taken for auto-growth and logical name of the file. But i want physical name of the file(mdf and ldf file name) instead of logical name.I don't know whether from sys.traces i will get physical name or please help me with an alternate way to do it.
July 17, 2013 at 6:16 am
I use this script to list all auto-growth events. It will summarize the number of times it happened and includes the oldest time and most recent time. The second select lists all filenames of all databases where auto-growth has happened. When a file is auto-grown the most recent time is displayed. (comments are in Dutch)
-- declareer variabele
declare @value sql_variant
-- bepaal huidige tracefile van default trace (alleen SQL 2005 of hoger)
SELECT
@value = value
FROM
fn_trace_getinfo(default)
WHERE
traceid = 1
and property = 2;
-- lees de tracefile uit voor event 92 (Data File Auto Grow) en 93 (Log File Auto Grow) en groepeer resultaat per database file
SELECT
DatabaseName
, FileName
, min(EndTime) as FirstAutogrowth
, max(EndTime) as LastAutogrowth
, count(EndTime) as Autogrowth_Actions
FROM
fn_trace_gettable(cast(@value as NVARCHAR(200)),1)
WHERE
EventClass IN (92, 93)
GROUP BY
DatabaseName
, FileName
ORDER BY
max(EndTime) desc
-- show all files for each database with autogrowth action
select
db.name as DatabaseName
, altfiles.filename
--, altfiles.growth
, 8*altfiles.growth/1024 as growth_MB
, max(trace.endtime) as LastAutogrowth
from
master.sys.databases db
right outer join master.sys.sysaltfiles altfiles
on db.database_id = altfiles.dbid
left outer join fn_trace_gettable(cast(@value as NVARCHAR(200)),1) trace
on db.name = trace.DatabaseName
and altfiles.name = trace.FileName
and EventClass IN (92, 93)
group by
db.name
, altfiles.filename
, altfiles.growth
, trace.FileName
having
db.name IN (select DatabaseName
from fn_trace_gettable(cast(@value as NVARCHAR(200)),1)
where EventClass IN (92, 93)
)
order by
db.name
, max(trace.endtime) desc
, altfiles.filename
July 17, 2013 at 6:30 am
Thank you hanshi..
By using your query i was able to rewrite my query.
DECLARE @trcfilename VARCHAR(1000);
SELECT @trcfilename = path FROM sys.traces WHERE is_default = 1
SELECT COUNT(*)as no_of_autogrowths,
sum(duration/(1000*60)) as time_in_min,FileName,DatabaseName
FROM ::fn_trace_gettable(@trcfilename, default)
WHERE (EventClass = 92 OR EventClass = 93)
GROUP BY Filename,DatabaseName
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply