December 3, 2014 at 5:54 am
Hi folks - we've got a database (SourceOne archive in case this leads anyone to a sudden insight) where overnight the tLog is just rocketing in size - to c 100Gb - so quickly the log backup isn't able to keep up(1) with it. Data file's only about 240 Gb. It's only started happening fairly recently and theSourceOne guy hasn't been running any major jobs, apparently.
So - my cunning plan is to get something set up to run a capture (sp_whoIsActive or something) when an autogrow is triggered. I've tried a bit of a google, but not managed to find anything that hits me as a solution.
Is there anyone out there who has done this or is able to provide pointers or links that might appeal to my inherent sense of laziness here? Suspect Extended Events may be in the mix somewhere (2008 R2 Standard)
TIA Andrew
(1) Partly, I suspect because the SAN admin, an evil sort of chap, appears to have the backup servers on a tier of SAN so low I suspect it's actually a pair of elderly blind monks hand illuminating individual bits into sheets of vellum as a storage mechanism. We've spoken on the matter, but he's merely moved house, stopped bringing his car into work, stands with his back to the wall at the back of the platform when the train's pulling in and carefully inspects his chair for suspicious looking wiring. Most frustrating.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
December 11, 2014 at 9:18 am
Got it.
Thought I'd post how I sorted this in case a search engine brings anyone else wanting to do the same here. After first tryring using Service Broker and Notification services (which are deprecated anyway) and running into a blizzard of permissions issues, the following - much easier - plan struck me.
Go into agent, and create a new alert on;
Type SQL Server performance condition alert
Object MSSQL$<instancename>:Databases
Counter LogGrowths
Instance <Database Name>
Alert if ...
rises above value 0 (zero)
In response
Execute job and either create a new job, or select a job you already have to run your preferred solution for logging data at this point - and optionally add in an email notification if you want one
tested it using a job executing sp_whoisactive using the @Destination_Table to output to a collection table and works a treat. now obviously this doesn't tell you what caused the autogrowth as such, but it gives you a starting point.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply