LOG Entry

rjverma

Hi All,

Anybody help us to stop the SQL Log entry generation, SQL Log entry become 19GB.

garak

Do you have Maintenance Plans like backup log / backup database ?
The SQL Log file grows, when you modify datas (delete, insert / update) and when you make a rebuild of the indexes (this could also be a maint. plan <- this you need to).

Also, when you create your backup maint. plans it is also importent to backup the system databases, because there are stored infos about the server self and all infos about the databases and more ...

The following backup strategy are recommended.

- several times on day a transactionlog backup
- at the evening a full database backup
or
- several times on day a transactionlog backup
- at the evening a different. database backup
- at the last workdate in the week a full database backup

Search the forum for Log and grow. I'm sure, you will find a lot of topics. For example in the SQL Performance or SQL General Subforum. Also read please the manuals on ToolsCD and on NAV installation CD / DVD

Regards

Administrator

[Topic moved from NAV/Navision to SQL General]

Slawek_Guzek

Hi,

Just to clarify: Nobody can turn off SQL Transaction Log. SQL Server writes to Transaction Log all changes made in your database. Transaction Log grows to infinity when:
1. The database is in Full Recovery Mode and the log is not backed up or truncated OR
2. There are opened and not commited transactions (not very likely in NAV, but can happen with NAS for example)

To prevent Transaction Log from growing to infinity you have to:
1. Start doing regular backup of your transaction log (backup of the database is not enough) OR
2. Start truncating transaction log on regular basis OR
3. Switch your database to Simple recovery model OR
4. Hire someone with good SQL+NAV experience to solve your problems.

Maintenance plans (mentioned by garak) are recommended solution for point 1.

Regards,
Slawek

bbrown

o How SQL Server writes changes to the database

 When a user makes a change to the database (insert, modify, delete, etc.), the change is immediately written to the transaction log and also held in memory. Once the change is written to the transaction log, the user’s connection is released and they may continue with the next task. A SQL process then takes the changes held in memory and writes them to the disk. The transaction log is then marked to identify the changes that have been flushed to the disk.

 When a server is restarted following a failure, anything in the transaction log past the last checkpoint will be the changes that were sitting in memory when the system failed. SQL Server will write these changes to the disk as part of the recovery process performed during system startup.