.LOG is Getting bigger

once , i had troubled
when my .log file was getting bigger significantly
usually it just 1 GB, en then boom… it had been 18 GB (one thing that you should know is .MDF is still normal)
do you know why this could happened?

Somebody increased the size of the log file. Check what the free space is in the log file, and you can shrink it.

One way you can increase the log file without change the size of mdf by much is to write to a journal 100 K times and deleting 100 K times. All of it will be written to the log file but the size of the db is still the same. Maybe somebody restored a backup?

exactly… i did restore database
i restore it in same server but different database
is it true if we do the restore, so the log file is getting bigger?

If you restore a Navision-DB, the log file is increased to the same size (or larger) as the DB. This because for SQL it is some bulk-processing, not a restore. This even happens if you put the recovery model to simple.
But it increases the logfile of that DB, not of another DB.

i restore just because i create a new one
i create new database and then i restore from back up i made
on the other hand… my friends that were using database (different database i restore), had an error message… they couldn’t log in because that .log file

Read Books Online about recovery models and how to truncate the transaction log.

We’ve run into this with a number of customers. Even if they use proper backups (full + transaction) the log file still keeps growing.

What recommendations and actions do you perform to keep this under control?

cheers

I already have the solution to reduce .log file

you can use the query analyzer from sql server 2000

and then use this script

BACKUP LOG “DatabaseName” WITH TRUNCATE_ONLY

dbcc shrinkfile (“DatabaseName_log”,50)

Your .Log database file will be more litte

yes, you will reduce log file with that command. Don’t forget that that log files grows based in your SQL Server recovery model. If you don’t make backups to log file isn’t necessary to have full backup. Change recovery mode.

by the way how do we know what recovery model we use to?

You could see/set the “Recovery Model” in the “Properties” of the database using Management Studio or Enterprise Manager.

Truncate will not reduce the size of the file.

Yes, Truncate will only invalidate log file to be reused, but when used together with shrinkfile, log files will decrease.

Growing and shrinking logfiles have it’s costs in performance, avoid using it in heavy databases.

Enterprise manager

Right Click… Properties…

and Then?

I couldn’t find it

Enterprise Manager - SQL Server - Databases - <Navision_DB>

Right-Click (or Double-Click) to open Properties

Page “Options” - Recovery Model

Alternatively you could configure the Recovery Model in NAV: File - Database - Alter; Page “Options”, Recovery Model

Before changing recovery model, understand difference between different models

Why is an 18 GB log file a problem?

  1. The log file should be on a dedicated drives. Therefore, as long as it does not grow larger than the drive, its size should not matter.

  2. Auto-grow of the transaction log file is one of the worst performance killers. Shrinking the file will onkly result in Auto-grow when the system requires the space. This also can result in a frgmented file which can impact performance.

  3. The transaction file should be sized so i does not grow between backups. If it is growing either expand the file or increase the fequency of backups.

OK, I found it

thank you very much Mr. Jörg Stryk …

by the way… what is the usage from recovery model?

what is the best and the most safe recovery model for our navision database?

You’re welcome, but please, no “Mr.” - just Jörg [;)]

“Full” means, that every single transaction is protocolled in the Transaction Log. Once a transaction is committed, the information remains in the log. You could create backups of the TLog, so you could restore the database to (almost) any point in time. Highest safety! This is the recommended setting!

“Bulk” means, that BULK INSERT operations do not cause a full log entry per single transaction, but just one entry per BULK operation. Besides that, it’s almost like having “Full” recovery. Only recommended if you are doing plenty of BULK INSERT transactions.

“Single” means, that each transaction is just temporary logged. Once it is committed, the TLog is truncated, the transaction information is lost. You can not create TLog backups, thus you can not fully restore the database until the last committed transaction. Not recommended for productive databases, this should be used for DEV and TEST databases (as you usually don’t create these backups there)

hehe thanks Jorg

i already use the full back up…

is it a good way to back up like truncate database, like i said before…