8 Replies Latest reply on Feb 23, 2011 1:20 PM by dmshimself

    Console Freezes - Timeout Expired & Database Locking

    Apprentice

      Version 7.3.2

       

      Hi all,

       

      Looking for a little troubleshooting advice as we've recently been experienceing some lockups with our system; every analyst's console session

      freezes up at the same time, eventually they have to kill the session and wait a minute or two before it'll let them log back in.

      This has only started happening in the past couple of weeks and can happen a couple of times a day.

       

      The error reported on our web server when this happens is:

       

      SqlException
      Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

       

      I've already logged a case with support regarding this but narrowing down the cause is proving difficult, has anyone come across something similar

      in the past?

       

      We thoughtit may be the account on our database server (that SD uses) occasionally locking out that's causing this but this, but this doesn't appear to correspond to the time the freezes are taking place.

       

      The ony recent big changes we've made was to enable automatic knowledge searches, these weren't working previously as the application pool used was

      set to ApplicationPoolIdentity, changing this to NetworkService, after alot of troubleshooting, resolved this.

      Searching is only set to search on loose focus and on 1 field (incident summary), we have very few articles anyway at the moment, However, the first

      freeze we had (with the error mentioned above) first occured before this change was put in place so I'm not sure if they're connected.

       

      Any suggestions appreciated as I'm not sure what's causing the db to lock, it seems fairly random and there's no scheduled tasks/bulk actions set

      in working hours that could be causing this.

       

      Appreciate any help or troubleshooting advice on this one.

        • 1. Re: Console Freezes - Timeout Expired & Database Locking
          masterpetz ITSMMVPGroup

          Hi Darren,

           

          first idea is the tps_user_session table is not cleaned out. But i think that would be one of the first suggestions from support.

          Could you verify, how big your tps_user_session table is?

           

          Best regards

          Christian

          • 2. Re: Console Freezes - Timeout Expired & Database Locking
            Apprentice

            Hi Christian,

             

            Having just checked, that table is around 19mb in size at the moment; it's cleared out each night as a maintenance task.

             

            Thanks for the suggestion.

            • 3. Re: Console Freezes - Timeout Expired & Database Locking
              masterpetz ITSMMVPGroup

              Hi Darren,

               

              to be on the safe side I would deactivate the automatic background search for a week and monitor the server. If no errors come up, activate the background search again and monitor the server. If the problems arise, you can focus on the knowledge search.

              By the way, are there any errors in the eventlog at the time the sql timeout occurs on the web server?

               

              Best regards

              Christian

              • 4. Re: Console Freezes - Timeout Expired & Database Locking
                gramsay Specialist

                We stopped using the KB as it was locking up our system.

                Other things that can cause time outs:

                • Querying the audit trail
                • Using web desk for changes and clicking on an attached CI
                • Saving queries
                • Any change to windows
                • Some complex crystal reports
                • Saving templates
                • Looking at it the wrong way
                • 5. Re: Console Freezes - Timeout Expired & Database Locking
                  dmshimself ITSMMVPGroup

                  Personally I'd keep away from turning on searching via loose focus and stick to highlighting the text and then using the magnifying glass in console to do the searches.

                   

                  You might want to scan through the performance.pdf guide on the docs CD as that can give you a useful checklist to run through, especially running a profile tuning sequence (unless you are running Oracle).

                   

                  Some people find use SQL isolation mode helps as per the enclosed http://community.landesk.com/support/docs/DOC-22128 Personally for you I think this iwould be providing a workaround rather than getting to the root cause, but it might help in the short term.

                  • 6. Re: Console Freezes - Timeout Expired & Database Locking
                    Apprentice

                    Hi,

                     

                    The only error on the web server that corresponds to the times these freezes occur is the sql exception error mentioned in the original post.

                     

                    Many thanks for all the suggestions, they have given us a few things to try to get to the bottom of this, including perhaps deactivating the automatic

                    searches to see if this helps at all.

                     

                    We're having someone from LANDesk come in this week to perform a health check on the system, so if we've not got to the bottom of this by then,

                    hopefully that'll locate the cause.

                     

                    Thanks once again, appreciated.

                    • 7. Re: Console Freezes - Timeout Expired & Database Locking
                      Apprentice

                      Hi,

                       

                      Just an update to this thread as we believe we may have found the cause of our system freezes, posting this here in case it helps anyone else.

                       

                      The freezes were due to the locking policy we had enabled and what this could allow analysts to do in certain situations that the system didn't like.

                       

                      Original Settings

                       

                      Object Designer > Incident Management > Locking Policy: OptimisticWithWarning

                      Settings > System > Locking Policy Settings: Read Only With Optional Actions

                       

                      With these two settings set like this the system gives a warning to analysts when opening a call if anyone else is currently in that call and the name of that analyst. This also allows the second analyst to still use any optional actions, such as adding an assignment for example and there lies the issue.

                       

                      Situation

                       

                      1. Analyst A opens a call, shortly after analyst B then opens the same call and sees the usual warning message.
                      2. Analayst A adds a new assignment to the call while analyst B is also still in the call.
                      3. Analyst B now adds a new assignment to the call; they have been in the same call all along and have not re-opned it since analyst A made their assignment first.
                      4. Once analyst B saves the assignment window the system freezes for them and all other analysts; the system will eventually error out or the session will have to be killed manually. The database server shows locked messages and takes several minutes to unlock before analysts can then log back in.

                       

                      This happens every time the above situation occured (as far as we know); whether other optional actions also cause the same issue besides adding an assignment I'm not sure as I have not tested this.

                       

                      Solution

                       

                      The solution was to change the locking policy under system settings to 'Read Only'; analysts will not see any optional actions that way when the call is locked by another user and thus prevents the same situation from occurring and locking up the system. A slight inconvenience perhaps, not to be able to add something like a note if another user is already in the call; I'll take that over a repeated system lock up.

                       

                      I'm not sure if anyone else has come across this issue or if it's unique to our setup, in any case, I've reported this to support in case it helps anyone else.

                      • 8. Re: Console Freezes - Timeout Expired & Database Locking
                        dmshimself ITSMMVPGroup

                        Wow that's a nasty one - thanks for the heads up.  No amount of DB tuning is going to help there.