How to tell when Automatic Growth occurs?
-
Anyone know a way to tell when the database size is automatically expanding? We're having some performance issues where alot of people's pc's are getting locked up for a few seconds at the same time. I'm trying to figure out if database expansion is happening at that time. I don't see anything in any log files. Anyone know a way to tell when database expansion occurs?
-
Not really, but the best pratice for something like that is to plan the expansions ahead of time and doing them off hours so that the users don't suffer from it. How big of a db are we talking about?
-
there was a script posted http://www.databasejournal.com/features/mssql/article.php/3517766
that should give you what you want
-
It sounds very possible that the autogrowth is causing the problems. You can use both Profiler and/or Windows System Monitor (perfmon) to see when the files grow. However, like Remi says, you should try to avoid using autogrowth and grow manually as necessary. Or even better, do not allow growth at all and instead add new files when necessary. That way you have the biggest chance of having contigous files.
-
>>Anyone know a way to tell when the database size is automatically expanding?
Start Profiler, add events Database->Data File Auto Grow, Database-> Log File Auto Grow
Start Trace.
That's all.
Wanna sheduling - script trace and make regular job, write log files.
Good luck!
-
Thanks guys!
-
The above tips are great to see if the DB is actually in the process of Autogrowing. What I find difficult is how to tell if the database has 'Autogrown' in the past. There is nothing in the SQL Log or Windows Event Viewer.
The only solution that I have found for this is to note the time/date stamp on the MDF files in Windows Explorer.
I'm sure someone else has a better solution.
-
As Yukas mentioned above, SQL Profiler works pretty well, but you have to keep the trace running all day I guess to catch it. However it found exactly what I was looking for
-
You can create an Alert which is fired by the database Size counter and use current size as the limit! and for TLog growth there is also a log grow counter!
Cheers!
* Noel
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply