SHRINK Txn Log Not Shrinking the Log
-
Our Transaction Log backups complete successfully. I then manually SHRINK a specific Log (which says Alloc = 7,700 MB Minimum is 0 MB) Upon successful SHRINK -- The file allocation remains at 7,700 MB. (most of the DB Logs shrink down successfully but a couple of my Logs are NOT shrinking)
Is there a properties setting I can modify to allow the Log to shrink down to 0.
(I realize there is overhead when I shrink the Log down to 0 as it needs to reallocate the space.. I am not concerned about the overhead. I need to get the log shrunk back to 0 !! )
thx in advance..
BT -
Hi,
I use DBCC SHRINKFILE (Logical log name, Size)
I would check DBCC SQLPERF(LOGSPACE) to make sure that the percentage of the log file used. You cannot shrink the log file any lower than the percentage used.
Also, it is worth considering that shrinking the log file to the smallest possible size will have a knock on effect when the log grows again, especially if you only grow the log file by a small amount each time. Ideally you need to work out how big the log can grow to between log backups and add say 20% to cover an increased load. Additionally, set the growth (percentage or MB) to a larger value to avoid repeated growth of the log as this hurts performance.
Finally, if the log is growing to a large size repeatedly, it may be worth considering the frequency of your log backups to keep things in check.
-
I use DBCC SHRINKFILE too. Identical to DBCC SQLPERF(LOGSPACE), I log on to the server and use Windows Explorer to view the actual file size allocation. I realize you cannot shrink the log file any lower than the percentage used -- but as I mentioned in my original post, the transaction log is backed up and showing that Log can be shrunk to 0 MB. Also, as I mentioned in my original post ~ I am NOT concerned about the overhead.
Is anyone aware of a properties setting which is inhibiting the SHRINK of the log?
BT -
After further review
I found that 2 of the DB's are Reporting DB's (activity except READs against these DB's) therefore we never setup transaction log backups.. (no need to) hence, the SHRINKs were not taking effect.I ran a BACKUP LOG dbname WITH NO_LOG then successfully shrunk the DB log down to a small size.. thanks for your feedback.
BT -
What is the output of DBCC SQLPERF(LOGSPACE)?
If the database is read only, it may be worth setting the database mode to simple. This will ensure the log does not grow.
-
You could also try using Backup log <db name> with truncate_only
and then try shrinking the log file using Dbcc shrinkfile.
-
Yes I have to do this often to free the virtual logs in SQL 2000 myself before a shrink will occurr.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply