Solved

Msg 9002 -  Unfinished procedure

Posted on 2004-10-06
17
466 Views
Last Modified: 2008-02-01
Hi,
I ran the following procedure on a database which has about 25 million records.  For each record, there's a ticker name and some have ~del as their suffix which I want to replace.

update prices
set ticker = replace(ticker,'~del','_')

then after 10 hours, I got this message.

Server: Msg 9002, Level 17, State 2, Line 2
The log file for database 'AIQ' is full. Back up the transaction log for the database to free up some log space.

I checked the collumn and it looks to me like the procedure was not completed.  I have two questions regarding this.
1.  Must I run the whole procedure again?  Perhaps the procedure really was done and I'm not seeing it.
2.  Is there a way to run this procedure more quickly?

Also, I detached the database then manually deleted the log by going into explorer and dragging it to the recycle bin, then reattached the database.
3.  Is this an okay way to go about this?
4.  How can I be sure the log won't fill up again?  (I'm not sure how big it was when I started.

THANKS A TON FOR ANY HELP!!!  I really appreciate it.  I setting this at 500 points because it's really several questiions.

Evan
0
Comment
Question by:EvanBrenner
  • 10
  • 7
17 Comments
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 12238050
1.  Yes.  The other updates should have been backed out (undone) automatically.

2.  Probably.  Mainly, update only those that need it, rather than every row:
update prices
set ticker = replace(ticker,'~del','_')
where charindex('~del', ticker) > 0
Also, pre-allocate a much larger log, change the 10% log increase to a fixed amount (such as 50M), don't limit the total log size, and make sure there is plenty of space on the drive for the log to grow.

3.  It's preferable to avoid if possible, however it can be used if in a big hurry or in an emergency.  Will post more on this shortly.

4.  Update in batches rather than all rows at once.  Will post more on this shortly also.
0
 

Author Comment

by:EvanBrenner
ID: 12238081
Scott,

Great!  Thanks a ton.  Looking forward to your next posts and I will begin working on the stuff you wrote thus far.

Evan
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 12238120
3.  Normally this will shrink the log without resorting to detach/attach:

BACKUP LOG databaseName WITH TRUNCATE_ONLY

DBCC SHRINKFILE (log_logical_file_name, 32)

4.  Set a row limit before issuing the UPDATE and then keep re-issuing the command until all rows are updated.  You could also shrink the log between each update, or after several updates, if desired.

SET ROWCOUNT 100000  --<<-- do updates in batches/blocks of 100,000 rows
SELECT 'Starting updates ...' --<<-- force @@ROWCOUNT to be > 0 when entering loop

WHILE @@ROWCOUNT > 0
BEGIN
    update prices
    set ticker = replace(ticker,'~del','_')
    where charindex('~del', ticker) > 0
END --WHILE

SELECT 'End of updates'
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 12238200
3.  Sometimes for a very large log this can be a lengthy process.  Then you can use your original method **but be SURE to do a CHECKDB first -- if you detach a damaged db it won't re-attach**:

DBCC CHECKDB('yourDbName')

If no errors reported:

EXEC sp_detach_db 'yourDbName'

--delete log via windows, etc.

EXEC sp_attach_db 'yourDbName', '<full data file path>'


Note the method used: **ALWAYS** use QA to detach/attach, *NOT* EM; it is too flaky for that and cause errors (trust me, I've seen it).
0
 

Author Comment

by:EvanBrenner
ID: 12238343
I'm a bit unclear about the second and last line of the procedure you wrote (pasted below in this message).  Are those actual commands or comments?
Also, it looks to me that this would do the first 100k or will it do more?  (I'm very new to this.)  If only the first 100k, how would it proceed to more?

SET ROWCOUNT 100000
?  (SELECT 'Starting updates ...' --<<-- force @@ROWCOUNT to be > 0 when entering loop)

WHILE @@ROWCOUNT > 0
BEGIN
    update prices
    set ticker = replace(ticker,'~del','_')
    where charindex('~del', ticker) > 0
END --WHILE

?  (SELECT 'End of updates')

Thanks.

0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 12238967
The first SELECT is just a dummy command to insure that the @@ROWCOUNT is greater than 0 so the loop code will be executed the first time; after that, the loop will continue running as long as it finds new rows to update (@@ROWCOUNT will contain the number of rows updated).

The last SELECT is just a dummy command to "complete" the message of the first; otherwise the output seems "unbalanced".
0
 

Author Comment

by:EvanBrenner
ID: 12242027
Still having a problem with this.  Following is what I enter in the query analyzer and it doesn't make the changes I need.

SET ROWCOUNT 100000
WHILE @@ROWCOUNT > 0
BEGIN
use aiq
    update prices
    set ticker = replace(ticker,'~del','_')

END --WHILE
SELECT 'End of updates'

Any thoughts?  Thanks.

Evan
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 12242188
1) Move the USE statement to the top
2) Be sure to use the WHERE clause, otherwise you'll update the same 100000 rows over and over


use aiq

SET ROWCOUNT 100000
WHILE @@ROWCOUNT > 0
BEGIN
    update prices
    set ticker = replace(ticker,'~del','_')
    where charindex('~del', ticker) > 0
END --WHILE
SELECT 'End of updates'
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:EvanBrenner
ID: 12242284
For some reason it's just not running and I'm getting the message "1 row affected"  even though I literally copied your code and pasted it.  My two (beginner's) thoughts are the characters don't cut and paste the same.  Or perhaps, the ticker collumn won't allow underscores, but both of those seem kind of far fetched.  I remain stumped.  Any thoughts?
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 12242700
Maybe they're all updated already?

What do you get from this:

SET ROWCOUNT 0

SELECT COUNT(*)
FROM prices
WHERE CHARINDEX('~del', ticker) > 0
0
 

Author Comment

by:EvanBrenner
ID: 12242781
Good point.  I ran it and came up with this number:

12824296
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 12243269
Yikes, that is a lot.  You did include the WHERE clause, right?  :-)

Try it without the loop and see if you get a message to the effect that "100000 row(s) were updated" ...

SET ROWCOUNT 100000
update prices
set ticker = replace(ticker,'~del','_')
where charindex('~del', ticker) > 0

... just to confirm that the statement itself works.

0
 

Author Comment

by:EvanBrenner
ID: 12243291
Yes, that just worked.  Awesome!  Now I just have we just have to get the loop working.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 12248735
@@ROWCOUNT would be very sensitive to other statements.  Probably it's better to do it this way:


DECLARE @atLeastOneRowUpdated BIT
SET @atLeastOneRowUpdated = 1

SET ROWCOUNT 100000

WHILE @atLeastOneRowUpdated = 1
BEGIN
    update prices
    set ticker = replace(ticker,'~del','_')
    where charindex('~del', ticker) > 0
    IF @@ROWCOUNT > 0
        SET @atLeastOneRowUpdated = 1
    ELSE
        SET @atLeastOneRowUpdated = 0
END --WHILE


Note, however, that will keep running until *all* 12M (or whatever) rows are updated.

You could also add a counter to control the max number of times:


DECLARE @atLeastOneRowUpdated BIT
DECLARE @loopCounter SMALLINT

SET @atLeastOneRowUpdated = 1
SET @loopCounter = 1

SET ROWCOUNT 100000

-- update rows until none are effected OR until the loop has executed 5 times
WHILE @atLeastOneRowUpdated = 1 AND @loopCounter <= 5
BEGIN
    update prices
    set ticker = replace(ticker,'~del','_')
    where charindex('~del', ticker) > 0
    IF @@ROWCOUNT > 0
        SET @atLeastOneRowUpdated = 1
    ELSE
        SET @atLeastOneRowUpdated = 0
    SET @loopCounter = @loopCounter + 1
END --WHILE
0
 

Author Comment

by:EvanBrenner
ID: 12249365
Scott - can't tell you how much I appreciate your help.  My partner is in today and he and I are going to work with this code and try and make it work.

Just so I'm clear, is it possible that that this can be sped up by as much as I think?  In other words, the 40 million records took ten hours, but it seems like doing it in batches of 100K will take about 5 minutes.  Is that factor of improvement realistic?  Have you seen it so dramatically improved in the past?

Again, thanks.  You make what I've experienced as the cold world of uncooperative computers a friendly place!

Evan
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 12250511
Each run of the 100000 will get slower and slower, because SQL will have to search further and further into the db to find 100000 matches.  However, overall the time should still be less because the logging will not be nearly as severe doing 100K vs. 10M.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 12253094
Btw, VERY IMPORTANT:

Do not add any statements between the UPDATE and IF @@ROWCOUNT check:

    update prices
    set ticker = replace(ticker,'~del','_')
    where charindex('~del', ticker) > 0
-- **don't add statement here **
    IF @@ROWCOUNT > 0
        ...
-- **add statement(s) here if needed **


Most any statement will affect the value of @@ROWCOUNT -- *not* just SELECT/UPDATE -- so you don't want to damage the value in that special system variable by adding a statement betwee the UPDATE and the comparison of it.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now