SQL Server Performance

17883 Error

Discussion in 'General DBA Questions' started by Raj1979, Mar 30, 2006.

  1. Raj1979 New Member

    Error: 17883, Severity: 1, State: 0
    The Scheduler 0 appears to be hung. SPID 7, ECID 0, UMS Context 0x045530B8.

    I have encountered this error on one of the servers.We do have a SAN and SQL Server is on SP3. I know that this is a problem and applying the SP4 should fix the problem.

    I wanted to know if there is an intermi workaround which will keep my SQL Server running. I tried restarting SQL Server Agnet but that does not help. Agent does not start nor users are able to connect to SQL Server.

    My next option is try restarting the SQL Server service. What if it refuses to start. I havenot applied SP4 before. Will I be able to apply sp4 without SQL Server engines running?

    Please let me know if there are anu interim fixes with which I can keep m server riunning till the end of the day after which I can apply SP4.

  2. simondm New Member

    This error isn't nessicarily fixed in SP4, it is possible that there is a problem. Though you only really should be concerned if this is continually happening.<br /><br />By saying it is fixed in SP4 I assume you are referring to kb810885. It is possible that this is the cause but it may not be the case.<br /><br />From experience I have found this error is usually genreated when a ad-hoc query is running that generates a very large amount of disk activity. For example joining millions of unindexed rows - this creates a lot of load on the tempdb.<br /><br />Did you have any queries or transactions fail when the error happened?<br /><br />SP3 (build 81<img src='/community/emoticons/emotion-11.gif' alt='8)' /> and SP4 have added logging features so that mini dumps are created in the log directory of SQL server. These can be sent off to MS (PSS case) for analysis if this is a continuous problem.<br /><br />I wouldn't fear about restarting your server - this error does not suggest it will not restart. For information when you install SP4 the services will be stopped and restarted by the installer. you may also require a reboot.<br /><br />I hope this helps.<br />Simon
  3. satya Moderator

    We have had this problem many of our servers and it turns out to be if the windows locale settings and SQL Server regional setting are mismatching then during the midnight clock changes sometimes the UMS thread hung due to time mismatch whenever any job executes. So make sure the jobs are executed during this clock change properly and also check if the parallelism is affecting this process.

    Satya SKJ
    Contributing Editor & Forums Moderator
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. thomas New Member

    Also internal diagnostics which generate this error were introduced or enhanced in SP3. So in previous version you could have had schedulers hung (the server would be in a poor state) without you getting this in the error log. And in my experience SP4 doesn't stop it happening.
  5. Raj1979 New Member

    Thanks Every one. Simond you are right by glancing the article kb810885 I thought that it is the issue. After your message I read the aticle again and I understood that it might me be a problem only with big files.

    As of now we experiend it only once. Because of large number of users connecting to the database it is very difficult to tell as what queries were running at that particular time.

    Once again thanks for each of your responses.

  6. satya Moderator

    You can take help of server side trace to capture those long-running resource intensive queries for more information. Also check for any parallelism issue and turn it off.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page