156 reputation
2
bio website blog.ashmind.com
location New Zealand
age
visits member for 2 years, 2 months
seen Mar 14 at 0:11

Aug
6
awarded  Citizen Patrol
Aug
6
comment SQL Server : KEY lock prevents FK on nearby row, but not SELECT
UPDATE: For some reason today I can not reproduce the issue in the exact same way as described in the question. I was able to reproduce (and fix) a different situation altogether, where the problem was caused by a ninja update to a row by the legacy code. I will attempt to delete the question to avoid misleading people.
Aug
6
comment SQL Server : KEY lock prevents FK on nearby row, but not SELECT
For some reason today I can not reproduce the issue in the exact same way as described in the question. I was able to reproduce (and fix) a different situation altogether, where the problem was caused by a ninja update to a row by the legacy code. I will delete the question to avoid misleading people.
Aug
3
comment SQL Server : KEY lock prevents FK on nearby row, but not SELECT
You are right, though I can't provide complete version either, as it some legacy C# code that a) I can not share b) does hundreds of lines of of unrelated stuff. But the idea to reproduce it in similar situation is good, I will try it. My current theory is actually key hash collision, which may explain why it is hard to reproduce (depends on what is included in hash, need to research that).
Aug
3
asked SQL Server : KEY lock prevents FK on nearby row, but not SELECT
Mar
20
comment SQL Server: in what situation can snapshot restore be blocked after SET SINGLE_USER?
So I still do not understand why can I get other users are using the database when I have a single script ALTER DATABASE ...; SINGLE_USER RESTORE DATABASE ...;.
Mar
20
comment SQL Server: in what situation can snapshot restore be blocked after SET SINGLE_USER?
How is it different from SET SINGLE_USER ROLLBACK IMMEDIATE given that current connection is not blocked from asking for RESTORE in either case?
Mar
20
comment SQL Server: in what situation can snapshot restore be blocked after SET SINGLE_USER?
Yes, I can try to, but how can it even happen? Can it reconnect between KILL and RESTORE?
Mar
20
comment SQL Server: in what situation can snapshot restore be blocked after SET SINGLE_USER?
The problem I see is not the SINGLE_USER blocking me from RESTORE, it is Database state cannot be changed while other users are using the database. Wouldn't RESTRICTED_USER potentially cause the same problem?
Mar
20
comment SQL Server: in what situation can snapshot restore be blocked after SET SINGLE_USER?
Can it happen if both these statements are in a single script that is sent to the DB at once? Is it because I am using master?
Mar
20
asked SQL Server: in what situation can snapshot restore be blocked after SET SINGLE_USER?
Jan
26
awarded  Student
Jan
24
asked What is the fastest way to reset SQL Server database if you can not use RESTORE?