Best Practice for Transaction logs?

Lucaslu

hi Everyone:

Greetings!

I am new to Navision and as well as SQL server 2005, I need your help in regards to Best Practice to setup the transaction logs, should it be auto-grow or fixed space for it usage?

What are the best practive to maintain the transaction logs? delete it? detach it? shrink it? truncate? backup? which are the best way to do it?

The problem i have is if i put it as auto-growth, eventually it will took up all the space available? what should i do?
If I put it as a fixed amount space, eventually it caused the navision stop working? if that happenned, what should i do?


Thank you,

Lucas Lu

nusyaputera

Hi Lucas,

you can check out to this topic,
viewtopic.php?f=33&t=32694

I am quoted Mr bbrown advice about this transaction log, and agree with him.

Do not rely on Auto-Grow to expand data and transaction log files. Auto-Grow should only be considered a safety net. It is there in case an unexpectedly large process suddenly consumes the available file space. Watch the available space and expand manually during low system activity. Avoid expanding in small increments as this will fragment the file on the drive.

Auto-Grow runs when the free space reaches 0% not as it approaches 0%. It does not say “I’m running out of space. Let me create more before it’s all gone”. What it says is “Oh darn! I ran out of space. Let me take over the system and create some more.”. Unfortunately, it does effectively “take over” the system. While the files are being expanded the system (SQL) will not respond to users. To the users, NAV (SQL) will basically stop responding until the expansion is complete. How long depends on the server’s speed and how much expansion must be done. With larger databases it will get longer. Very large databases can effectively stop responding for several minutes. This is why you don’t depend on Auto-Grow. You want to manage the data size so Auto-Grow never runs.

Lucaslu

thank you for your reply!

Gathering Bbrown information, I positively think and suggested the best practice to manage transaction logs is set a maintainance plans as such,

1. backup database
2.shrink database
3. rebuild indexes
3 update statistics
4. data integrity check

I also scheduled it running once a week for the above maintenance, by doing this i won't be too much worry about logs over growth.

You also can use SQL Agent to alert you when disk space nearly used up!

I hope other people can give their suggestions and opinion about the best practice of transaction logs!

bbrown

Lucaslu wrote:

thank you for your reply!

Gathering Bbrown information, I positively think and suggested the best practice to manage transaction logs is set a maintainance plans as such,

1. backup database
2.shrink database
3. rebuild indexes
3 update statistics
4. data integrity check

I also scheduled it running once a week for the above maintenance, by doing this i won't be too much worry about logs over growth.

You also can use SQL Agent to alert you when disk space nearly used up!

I hope other people can give their suggestions and opinion about the best practice of transaction logs!

Do not shrink the database (or the log).

Since the transaction log is (should be) on its own dedicated drive, that fact that it may occupy a large part of the drive is not an issue. But as a general rule you should always maintain 10% free space on NTFS drives.

Create the transaction log large enough so it does not auto-expand between backups. If it is auto-expanding then either increase backup frequency or manually increase the size.

rfigueiredo

http://support.microsoft.com/kb/315512/en-us

Best Practices
For a managed production system, you must consider autogrow to be merely a contingency for unexpected growth. Do not manage your data and log growth on a day-to-day basis with autogrow.
You can use alerts or monitoring programs to monitor file sizes and grow files proactively. This helps you avoid fragmentation and permits you to shift these maintenance activities to non-peak hours.
AutoShrink and autogrow must be carefully evaluated by a trained Database Administrator (DBA); they must not be left unmanaged.
Your autogrow increment must be large enough to avoid the performance penalties listed in the previous section. The exact value to use in your configuration setting and the choice between a percentage growth and a specific MB size growth depends on many factors in your environment. A general rule of thumb to you can use for testing is to set your autogrow setting to about one-eight the size of the file.
Turn on the <MAXSIZE> setting for each file to prevent any one file from growing to a point where it uses up all available disk space.
Keep the size of your transactions as small as possible to prevent unplanned file growth.
Why do I have to worry about disk space if size settings are automatically controlled?
The autogrow setting cannot grow the database size beyond the limits of the available disk space on the drives for which files are defined. Therefore, if you rely on the autogrow functionality to size your databases, you must still independently check your available hard disk space. The autogrow setting is also limited by the MAXSIZE parameter you select for each file. To reduce the possibility of running out of space, you can monitor the Performance Monitor counter SQL Server: Databases Object :Data File(s) Size (KB) and set up an alert for when the database reaches a certain size.
Unplanned growth of data or log files can take space that other applications expect to be available and might cause those other applications to experience problems.
The growth increment of your transaction log must be large enough to stay ahead of the needs of your transaction units. Even with autogrow turned on, you can receive a message that the transaction log is full, if it cannot grow fast enough to satisfy the needs of your query.
SQL Server does not constantly test for databases that have hit the configured threshold for autoshrink. Instead, it looks at the available databases and finds the first one that is configured to autoshrink. It checks that database and shrinks that database if needed. Then, it waits several minutes before checking the next database that is configured for autoshrink. In other words, SQL Server does not check all databases at once and shrink them all at once. It will work through the databases in a round robin fashion to stagger the load out over a period of time. Therefore, depending on how many databases on a particular SQL Server instance you have configured to autoshrink, it might take several hours from the time the database hits the threshold until it actually shrinks.

davmac1

Don't forget that backup database needs one task to backup the database itself and another to backup the transaction log.
If you only backup the entire database, your transaction log will never remove committed transactions, and keep growing.

kriki

Check also this http://mibuso.com/blogs/kriki/2008/12/23/some-tips-tricks-for-the-transaction-log/