• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2019
  • Last Modified:

Why does SQL 2005 initial log file size change automatically on a database?

I have a database that for some reason the initial Log size appears to be changing on its own. I want it set to 1024 MB and I have set it to that and closed the Database properties window. I then go back into the database properties window and verify that my change is saved and it is. Then in a few hours or few days, the value gets changed, sometimes larger and sometime smaller. For example I set it to 1024 MB this morning and now it is showing 370 MB just 6 hours later. Last week it went from 1024 MB to 180,369 MB.

I am in a very small office and no one else is changing this value so something is doing it within SQL. The database is part of a replication to another server but I cant imagine that would effect it. Any ideas one why this would happen?
0
dond123
Asked:
dond123
  • 3
  • 3
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
The database log is a transactional log, so as your database is used based on your settings this file will grow.

Open SQL Management Studio and right-click your database and choose properties.  Select files and located the log file name from the grid.  Under the autogrowth column, you will see an elipsis (...) button that you can click on.

Once you open this screen you will see a checkbox "Enable Autogrowth" -- is this checked?  If so, the database will grow according to the rate set by radio button and text boxes on same window.

To maintain small size of transaction logs, you can change the recovery model of the database to SIMPLE or have regularly scheduled transaction log backups.  

Hope that helps.
0
 
dportasCommented:
It's important first that you understand the purpose of the transaction log and how it works. Please read the topics in Books Online on transaction logging and backups.

The simple answer to your question is that the log grew because you didn't make it large enough to start with and probably also because you didn't think through your business's data recovery and storage needs. mwvisa1 has suggested some possible solutions but you should not implement them unless you understand the implications.
0
 
dond123Author Commented:
Thanks to both dportas and mwvisa1 but I do understand the use of the transaction log and the fact that it needs to grow to accommodate transactions. I do weekly full backups and daily transaction log backups with a shrink after the backup. I want my initial log file size to be set at 1024 MB so that after I backup the transaction log daily, the log file size will again be at or close to 1024 MB.

My concern is that the initial log file size setting in the properties section of the database is being changed (see attached screen shot). The 129 MB shown in the screen shot was actually set at 1024 MB yesterday and somehow it has been reduced in the properties screen. Other times the number will be increased to a huge size like 180,369 MB. How do I keep that number from changing all the time? It does not seem to happen on my other databases so I cant figure out why it is happening on this one.

Thanks,
File-Size.jpg
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
Kevin CrossChief Technology OfficerCommented:
A daily transaction log backup is not sufficient to keep size contained...you will have to do hourly or more frequent.  Or if you never need the differentials in data from your backup in a recovery situation then change to SIMPLE recovery model then when full backup occurs the logs should truncate automatically.

That value changes as the file grows.  It is not "initial" size as you may be thinking.  It is what it is now until it has to grow another 10%. :)
0
 
dond123Author Commented:
Thanks mvisa1. So you are saying that the initial file setting in the properties window is dynamic and that SQL will change this setting as needed? I thought that setting was user defined and would not change. Once the log file was backed up and shrunk, I thought that setting told SQL what to set the initial file size of the log to.

Having said that and accepting your explanation, when my transaction log gets to 180,000 MB in size and is backed up and shrunk, how come sometimes when I come into the properties screen the initial file size of the log is still at 180,000 MB and I cant reduce it without detaching the database and reattaching it without a log file? This causes the log to take up this enormous amount of disk space without needing it and my transaction log only gets that big maybe once a month.
0
 
Kevin CrossChief Technology OfficerCommented:
Think this has to do with frequency of log backups.  If you don't have frequent transactional log backups, and then use DBCC SHRINKFILE for example I have found I usually have to run through at least twice to actual do anything useful to the size of the log.

On a heavily used database that I am doing hourly backups on my log stays a decent size.  Right now while in between backups as not yet 11a -- it is sitting at 57MB and devleopment copy of same database we are not doing frequent backups on is at 227MB.
0
 
dond123Author Commented:
Thanks mwvisa1.
0
 
dportasCommented:
Do not keep shrinking the transaction log file. Shrinking is harmful and there is absolutely no need to do it regularly. Get it to a stable size and then leave it alone.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now