Updating 5M rows & populate fulltext at the same time...problem!

Posted on 2007-10-10
Last Modified: 2010-03-19
Hi All, I have several statements that do the update to different fields on a table that stores 5M rows of data. When the first statement done, before the second statement starts, a increamental fulltext population on the same table started. And I found there is a locked error.

Msg 1204, Level 19, State 1, Server SERVER_NAME, Line 1
The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.

I have some questions on this:
1) Does it really a increamental fulltext population will 'lock' the table during the population process?
2) Is there any other possibilities that could cause that error? (there was no other processes running at the same time)
3) For a table with 5M rows of data, if only less than 1500 rows data changed (either new or updated), how long could it take for the increamental fulltext population to complete? That is a quad-cpu server.

Thanks in advance!
Question by:IxCraZee
    LVL 25

    Expert Comment

    1) does lock?
    Guess it does (no experiance with it)
    2)  other proces?
    Activity monitor can tell you that or Profiler trace (trace alse the statement START).
    3) duration?
    Too many factors playing a role

    But take a loock at the STOP POPULATION-possibility (from ALTER FULLLTEXT INDEX)  when your process requirse a 'break'
    LVL 16

    Accepted Solution

    um, you don't want to update 5M records at once, break it up in sections so your transaction log doesn't fill up and lock the process.

    Author Comment

    Ya SQL_SERVER_DBA, after that lock issue, I was thinking to do the update few hundreds rows at a time, till no more rows to be updated. I have not test it out and not sure whether when the population running together with this few hundred rows update will cause any lock issue.

    Btw, anyone has any idea that probably how long it takes to run the increamental population if only around 1500 rows changed?
    LVL 25

    Assisted Solution

    <Btw, anyone has any idea that probably how long it takes to run the increamental population if only around 1500 rows changed?>
    Too many LOCAL factors play a role to give ever any hint on duration.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    759 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

    11 Experts available now in Live!

    Get 1:1 Help Now