March 28, 2005 at 9:18 am
I recently had a transaction log file unexpectedly grow to 7 Gigs.
What experience does anyone have with reading a transaction log file?
I've seen ApexSQL but it is $$$.
Is there anything else that anyone recommends?
Thanks.
March 28, 2005 at 9:44 am
I don't know what you consider to be expensive, but Lumigent's Log Explorer was less than $1,000 per server last time I checked. They offer an eval download too, so you can try it before you buy it.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
March 29, 2005 at 10:20 am
Has the application gone through recent maintenance ? Do users have ADHOC query/update capability ? Were there recent schema changes (triggers added for example) ? New tables and indexes added ? Have you investigated the daily scheduled tasks yet ? What does your database maintenance plan look like ? The reason I ask is that there are many possible explanations. There are also a few options in the maintenance plan that can cause 'wild' transaction log growth. It might be a quick fix (only a check box or two) instead of delving into the complexity and potential cost of another utility.
Here's one thing to look for (if combinatinon of the options are checked, they cause log growth:
'Optimizations' tab, 'Reorganize Data and Index pages' and the two radio buttons below it.
Also, what is your database 'recovery model' ? Full, Simple or Bulk Logged ? Full recovery needs periodic transaction log backups. Simple and Bulk Logged do not. However Bulk Logged can cause explosive growth when utilizing Bulk Insert in very large transactions can also cause the behaviour that you are seeing.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
March 29, 2005 at 11:01 am
I agree with Rudy that there might be more appropriate ways to approach this particular situation. From my experience, the most likely scenario is that someone switched the recovery model or changed the log backup schedule (or maybe the log backups are not being done at all).
That said, it has been my experience that Log Explorer (or another tool like it) is indispensable. I wouldn't want to manage a production SQL Server database without having access to a log reader tool, primarily for the flexibility it gives you in recovering data without doing a full restore and bringing the system down.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply