October 3, 2006 at 5:41 pm
We have a case where the SQL log files continue to grow even though they are being backed up regularly. The recovery mode is 'Full'; the DB size is only about 6 GB, but the logs are over 27 GB at this point.
When viewed in Enterprise Manager, the logs are over 90% full, so they are being fully utilized (not a lot of empty space).
This isn't normal, is it?
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC
October 3, 2006 at 11:38 pm
When was the last full database backup?
Try issuing a checkpoint against the database before the next transaction log backup. You might like to schedule a more frequent transaction log backup to keep the log file from growing too big!
October 4, 2006 at 3:43 am
ackup the transaction log. Backing up of the transaction log will automatically issue a checkpoint. Then you can use DBCC SHRINKFILE to actually reduce the size of the log file.
Pankaj Khanna
Database Administrator - SQL Server 2000
Keep hope to keep you intact...
October 4, 2006 at 4:04 am
Hi,
you could also create an backup transaction log job with "SQL Server performance condition alert", use the counter "percent log used" and "rise above" with a value "60.
With this you have an automatic transaction log backup and its independent from a time schedule. I hope you understand this 🙂 i am not a native Englisch speaker 🙂
October 4, 2006 at 6:46 pm
Two further observations:
1. Backing up the transaction log will only issue a checkpoint if you are using the NO_LOG or TRUNCATE_ONLY clauses (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_241g.asp);
2. Could your problem be related to this fix in this KB article?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply