July 8, 2019 at 6:42 pm
ok so Friday off I get a call from user doing posting taking over an hour( should take 5 minutes). No blocking do deadlock no high CPU. Active sessions show higher reads than normal. No open trans. Go to mgmt. studio and when I try to open objects on main pharmacy database it times out. Go to query analyzer but when I select the drop down for same database it does not appear. I can query the database in QA using another database. We bounce the instance, bounce the server bounce vm host nothing works. Call Microsoft ( of course my 2 hour promised call back becomes 8). No errors in windows, or sql of any type. It is part of an AG with RO node, so I add dummy table it goes to RO node instantly. FInally after 3.5 hours behavior leaves, no trace of any kind. Still can find no errors anywhere anyone want to take a guess?
July 8, 2019 at 6:58 pm
Microsoft didn't find anything?
July 8, 2019 at 7:24 pm
no help
July 8, 2019 at 8:24 pm
Next time this happens, have a look at the wait_type and/or wait_resource fields in sys.dm_exec_requests. That may give you a few more clues as to what is going on. The last_wait_type field may also give clues, but I doubt will be at all conclusive.
July 9, 2019 at 4:50 pm
sp_whoisactive. Sounds like blocking.
July 9, 2019 at 4:51 pm
Wait, still the issue after bouncing the server? Could there be some rollback activity taking place?
July 9, 2019 at 6:27 pm
no rollback
One anomaly seems to be the query store, it had flipped to read only (there was plenty of space left on disk but the store was a max level which causes it to flip read only). Still nothing anyplace in any log of what happened beginning to think about this as possible, there were no transactions of any type after any of reboots while we tried but I don't think query store shows as open transaction
July 9, 2019 at 8:33 pm
I have seen similar issues when a large data load is processing - or someone is running a process to create/rebuild indexes. These processes can take out a lock that prevents accessing the objects from object explorer (or direct queries).
Restarting the instance should clear that process - unless the person running it restarts it immediately after the instance has been restarted.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 11, 2019 at 7:10 pm
I have a symptom like this regularly on a server, but it usually completely kills the whole box and can't get anything out of it until restarting the instance. The jobs have implicit SQL in them and one of them has retry logic in it. I wanted to try implementing RCSI on it based on Brent's post yesterday, once we get it up to 2016 or 2017
July 30, 2019 at 4:25 pm
Hi,
If the behaivour stops after a while, I believe that is a transaction issue. I will should check temp db in order to see if there is plenty of space. If your share your disk space with another applications, then maybe the other applications are slowing down your sql server. Perhaps a full backup or a massiver copy from/to another server. Also, using the kit from Brent Ozar´s are a big help diagnosing performance problems in sql.
Hope this helps.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply