May 6, 2013 at 8:17 am
I am a Jr. Level DBA, responsible for monitoring our production server. On Friday I noted that the drive our data files reside on was nearly at capacity; my supervisor was on vacation, but I notified him thru email.
Using a monitoring tool that takes regular polls, I noted that the drive space suddenly decreased on Saturday night (right before a backup job, as it turns out. The jobs were successful).
My questions are: what methods are possible to resolve this situation? I've only thought of moving some datafiles to another drive, and shrinking some datafiles. Also, are these types of operations typically logged somewhere? I don't see anything using sys.xp_errorlog nor in the job log.
Any help is much appreciated.
~ Jeff
May 6, 2013 at 11:37 am
Shrinking your data files is considered a bad idea unless you;re in an emergency situation. It fragments your indexes terribly and inevitably the files will likely grow to their original size anyway, creating a very bad cycle of poor performance and downtime while the shrinks are done and the autogrows reoccur.
All grow and shrink operations are logged by the Default Trace so you can review that and see when those occurred.
It sounds like you need to ask for more disk space or move some data files off the drive that is filling up.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 6, 2013 at 11:41 am
Thanks for replying; my supervisor and I were mostly concerned with the "who and why" with the sudden data growth, then later settling back to normal levels...as we didn't get any notification that such a change was likely, such as a large bulk load, we were hoping to track what happened and who caused it.
The trace is a good idea, I'll start looking there.
~ Jeff
May 6, 2013 at 11:51 am
jhager (5/6/2013)
Thanks for replying; my supervisor and I were mostly concerned with the "who and why" with the sudden data growth, then later settling back to normal levels...as we didn't get any notification that such a change was likely, such as a large bulk load, we were hoping to track what happened and who caused it.The trace is a good idea, I'll start looking there.
~ Jeff
The Default Trace will tell you the Login Name of the person that initiated the operation that caused the database to autogrow or shrink, the Application Name (from the Connection String), the the date and time, etc.
I recommend loading the Default Trace file into a table. You're looking for rows WHERE EventClass IN (92, 93, 94, 95).
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply