Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    Akld, NZ
    Posts
    38

    Cool Unanswered: Reindexing and Transaction Logs

    Hi All,

    Just after some feedback on a scenario where we have full logging setup on one of the databases, and the transaction logs are backed up every 60min. At 0000-0100 the log jumps from being a few thousand k up to over 1.7gb.
    I did some profiling for this time, and it appears that this jump is related to the reindexing of the indexes on the database.

    Is this normal for the log file to jump in size so much? Or is this an indication of some other issue (potentially with the indexes)?

    Is there any way that the reindexing can be excluded from the log files or is this a necessity?

    Thanks in advance for your help.

    Cheers
    Troy
    Sideways see's more scenery

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It is quite normal for re-indexing to fill up a transaction log. Especially if you use dbcc indexdefrag.

  3. #3
    Join Date
    Nov 2003
    Location
    Akld, NZ
    Posts
    38
    So I presume there is no 2 ways around this really then? Massive jump in size, which makes for some interesting issues in terms of planning for space *sigh*...
    Sideways see's more scenery

  4. #4
    Join Date
    Dec 2004
    Location
    CA, USA
    Posts
    63

    it's simple

    Your log may stay smaller with database recovery model set to SIMPLE

  5. #5
    Join Date
    Nov 2003
    Location
    Akld, NZ
    Posts
    38
    haha thanks for that - umm but then I loose my logging....sorta defeats the point of backing up the logs :P
    But yeah I hear what you are all saying - suppose I was just after a solution which would allow me to turn off the logging for the index rebuilds and leave it on for all the remainder of the normal i/u/d's in the database....

    Thanks all, have a good Christmas/Break/etc...
    Sideways see's more scenery

Posting Permissions

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