Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2001
    Posts
    3

    Unanswered: Transaction Log shrinking mysteriously...

    Hi,

    We are running SQL 7 SP3 with merge replication enabled. We have a maintenance plan running which backs up both the database and the transaction log every night. For some reason, the job seems to be shrinking the physical size of the log file. I reset the size of the transaction log to 500MB last night, and this morning (after the job has run) it is down to 384MB. The autoshrink option for the database is set to false, and we haven't selected the maintenance plan option to shrink the database. Also, the log is set to grow automatically, with unrestricted growth.

    If anyone has any ideas, I'd be most grateful.

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    My guess woulkd be that the maintenance plan is doing it anyway.
    Try using the profiler to log what is happening.

    This is one reason that I advise against using the maintenance exe - you can never be realy sure what it is going to do.

  3. #3
    Join Date
    Sep 2001
    Posts
    3
    Thanks for that. The log is definitely shrinking, but I still can't work out why. The maintenance plan isn't performing any optimizations for this database. I've tried scripting a backup statement for the transaction log and running that against a local copy of this database. When I just run the backup statement, the log still shrinks, but when I add the WITH NO_TRUNCATE statement it seems fine. Although the DATABASEPROPERTY (IsAutoShrink) returns 0, there was a time when this database was set to AutoShrink. Is there any chance that there could be a bug in SQL Server related to this? I'm wondering if for some reason it hasn't picked up the fact that the autoshrink is now set to false. I've tried looking for any documentation on this as a problem with SQL Server, but can't find anything.

    Thanks,
    Lou

  4. #4
    Join Date
    Sep 2001
    Posts
    3
    In case anyone experiences a similar problem, I placed a support call with Microsoft, and this is the reply I got (which worked!!)

    "The "usershrinkfile" mode has a positive number indicating it will try to
    shrink down the transaction log when possible. (It will check periodically
    and try to shrink down tran log as close as to the number it indicated in
    "usershrinkfile" mode). This "usershrinkfile" mode is normally negative (-1)
    UNLESS someone tries to shrink the tran log size and fails. After that, the
    "usershrinkfile" will be a positive number which is close to the target size
    .

    The tran log will automatically shrink until the "usershrinkfile" mode is
    set to be -1. The "usershrinkfile" mode will become -1 if the shrink
    complets successfully at its target size. You can run the following to find
    out what's the value for "usershrinkfile":

    DBCC fileheader(DB_name)

    For example, I have a tran log size of 10MB. I then issue "DBCC shrinkfile"
    command to attempt shrinking the tran log at 1pm. During that time, there
    are many activities on my database and so the tran log does not get shrunk
    right away. However, the "usershrinkfile" mode will changed from -1 to a
    positive number. Because of this, my tran log will shrink later during the
    day if the tran log has free space to be shrunk.

    For example, the activities are done and all transactions are committed
    after 2pm. At this time, if I truncate/backup my transaction log to free up
    space in tran log, my tran log will shrink to a size close to the target
    size I requested before.

    The tran log will periodically shrink itself until the "usershrinkfile" mode
    is set to be -1.



    WORKAROUND:

    In order to NOT have the tran log shrink automatically, we need to set the
    value of "usershrinkfile" back to negative (please note that tran log
    shrinking DOES NOT prevent you from backuping up the tran log).

    We can first increase the tran log by 10MB (so size = X+ 10 while X is the
    original size in MB) and then run "DBCC shrinkfile(file_name, X+9). Since
    there is enough free space, the tran log will be shrunk successfully and the
    "usershrinkfile" mode will be set to -1.

    OR

    If we know that the tran log is 12MB and that it has free space to shrink,
    we can just issue "DBCC shrinkfile(file_name, 11) and the tran log will
    successfully shrink to the target of 11MB and the "usershrinkfile" mode will
    be set to -1. "

    Hurrah!

Posting Permissions

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