Manual growth of data and log files

Question

Manual growth of data and log files

Casper101

Hall of Fame

Points: 3903
More actions
September 26, 2008 at 12:40 am

#124818

Hi,
I don’t think I am getting this manual growth concept.
I am suppose to manually grow our data and log file and I am suppose to make the sizes big enough so I only have to manually grow them once a month.
I have tried 2 methods and none seem to work - my log file still fills up and every night I get the message: The transaction log for database 'abc' is full.
Method 1: Right click database - properties - Files - I increase the size of the "Initial Size(MB)" column, for example: 5000.
During the night when processes run - they fail with message: The transaction log for database 'ABC' is full. When I get back the next morning - this "Initial Size" value has gone back to 1. Why is this happening?
Method 2: Using T-SQL here is an example of what I did:
ALTER DATABASE ABC
MODIFY FILE (NAME = 'ABC_log',
MAXSIZE = 5GB)
But no matter what I try - I keep getting the error of the log being full.
Can anyone simplify this for me PLEASE? 🙂
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Wilfred van Dijk SSCrazy Eights Points: 9003 More actions · Answer 1

If your initial size has gone back to 1, you probably have autoshrink on. If you don't allow autogrowth (which i don't recommend), don't enable shrink (you'll only have the optimal logfile size until the shrink happens)

I recommend autoshrink, with a maxfilesize. This prevents you from messages like you get and you'll have the guarantee the file won't blow up your disk.

What you also can do is create an performance alert for "% logfile full" and as an action increase your logfile, but I realize this is the same behavior as autogrowth 😀

Wilfred
The best things in life are the simple things

Casper101 Hall of Fame Points: 3903 More actions · Answer 2

Hi,

I have checked and AutoShrink = False.

Here is some more info (in case)

SQL 2005

The Recovery mode = Full for some and Simple for others.

Backups occur every night

Transaction log backups every 10 min for the DBs with Full Recovery model.

I also do not agree with manual growth - but unfortunately have not much say in this 🙁

Any more advice?

Casper101 Hall of Fame Points: 3903 More actions · Answer 3

Ok there was a Shrink DB option in the Maintenance plan that executed every night at 12:00

I have disabled that.

I will keep an eye on it - see if this sorts out my problem

THANK YOU!

Wilfred van Dijk SSCrazy Eights Points: 9003 More actions · Answer 4

OOPS, type error:

I recommend autoshrink, with a maxfilesize. This prevents you from messages like you get and you'll have the guarantee the file won't blow up your disk.

It should be:

I recommend autogrowth, with a maxfilesize. This prevents you from messages like you get and you'll have the guarantee the file won't blow up your disk.

Sorry for the confusion

Wilfred
The best things in life are the simple things

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 5

MV (9/26/2008)
Hi,
I have checked and AutoShrink = False.

If the log file isn’t set to auto shrink, then someone is shrinking it. Check it you have a job that shrinks the log file. You can also run trace with the event “log file auto shrink” to see more details about the shrink operation (who does it, when he does it, etc’).

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/