Admin puzzler

  • tcronin 95651

    SSCertifiable

    Points: 6735

    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?

  • AlexSQLForums

    SSChampion

    Points: 14252

    Microsoft didn't find anything?

     

    Alex S
  • tcronin 95651

    SSCertifiable

    Points: 6735

    no help

  • crow1969

    SSCrazy

    Points: 2909

    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.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716562

    sp_whoisactive. Sounds like blocking.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716562

    Wait, still the issue after bouncing the server? Could there be some rollback activity taking place?

     

  • tcronin 95651

    SSCertifiable

    Points: 6735

    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

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88170

    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.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • BrownCoat42

    SSC Enthusiast

    Points: 117

    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

  • mig28mx

    Ten Centuries

    Points: 1022

    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 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply