Thread: Table Error

I got this from the Books On-Line...

By default, SQL Server Compact Edition uses row-level locking for data pages and page-level locking for index pages.

Also see this:

Applications do not typically request locks directly. Locks are managed internally by a part of the Database Engine called the lock manager. When an instance of the Database Engine processes a Transact-SQL statement, the Database Engine query processor determines which resources are to be accessed. The query processor determines what types of locks are required to protect each resource based on the type of access and the transaction isolation level setting. The query processor then requests the appropriate locks from the lock manager. The lock manager grants the locks if there are no conflicting locks held by other transactions.

You might have your friend look for any override of SQL server defaults. Or see if any of his code changes the transaction isolation setting.

I don't think that under normal usage it is possible to insert two records with the same auto increment -- I'm pretty sure that's an atomic operation. Even if it were possible, that should only create a corrupt/invalid record in the table -- not corrupt the pages on the hard drive.

Try to run CHECKDB as soon as possible after the error is reported. If CHECKDB shows that the database is clean, then mostly likely the check is only broken in memory -- possibly due to corrupted cache data (from the hardware cache). If CHECKDB is clean, it is most likely that there is hardware problem. Interesting that it only happen on the one table however -- tho perhaps due to the fact that this table may be the most heavily update (??).

There is a known bug in SQL Server 2000, but it relates to doing a SHRINKFILE or SHRINKDB, not normal day-to-day operation -- however see http://support.microsoft.com/kb/811205

Well, latest news is that he didnt have them auto-increment.
He use to get the id from db, increment it and then insert....... do you think that might have been the cause??

Anyway, i have asked him to change to auto-increment and change code too.
And he has done that and uploaded everything, and now waiting to see if data get corrupts again.

Well, he had to fix this Fetch, Increment and Insert problem, but then again, that wouldn't corrupt the chain (tho the plethora of other problems it MIGHT cause...).

I'm still kind of leaning toward a H/W issue. As soon as the error is noticed again, run the CHECKDB and if clean, start considering a H/W issue. Also, I'd ensure that all fixes have been applied to SQL2000.

Well he got changed his host, so lets see what the new host goes!!