December 17, 2012 at 3:05 pm
So every Sunday we run a custom reindex on a 2005 SQL Server with ~30 DBs on it. Takes the better part of half a day. The hard drive space is tight but before it starts there's plenty of free space WITHIN these DBs. When the reindex runs it starts chewing additional hard drive space. I guess this is to be expected.
My main question is why can't I see evidence of any autogrowth in the SQL logs?
EXEC xp_readerrorlog 0,1,'autogrow'
shows nothing in this timeframe. If indexing is causing additional drive space to be used wouldn't it have to have been reflected in an autogrowth somewhere? Most of our files grow at 10% and some, but not all indexes are on other filegroups besides primary.
Thanks,
Ken
December 17, 2012 at 3:42 pm
Autogrow is not logged to the error log unless it times out, takes excessively long or fails. Try the default trace.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 17, 2012 at 4:25 pm
Found it, thanks.
SELECT databaseid, filename, SUM(IntegerData*8) AS 'Growth in KB', Duration, StartTime
FROM fn_trace_gettable(<your trace file>, DEFAULT)
WHERE EventClass BETWEEN 92 AND 93
GROUP BY databaseid, filename, IntegerData, Duration, StartTime
ORDER BY StartTime
This is very helpful. At 1st I was looking for
WHERE TextData LIKE '%autogrow%'
That will not work.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply