IxCraZee
asked on
Updating 5M rows & populate fulltext at the same time...problem!
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
Btw, anyone has any idea that probably how long it takes to run the increamental population if only around 1500 rows changed?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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'