FTI population: Locking issue shutting down Full-Text Index population

We are implementing FTI on a Linguistic SQL Server Database for performance improvement. The maximum size of database is 20 GB and there are 27 million records in the table on which we are implementing FTI. When We did a small POC with 1.2 million records, the FTI worked fine. But now with 27 million records it' giving the error messages attached in the event logs below.

It looks like the system resources are insufficient for completing the FTI population, but can I pinpoint what is causing problem from the overall system? Can we make some firm conclusions by looking at the attached event logs?

FYI: the free disc space on which the FTI catalogs are kept is 55GB. The SQL Server configuration is 4 processors, 4GB RAM.

Any help in this regard is greatly appreciated. ThanksHi There,

You could always split the process by /8 or /16 different tables and then move them to the new location systematically and not all at one time.

(ISQL/W : Select percentage 25% from blah ...?)

Frequently update and select Table Locking Problem

Hi guys,

I faced problem related to Locking and Isolation Level on Table(s).

My problems is there r some tables which r frequently updated, and I also want to fire select query over those tables every 1 seconds and want to get only committed records.

In current scenario we start transactions with Read Committed Lock for updating records. But in this scenario I can’t get select query result because of some of recourses r used by transactions so after some time it gives Deadlock error.

So I want solution like both operation run simultaneously and get only committed records at a time of transaction running.

Please help me for solving my problem.

It seems to me that a deadlock error would only happen if you were trying to update the same tables in different orders at the same time - i.e., Update tableA, tableB running at the same time as Update tableB, tableA. Txn1 would be waiting for Txn2 to finish, and Txn2 would be waiting for Txn1 to finish.

If what you are getting is just a locking block, it might be a different story.

|||

No in my scenario not happen as u told. In that only one trans running but by default the isolation level is READ_COMMITTED and in that when i trying to run select query it is going in waiting mode till trans is committed or roleback.

but i want to run both simultaniously and get only committed data

|||

Which version you are using 2000 or 2005? If you are using 2005, then i think you could use Snapshot isolation level.

|||

I am use 2005 .

I also found Snapshot Isolation solutions from google, but now my confusation is related to which of belowing two r better for my problem.

ALLOW_SNAPSHOT_ISOLATION

READ_COMMITTED_SNAPSHOT

Also I don't know differance about both of them. Please if u have any idea about them then tell me.

Thanks for giving this hint to solve my problem

|||

There is an excellent article about SQL Server 2005 Row Versioning Based Transaction Isolation.Here is the URL:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5snapshotisol.asp

Please go through the article carefully and workout the examples specified in it.It would work for u.

Let me know if have any problems.

Frequently update and select Table Locking Problem

Hi guys,

I faced problem related to Locking and Isolation Level on Table(s).

My problems is there r some tables which r frequently updated, and I also want to fire select query over those tables every 1 seconds and want to get only committed records.

In current scenario we start transactions with ReadCommitted Lock for updating records. But in this scenario I can’t get select query result because of some of recourses r used by transactions so after some time it gives Deadlock error.

So I want solution like both operation run simultaneously and get only committed records at a time of transaction running

Please help me for solving my problem.

have you tried explicit dirty-read options in the select query ?

select * from mytable with (nolock) where mycolumn = 'hahaha'|||Yes I tried with that option, but it also return uncommitted data which i don't want.|||You cannot read "commited records" only of a transaction since those records actually does not exists untile the tran is closed: updated records are commited all together when you issue a commit transaction.|||

That's thing i knw, but the problem is when any trans for update or insert start on table I can't fire select query on that resources (means that table(s)) and it is going in waiting mode till trans is not committed or roleback.

So i resolve only this problems....

|||That's exactly the expected behavior if you use transactions.
You can use the 'with (rowlock)' option on the update (avoiding transactions) if you don't want other users to be put in wait, in that case a dirty read is allowed.|||

I got one solutions for resolving my problem using Snapshot Isolation.

But there are two types of Isolation READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION

so If u have any idea about them please inform me.

Is this resolve my problem or not?

what is the differance between them?

|||

Try use

select * from SomeTable with(READPAST)

Quote from Server Books:

READPAST

Skip locked rows. This option causes a transaction to skip rows locked by other transactions that would ordinarily appear in the result set, rather than block the transaction waiting for the other transactions to release their locks on these rows. The READPAST lock hint applies only to transactions operating at READ COMMITTED isolation and will read only past row-level locks. Applies only to the SELECT statement.

|||

Two possibilities that I know of, and the one you suggest is one of them. Using snapshot you will get the way the row looked before it was in the process of being edited.

Using readpast, as Alexander suggests is the other.

The question is what the purpose of your read queries is:

If you want a periodic count of where you are in a process, use snapshot

If you want to fetch the next, unlocked row for editing, then use readpast. A common use of this might be a set of rows that you have clerks working on. So you might execute a query like:

select <columns>
from workToDo
where status = 'NOTSTARTED'

In this case it is desirous to skip exclusively locked rows as they might be in the process of being updated. Using snapshot in this case would stink because you never want the user to collide with itself.

|||

Snapshot isolation level is not available in MS SQL 2000.

>> select <columns>
>> from workToDo
>> where status = 'NOTSTARTED'

If there is no index on "status" field then deadlock may occur anyway. Even if there is index on "status" field deadlock may occur because if table is updated with large amount of data and distribution of key values has changed then server may ignore index until UPDATE STATISTICS executed (explicitly or due to it is marked as auto-updatable).

|||Thanks to all for giving different solution. I finally solved my problem using snapshot.