Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005
    Posts
    7

    Unanswered: URGENT: Explosive log growth

    All:

    I've got a serious problem happening now with the transaction log on one of my databases. The log keeps growing at an alarming rate - avg. 1GB/hr on a ~550MB database; there's nowhere near that much insert/update activity in the database. This is causing space problems with the (hourly) log backups.

    The database serves an internal web site; both the web server and the SQL server have been bounced, but this keeps happening.

    Profiler shows many, many TransactionLog events in Profiler - orders of magnitude greater than other events. User activity is no greater than it ever is. DBCC OPENTRAN consistently returns no open transactions. I've created a job to checkpoint and truncate the log right before the log backups, but that's obviously only a stopgap for the backup space problem & not a solution.

    Do you have any suggestions as to where to go from here? I'm at a loss, and this can't stay as it is. Thanks for ANY input...

  2. #2
    Join Date
    Jan 2005
    Posts
    7
    I've just noted that the SPID with all the TransactionLog events is also running a fair number of SQLTransaction events with ObjectName of "buildstats" or "Flush IDES", about which I can't find any information. The TRansactionLog events are still much greater in number than these.

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    is someone doing a huge bulk insert without specifying BATCHSIZE?

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I had the problem of a log file growing extremely large (one of the dbs was doing massive record imports, another wasn't). In the backup maintenance plan, I backed up the transaction log as part of the backups (truncate log on checkpoint). I found that not backing up the transaction log as part of the full backup tended to cause the log file to grow extremely large, especially the db where I was only doing full backups once a week. I changed the backup plan to do backups nightly (backing up the transaction log as part of the backup maintenance plan which seemed to take care of the problem.)

    Also FYI: I also had the problem of not being able to shrink the huge log file. I found that changing the Recovery method from Bulk Insert/Full to Simple and then back to Bulk Insert/Full again, I was then able to shrink the log file (a little quirk I found with SQL Server.) You may also want to look at the Recovery Model and possibly changing it to see if this helps with the large log file. Also look at the setting for the growth of the log file.
    Last edited by pkstormy; 09-15-07 at 23:55.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •