I am not looking for something going forward like profiler, etc. I want to know if there is a way to historically look back and determine when file growth occurred. I have an error I am trying to resolve and think it might be associated with the tempdb file growth. Is there a way to find out the last time(s) the tempdb grew?
2008年9月17日 14:55版主To my best knowledge, no you can't tell historically, the last time the file was grown. I ran through the DMV's and couldn't find one that would have that informaton. You should be monitoring and growing your datafiles manually, leaving Autogrow on as a safety net, and not as a primary growth mechanism.
You can review the history of autogrow and autoshrink event by using the standard reports that are available with via Management Studio as of Service Pack 2.
Open Management studio, expand databases folder and then right click the database you wish to review.
Then choose Reports>Standard Reports > Disk Usage.
You can then expand the appropriate report option by clicking the plus sign.
Let us know how you get on.
Yes, you can query the default trace to get this information. The default trace is enabled by default on SQL 2005 and greater. This allows you to get at a whole lot of other information as well including object creation, deletion, and update, auditing etc..Code Snippet
--check if default trace is enabled
select * from sys.configurations where configuration_id = 1568
--get the current trace rollover file
--use this path with the log.trc file in the path below.
--this will cause a file rollover to get all the data
select * from ::fn_trace_getinfo(0)
--list of events object deleted, object altered
--list of categories e.g. database, sp etc..
SELECT ntusername,loginname, objectname, e.category_id, textdata, starttime,spid,hostname, eventclass,databasename, e.name
FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc',0)
inner join sys.trace_events e
on eventclass = trace_event_id
INNER JOIN sys.trace_categories AS cat
ON e.category_id = cat.category_id
where databasename = 'MyDB' and
cat.category_id = 2 and --database category
e.trace_event_id in (92,93) --db file growth
- 已建议为答案 Jose Felipe Rocha 2013年3月8日 19:10
Default trace can be used for getting information on Log file growths. But the default trace only can go back to certain time.
When you mean historic, if its within Default trace covering time then you can view this information.
The default trace gets full depending on activity on server.
Quick way to see this information is using
Reports>Standard Reports > Disk Usage which uses Default trace.
Hope this helps :)
Vamshi SQL DBA(MCITP) My Blogs: http://sqlserver-dba.co.uk http://sql-developers.co.uk/