[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2007-10-10
Medium Priority
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
  • 2
LVL 25

Expert Comment

ID: 20047282
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

SQL_SERVER_DBA earned 300 total points
ID: 20048605
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

ID: 20053514
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

jogos earned 200 total points
ID: 20054793
<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.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
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.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

873 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