![]() ISNULL(suser_sname(dtb.owner_sid),”) AS , Insert into #tmp_db_hadr_dbrs select group_database_id, synchronization_state, is_local from _hadr_database_replica_statesĪS sysname),””) + ‘]’ + + quotename(dtb.name,””) + ‘]’ AS ,ĭtb.containment AS ,ĭtb.recovery_model AS , Select = HAS_PERMS_BY_NAME(null, null, ‘VIEW SERVER STATE’) In deadlock report I saw the nvarchar(4000))Ĭreate table #tmp_db_hadr_dbrs (group_database_id uniqueidentifier, synchronization_state tinyint, is_local bit) It is quite unpleasant during an Application Release deployment… It seems when someone has this permission, he can perform some queries, that put a SHARED Database Lock on a Database, that is in single_user mode! He was just connected to the server with SSMS, and listed the Databases. I had the same problem, but the other session was a normal user session, who had VIEW SERVER STATE rights. ![]() WHERE l.request_session_id = s.session_idĪnd resource_database_id = db_id('') SELECT db_name(resource_database_id) AS DB_Name, request_session_idĪND EXISTS (SELECT * FROM sys.dm_exec_sessions s I personally don’t like using sp_who so I used this little query to find the connection. In addition on my latest run through of this I had to find and kill the one user connection before any of the above would work. Here is the solution: SET DEADLOCK_PRIORITY HIGHĪLTER DATABASE SET MULTI_USER WITH ROLLBACK IMMEDIATE In the interest of brevity and not to take anything away from the original answerer (please go upvote their answer, it’s proved HIGHLY useful). Update: I just needed this again and realized that I had to skim through a lot of text to find the link to get the answer. So next time you find yourself stuck on a problem, and you’ve been working on it for a while with no progress, don’t be afraid to reach out for some help. But for whatever reason I didn’t put the two together until someone else suggested it. And in fact, I did know about deadlock priority ahead of time. I consider myself reasonably good at T-SQL. So what’s can you take out of this? Well, aside from running into my specific problem, which is presumably pretty rare (first time in 20 years for me), it’s an interesting view in resolving a problem. I was able to make the change without any problems and everything moved along smoothly from there. Now I decided to simplify things and just changed my deadlock priority then went straight to MULTI_USER. One of the answers suggested changing the deadlock priority before running a series of ALTERs to set the database OFFLINE, back ONLINE, and then finally back to MULTI_USER mode. From what they said, their problem, and quite possibly mine, was caused by the system trying to do an auto statistics update. Finally, though, Robert Davis ( b/ t) sent me to this link on dba.stackexchange. Now I don’t know about you but I hate rebooting a server because of a problem like this. After some discussion, it was starting to feel like we were going to have to reboot. I spent a little while trying various things and searching through forums before I went for help on twitter using the #SQLHELP hashtag. Interestingly when I tried to do the ALTER instead of just hanging I immediately got a deadlock error. Don’t ask me how.) I wasn’t able to get that exclusive access I needed. So because they were holding locks on the database (And somehow even though it was in single user there were multiple sessions with locks in the database. Why does it matter that they were system sessions? The important thing to remember here is that these sessions can not be KILLed. In my case the problem sessions were all TASK MANAGER sessions. ![]() These sessions include the LOG WRITER, RECOVERY WRITER, TASK MANAGER etc. The best way to tell is that the is_user_process flag in the sys.dm_exec_sessions DMO will be a 0. They typically (but not always) have session IDs under 50. ![]() System sessions are those created by SQL itself. Which meant I wasn’t able to get exclusive use of the database which is required to do an ALTER DATABASE to set it back into MULTI_USER.Īt this point you may have a couple of questions so let me try to answer (some of) them: How exactly was it stuck you ask? Well, 4-5 system sessions were holding locks on the database (and blocking each other). ![]() A database was stuck in single user mode. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |