SA 10.0.1.3960 / Win64 + mirroring running on Windows 2003 Server.
I know it may happen due to the running out of locks. However, 'mytable' has only ~30 000 records - not that much for a database with 8 k pages. Also, it does work on the same database started in standalone mode. What can I do to fix the problem?
Without more details, I believe that this issue is related to the background cleaner process that was introduced in Version 10.
The cleaner process is a background task that performs maintenance tasks, such as reorganizing index pages after a significant number of rows have been deleted. The cleaner process runs only when the server is (largely) idle. However, it does run as a connection (rather than only as a thread) and it acquires shared schema locks on the table(s) that it is modifying.
The issue with DDL statements such as ALTER TABLE ADD FOREIGN KEY is that the connection executing the ALTER TABLE must acquire an exclusive lock on the table, and if it is unable to do so the ALTER TABLE statement fails immediately with the lock error (SQLCODE: -210; SQLSTATE: 42W18) that you have seen. If the ALTER TABLE is attempted while the cleaner holds a shared schema lock on the same table, the ALTER will subsequently fail.
Retrying the operation should succeed.
We are currently working on a fix so that the cleaner process will terminate as soon as any other connection requires exclusive access to the table(s) being processed by the cleaner. That particular change is now undergoing testing. A separate, independent change, available with 10.0.1 build 3985, modifies the error message for SQLSTATE -210 so that the offending process holding the lock (in this case, the cleaner process) is properly identified.