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.
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.
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":
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.
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.
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. "