hai all
why my database .log sometimes getting bigger signifigantly?
so sometimes the user getting an error message telling the .log file is full
so i must do the truncate from sql query anlyzer
1). Your maintenance plan should at least have a backup procedure
2). When importing fob’s with tables, change your recoverymode to simple. (After making a backup).
Hi Stan
Are you doing a transaction log backup as well as a full backup? I would strongly suggest that you schedule a transaction log backup every 15 mins to allow for a full disaster recovery plan to be implimented.
If you do not make a transaction log backup then the tramsaction log is not truncated when the full backup is made, you have to do both backups.
Paul Baxter
exactly… i did full back up
log truncated may be i do it every 2-3 weeks
schedule a transaction log backup every 15 mins? i do it through sql server or direct from navision?
You can’t do a transaction log backup from NAV. Set up a maintenance plan with a full backup every night. If your recovery model is simple then that automatically truncates the transaction log. If your recovery model is full or bulk logged then you will have to include a transaction log backup in your maintenance plan. If space is an issue then you can have the maintenance plan delete backup files older than a number of days.
I have a large db, i take daily FULL backup and LOG backup every 15 min, My recovery option is FULL. still my log file is not shrinked automatically. it can go upto 40 gb if we run month end batches. time to time i have to run manual query to shrink it.
any solution ?
I have a large db, i take daily FULL backup and LOG backup every 15 min, My recovery option is FULL. still my log file is not shrinked automatically. it can go upto 40 gb if we run month end batches. time to time i have to run manual query to shrink it.
any solution ?
What can I say… make sure that the TL drive is big enough to hold a certain length of log file. I am working on a customer that had more than 100GB of transaction log in 2 hours, and all they can do is make sure they have the disk space to store it. It’s not going away, it’s part of the way that SQL Server databases work.
Don’t confuse truncating the log with shrinking the log file. A truncate operation clear the transactions that have been committed to disk. It does not reduce the size of the file. If a large transaction (Navision restore) grows the file, truncating will not shrink the file.
Exactly, so even if your full backup truncates the transaction log, the file size may still be the same. This is good, because there will be new space in the log file to store new transactions. It’s like a bucket for transactions. Backup up the database (or the log, which you have to do separately in full recofery model) simply empties the bucket. So truncating the log is emptying the transactions from the bucket. Shrinking the file is making the bucket smaller.
So imagine the TL file is like this (start file [, and file ], space used x):
[xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx ] let’s say the file is 25GB
After full backup (which in simple recovery model automatically truncates the TL):
[x ] file is still 25GB, only very small portion of it is used.
I have similar prob too…
my DB is about 45Gb and my log file si growing up to 120Gb.
I notice that this happen on Sun,
on Sun I am doing re organise index.re0build index.
is it normal?
This article will explain how to shrink the physical log file: http://support.microsoft.com/kb/272318
In a nutshell, you will run a DBCC SHRINKFILE on the log file, using the log file name and target size as parameters. Sometimes it is necessary to run a ‘SELECT * FROM sysfiles’ against your database to get the correct log file name. I don’t recommend using the AUTOSHRINK database option as it can create a lot of unnecessary disk I/O. The worst situation to be in is to have a database that AUTOSHRINKs and AUTOGROWs repeatedly. It’s good for databases to have a little breathing room.
If the database is set to FULL recovery and you are backing up the transaction log regularly, it should stay at a reasonable size. The other suggestions posted here will help you handle specific, exceptional situations that arise. As part of normal DB administration, you should monitor the size of your databases and log files. In addition to helping you spot size issues before they result in application problems, you can also establish some baselines which will make it easier to predict future capacity requirements, for example.
One other concern. When you say the “.log file is full,” does this mean the log file grows to the point that it consumes all the free space on the drive, or that you’ve specified a size limit for the log file, but it needs to grow? In other words, are you out of disk space or just log space? In an ideal environment, using baseline data, you can manually set file size limits, increasing them periodically to meet growth needs, but SQL’s AUTOGROW makes management a lot easier for people who aren’t full-time DBA’s. Just be careful about using the ‘grow by percent’ setting on large files. Adding 10% to a 50MB file doesn’t carry the same storage consequences as adding 10% to a 1GB file.