August 18, 2004 at 11:50 am
Hello. I have a production server running SQL 2000. I have the recovery mode set to simple because I do not need the logs. My application basically deletes a large amount of data and then loads data. I have the transaction log set to limit at 30G. The log continues to fill up everytime the program runs and then crashes the application. I get the following error in my log:
Error: 9002, Severity: 17, State: 6
The log file for database 'EventAdmin' is full. Back up the transaction log for the database to free up some log space.
But, because the recovery is set to simple, I can't backup the transaction log. A full backup would take too long.
Any advice would be helpful.
Thanks in advance.
Dorothy
August 18, 2004 at 12:09 pm
You mention that you do not need the transaction logs and I assume therefore that you do not need to recover the database using the transaction logs in event of a crash.
You can use the BACKUP LOG command with the TRUNCATE_ONLY option.
Important Note: This will truncate the transaction logs and your only option to recover in case of a system or media failure will be a restore from your last full database backup.
The syntax of the command is BACKUP LOG <DATABASE NAME> WITH TRUNCATE_ONLY
The command shown above should be issued in Query Analyzer.
Hope this helps.
August 18, 2004 at 12:24 pm
My gutt tells me you've got a uncommitted transaction going on when the log becomes full. Therefore there is no way to truncate it. You will have to either expand the 30 GB log to let the transaction to finish, or let the SQL server abort and roll it back when timing out. Rolling back will take equal amount of time as it's getting so far.
The problem is probably within your application which massively delete and insert in one single transaction. Try separate transaction into smaller sizes so that they could be truncated when committed.
You don't need to backup log with truncate_only because simple recovery mode takes care of it.
August 18, 2004 at 12:54 pm
This illustrates a misunderstanding about SIMPLE recovery mode.
Simple recovery mode DOES still use the transaction log, just like FULL mode. It all has to do with the inactive and active portions of the Tlog.
The difference is that FULL mode will not reuse the inactive transaction log space until it has been backed up.
Simple recovery mode WILL reuse the inactive transaction log space.
You need to 'guesstimate' the largest space that a transaction will need to use. Create your Tlog that size (or a bit bigger). The transaction will run and be logged. Once the transaction has completed, SQL Server can reuse the Tlog space for other transactions.
-SQLBill
August 18, 2004 at 2:01 pm
SQLBill,
Just so I understand, in SIMPLE mode, the transaction log only holds the current transactions being issued, then releases that space when the transaction is complete
but in FULL mode, the transaction log holds all transactions issued, and will not release that space once the transaction is complete, but will store it in the log?
Thanks,
Michael
August 19, 2004 at 5:45 am
In FULL recovery mode, the transaction log will not make available the space until a full backup is performed, or a transaction log backup is performed at which point the inactive portion of the transaction log will be used.
The physical log size will not be returned to the OS until it is shrunk after a transaction log backup.
August 19, 2004 at 7:08 am
Simple mode is more like using an overwrite command. It will overwrite the inactive portion of the transaction log. It won't release it back to the OS, it just reuses it.
Full mode is more like using append. It keeps adding to the log until you do a transaction log backup. Then the inactive portion is 'marked' for overwrite (reuse).
The only way to return the inactive portion to the OS is to shrink the log file.
So, I set my db to SIMPLE and give it 5 MB of space and don't allow growth. Then I run a 2 MB transaction. Now I have 2 MB active space and 3 MB unused. That transaction stops and I run a second transaction requiring 2 MB of tlog. Now I have 2 MB active, 2 MB inactive and 1 MB unused. Then I run a third transaction again needing 2 MB of log space. In Simple mode, it will use the 1 MB unused space and 1 MB of inactive space. So now I am back to 2 MB active and 3 MB inactive. In Full mode, it will use the 1 MB unused and fail because it needs another 1 MB that is not available (unless autogrow is allowed).
Does that explain it?
-SQLBill
August 19, 2004 at 7:12 am
you could also increase the growth factor for the transaction log, in the database properties, and change it to unrestricted growth. If it manages to grow large enough without causing a problem to the normal deletes/dataloads you do, then keep the log at that size and reduce the growth factor back to a smaller percentage again (eg 10%). It depends on whether you have the disk space to have the log file as big as it needs to be (to do your transactions in one hit). If not, your only choice is to reduce the number of records you process each time.
Regards
Peter
August 19, 2004 at 7:54 am
Thanks everyone for the help. I have informed the user that he needs to breakup the parameters on this application. I originally had the logs set at unrestricted, but it got to 140G and crashed the server. So, I moved it to 20G and then 30G, and it is still not finishing the transaction.
Thanks again.
Dorothy
August 19, 2004 at 9:07 am
Transaction logging cannot be "turned off", regardless of the recovery model in place.
SQL Server issues a "checkpoint" every 60 seconds in the transaction log. This interval can change depending upon load, etc., but it is around 60 seconds most of the time.
In a simple recovery model, when a checkpoint is issued, SQL will check if all transactions from the oldest checkpoint forward to the next checkpoint are commited. If so, SQL Server will trauncate the transaction log.
To hopefully clarify a few posts above, if the transaction log is set to autogrow, when a checkpoint is issued (assuming all transactions have been commited) the disk space will be returned to the OS. If the transaction log is set to a fixed size, the amount of free space in the log file will increase. You will have to shrink the file to return the space to the OS.
A few possible solutions:
1. If the actual code is deleting all records from a table, and the syntax being used is "DELETE tablename", change this to TRAUNCATE tablename. Truncate will not log the transactions, but it cannot be used if a foreign key is refering to this table.
2. Similarly, the code can drop and recreate the table(s)
2. If there are a series of insert/updates/deletes, you can force SQL Server to issue a checkpoint in the code by adding CHECKPOINT between each statement. See BOL. This will hopefully cause the transaction log to be truncated more frequently.
Hope this helps.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply