| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-12-05 : 08:49:55
|
| Nathan writes "I am running an update statement, based on a join with 2 tables - both having indexes on the respective columns - on a table with about 800,000 rows of data and I keep getting the following error:Server: Msg 9002, Level 17, State 2, Line 2The log file for database 'Sample' is full. Back up the transaction log for the database to free up some log space.I have already tried the following steps:1. Backed up both the transaction log and full database 2. Truncated the transaction log3. Increased the log file size4. I have the database options set to truncate on checkpoint 5. The The script runs for about 2 hours and then keeps giving the same error mentioned above inspite of me trying the above mentioned alternatives.Please let me know what I can do further to avoid this problem." |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2001-12-05 : 09:48:54
|
| Probably you don't have enough space in the log to hold the entries for all the updates in the statement.probably best to do it in batches.Insetr the primary key of one of the tables included into a temp table then just joni to the top say 5000 recs of the temp table for the update - then delete those recs from the temp table - keep looping until no recs left in the temp table.This should leave enough time between updates for the log to truncate.(also make sure that no spids have open transactions as this will stop the log from truncating too).==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
 |
|
|
JNotenboom
Starting Member
27 Posts |
Posted - 2001-12-05 : 10:52:19
|
| Hi,Be sure to take a look at this:- Right click on yr database. Go to properties;- Go to the tab 'options';- Select 'simple' (instead of 'full') as the recovery model. What it does is just not logging transactions in the transaction log. (what's in the name ;) )- Run your proces again.Regards,JohanOur solution will be part of your problem |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2001-12-05 : 12:40:02
|
| JNotenboom>>Select 'simple' (instead of 'full') as the recovery model. What it does is just not logging transactions in the transaction log. Are you sure it doesn't just truncate the log?4. I have the database options set to truncate on checkpoint Hints that this is v7-.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
mono
Starting Member
36 Posts |
Posted - 2001-12-05 : 16:01:03
|
| I've come across Nathan's problem before and Itried setting the logging model to Simple as rob suggests but it had no effect. Does one need to stop and restart the db or do a backup or whatever to actually make it take effect?mono |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2001-12-05 : 16:54:13
|
| AFAIK, changes in recovery model settings on SQL2k take effect right away. The reason why it does not help in a situation like Nathan's is that simple recovery mode does not turn off logging completely -- all non-bulk operations are still logged (and log is truncated on checkpoint after transaction has completed). So when updating many rows, it is still possible to run out of space. The best solution (as nr already pointed out) is to break up the update into several smaller ones. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2001-12-06 : 06:41:30
|
| >> 4. I have the database options set to truncate on checkpoint Sort of miplies that this isn't v2000.Still think the problem is the tran log not being bis enough for the update so clearing the log won't help.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
JNotenboom
Starting Member
27 Posts |
Posted - 2001-12-07 : 09:51:18
|
| Hello again,Indeed my tip was based on SQL 2K. It does not contain the option 'truncate on checkpoint' anymore I believe.What we did was also based on an empty transaction log. So the transaction log was almost empty when we started the import. So Restore empty database, if you have one.After this, we inserted a few million records into the database (around six tables). This took a while but the transaction log stayed below 2 mb. This was because we put the recovery model on 'simple'.This solutions solved a lot of our problem with the import. for more information about the problem I had, search for me in this forum......;-)b. regards,JohanOur solution will be part of your problem |
|
|
|
|
Problem with Transaction log