March 14, 2012 at 8:59 am
How can I find out what?
It's in full recovery mode, autogrew to max size (just under full disk), successful log backups every 10 minutes, not used for replication / mirroring etc. so must be a long running transaction.
My question is, how can I find out what was running?
Thank you.
March 14, 2012 at 9:54 am
Unless you have a trace running capturing all SQL, you cannot. But, you may be able to tell who was running the process that caused the autogrows. If teh Default Trace is enabled (default config) and the relevant ones are still around (they rotate out of existence over time) you can see all autogrow events and who initiated them. If the process that caused the events is running under a non-generic security account you may be able to determine the process that caused the autogrow.
Here is a starting point for you in case you are unfamiliar with the Default Trace: Reviewing AutoGrow events from the default trace
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 14, 2012 at 9:59 am
You may also want to review the jobs that run at night. Did any maintenance plans run that may have rebuilt indexes?
March 14, 2012 at 10:18 am
Thanks for your time, we do have the default trace enabled. But, unless I'm missing something which is quite probable, I can't see that the relevant SQL is captured. Actually I rarely find the default trace very useful.
I know it's a scheduled job but I can't see the code it's running, the application guy left a long time ago, the app isn't supported & I need to pin point & ideally optimise the SQL.
So, presuming there's only the default trace running, there's nothing that can tell me what WAS running?
I guess I hoped there was something I didn't know about:
? log readers (know nothing about this, but I'm guessing this won't be at all useful since backups have all run & log is now at 2% used)
? dmvs
? Er...grasping at straws now.
March 14, 2012 at 11:24 am
snomad (3/14/2012)
Thanks for your time, we do have the default trace enabled. But, unless I'm missing something which is quite probable, I can't see that the relevant SQL is captured.
And you won't. Please re-read my post. The Default trace will tell you WHO was running the process that caused the autogrow (and if memory serves, from which host). As I said, if you did not have a trace running capturing SQL at the time of the autogrow your options are limited.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply