October 20, 2014 at 3:59 pm
Hello,
I have a simple, most likely not very simple answer. I have a database which log grew pretty big due to lots of transactions, that's normal behavior. My question is why would the initial size for the log changed for the database? I ran backup logs after that and the log size didn't decrease and once I checked the initial file size it was huge. I don't believe anyone changed this on purpose and I definitely know I didn't change it. It seems to me that SQL changed the initial size for the database. Could this be possible? If so, how can we prevent from resetting?
Thank you so much.
October 21, 2014 at 10:59 am
Backup up the log does not cause it shrink. Backing up the log marks parts of it to be available for re-use. You must run a DBCC SHRINKFILE to reclaim the space on disk.
If you are looking at the initial size in the SSMS database properties I believe that actually shows current size. They call it initial size for when you are creating a file.
Right sizing the transaction log is important as you can get a fairly significant performance hit if it needs to grow during business hours and having a log file that is too large can also be a hit when doing disaster recovery.
If you aren't doing regular log backups, which will help maintain the log at a reasonable size, and you don't need the ability to restore to a point in time, you should change the recovery model to SIMPLE.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply