11 Replies Latest reply on Aug 8, 2011 6:47 AM by JulianWigman

    System locks frequently and people can't log in - times out

    Adam Wilden Expert

      Hi Folks,

       

      We are on 7.4.

       

      Once or twice a day the whole system seems to freeze and people can't login via either WebAccess or Console (usually both).

       

      We get a timeout error after a couple of minutes.

       

      We've not seen this on previous versions.

       

      Resetting IIS or the app pool clears the problem, as usually does waiting it out for 10 minutes...

       

      We can't see anything obvious causing this - ram and cpu are fine on all servers at the time and other functions on the servers run fine.

       

      Our DBAs have checked and the query using the most resources at the time is:

       

      SELECT T1.tps_name,T1.tps_account_disabled,T1.tps_guid,T1.tps_title,T1.tps_deleted,T1.tps_user_type_guid
      FROM tps_user T1
      WHERE (T1.tps_name=:0) AND T1.tps_deleted=:1

       

      which I assume is the main user lookup?

       

      We've patched for problem 5154 which we had hoped would cure this but we are still seeing it the freeze regularly.

       

      Anyone else seen this?

       

      Are there any diagnostic tools, logging we can use to get moe info?

       

      Thanks - Adam.

        • 1. Re: System locks frequently and people can't log in - times out
          JulianWigman Expert

          Hey Interesting.

           

          I may have had similar experiences, not 100% sure same as you though.

           

          We have recycling set to just once a day (at 21:00),  but there are times during the day I've looked at the web server and seen memory max'ing out.   It runs like this for a while then we notice  it freezes slightly and the memory drops back.  Seems to me that IIS chooses to recycle even if not configured to do during the day anyway. 

           

          Probably totally different issue but hey.

           

          Julian

          • 2. Re: System locks frequently and people can't log in - times out
            Adam Wilden Expert

            Hi Julian

             

            Thanks for your thoughts - I don't think it's recycling in our case.

             

            In fact recycling the app pool isd the quickest way to resolve the issue.

             

            Karen, we aren't using Service Catalogue.

             

            Whether the query is a cause or a symptom I can't say - but I don't think it's a conincidence we get a database spike at the same time.

             

            Cheers - Adam.

            • 3. Re: System locks frequently and people can't log in - times out
              dmshimself ITSMMVPGroup

              No doubt the landesk team have been in contact already.  As a first stab I'd be looking in the event log on the server at the point when it recovers.  They may be some clue in there.  Is this ORacle or SQL Server?

              • 4. Re: System locks frequently and people can't log in - times out
                Adam Wilden Expert

                Hi Dave,

                 

                Nothing in the logs on any of the servers.

                 

                We are on Oracle.

                 

                I get the feeling this is going top be one of those tricky things to find...

                 

                Cheers - Adam.

                • 5. Re: System locks frequently and people can't log in - times out
                  masterpetz ITSMMVPGroup

                  Hi Adam,

                   

                  did you have a stored procedure in your maintenance plan, that clears out the tps_user_session table? This is the table that grows daily and if very much users use the servicedesk, it grows very heavy!

                  We have seen this a while ago at one of our customers, that the iis runs in a timeout because the queries that runs on the tps_user_session table in the background take a few minutes and if the time was to long, iis said timeout...

                  There are some maintenance scripts in the community, that points you in the right way.

                   

                  Cheers

                  Christian

                  • 6. Re: System locks frequently and people can't log in - times out
                    elizabethcombrink Employee

                    Hi Adam - I don't recognise that query as any of mine that are resource intensive.

                    • 7. Re: System locks frequently and people can't log in - times out
                      Apprentice

                      Hi Adam,

                       

                      We occasionally still get locks on our database (7.3.2) although not as frequent now we've done a little toubleshooting.

                       

                      The database would often freeze for upto 5 minutes unless I killed the offending lock, we narrowed some of this down in the past to users trying to perform seperate actions on the same record. For example, the first user making an assignment on the call and then the second user also making an assignment afterwards without the window being refreshed inbetween - the system didn't like that and would lock up each time - a bit of a tough one to track down at the time; we eventually disabled optional actions on locked calls due to these issues.

                       

                      This may not be the cause of your particular issue but the symptoms sound very similar; does this happen at set times during the day or similar points or is it totally random? You've probably already looked into that but it could help to identify whether it's caused by a user, a query they're manually running or a scheduled one or resource issue.

                       

                      Hope this helps

                      • 8. Re: System locks frequently and people can't log in - times out
                        Adam Wilden Expert

                        Hi Folks,

                         

                        Thanks for all your comments.

                         

                        It's really strange - we can't seem to find any obvious reason for this at all.

                         

                        The query seems to run with no issues most of the time so is probably a symptom rather than cause - unless it is clashing with something.  But we have seen the time outs at midnight when I was the only user!  That probably rules out locked calls etc as well.  But we've not seen this at all before 7.4.

                         

                        We are running each site on only one worker process (as if we add more, the Save and Save & Close buttons often return you to the wrong place) but we tested it running on 4 processes and got the same freeze.

                         

                        We run stored procs regularly to clear down tables/sessions but these all run in the small hours and none of our freezes have co-incided...

                         

                        Not sure what to try now - all we can do is keep looking whenever we get a freeze and hope to spot something!

                         

                        Cheers - Adam.

                        • 9. Re: System locks frequently and people can't log in - times out
                          dmshimself ITSMMVPGroup

                          You could turn on SOAP and database access trace in TPS and leave it running?  Depending on the load on the systems this could be quite a viable way to show what TPS was doing.  Worth testing out first to check on load and also asking your support team what they think.  Strange as it may seem we have found a small number of quite significant internal Oracle problems with 10G that are fixed in 11GR1 (supported) and others that are fixed in 11GR2 (not supported).  These only arise depending on the platform used for Oracle, but if you are using 10G it might be worth looking at an upgrade if you were thinking of moving towards that version anyway.

                          • 10. Re: System locks frequently and people can't log in - times out
                            Adam Wilden Expert

                            Thanks for the advice Dave,

                             

                            Will try monitoring (upgrading Oracle not an option at the moment).

                             

                            But I'm fairly convinced it's the Application Pools.

                             

                            We get the freeze a couple of times every day and it usually only affects one pool.

                             

                            Our console runs from our Apps server and our WebAccess from 2 websites on our Web server - each has their own pool.

                             

                            So this morning the Console froze and timed people out, whereas Webdesk users were not affected.

                             

                            All seem to freeze occasionally and recycling the App Pool always clears it.

                             

                            Each pool is running one worker process and is set to recycle at 6.00 each morning (we've also tried a full IIS reset).  Timeout is set to 240.

                             

                            If anyone has some recommended settings that they've found work well we'd be very interested.

                             

                            (Running on physical Windows Server 2008 64bit SP2 with IIS 7,  8-Core Intel Zeons (E5530) with 16GB RAM)

                             

                            Cheers guys.

                            • 11. Re: System locks frequently and people can't log in - times out
                              JulianWigman Expert

                              Adam,

                               

                              I'm sure we have the same issue too.  We are same OS (2008R2 x64) and similar Hardware specs.  We are running under ESX server too though.

                               

                              Let me know when you find a solution! 

                               

                              Julian