Enterprise Manager - problem

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Mr Curious
    #1

    Enterprise Manager - problem

    I have a problem with Enterprise Manager - when I refresh list of tables or
    databases it hangs (is not responding) for long time - about 10-30 minutes.
    What is more important during my refreshement users are unable to work
    normally - it hangs entire server.
    Some other details:
    1. I am only dbo of one database with no administrative privilges.
    2. There are no problems with Query Analyzer
    3. Problem is related to one particular server - I don't have these
    difficulties with my local instance of SQL Server.



    Is there any solution of this problem?

    Cheers.


    Tags: None
    • Erland Sommarskog
      #2
      Re: Enterprise Manager - problem

      Mr Curious (courious@somew here.eu) writes:[color=blue]
      > I have a problem with Enterprise Manager - when I refresh list of tables
      > or databases it hangs (is not responding) for long time - about 10-30
      > minutes.
      > What is more important during my refreshement users are unable to work
      > normally - it hangs entire server.
      > Some other details:
      > 1. I am only dbo of one database with no administrative privilges.
      > 2. There are no problems with Query Analyzer
      > 3. Problem is related to one particular server - I don't have these
      > difficulties with my local instance of SQL Server.[/color]

      Sounds like there are several databases that are set to autoclose. Then
      EM needs to open all of those, to see what permissions you have in them.


      --
      Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

      Books Online for SQL Server 2005 at
      http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

      Books Online for SQL Server 2000 at
      http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

        Comment

        • Mr Curious
          #3
          Re: Enterprise Manager - problem


          Uzytkownik "Erland Sommarskog" <esquel@sommars kog.se> napisal w wiadomosci
          news:Xns9716F29 CBA659Yazorman@ 127.0.0.1...[color=blue]
          > Mr Curious (courious@somew here.eu) writes:[color=green]
          >> I have a problem with Enterprise Manager - when I refresh list of tables
          >> or databases it hangs (is not responding) for long time - about 10-30
          >> minutes.
          >> What is more important during my refreshement users are unable to work
          >> normally - it hangs entire server.
          >> Some other details:
          >> 1. I am only dbo of one database with no administrative privilges.
          >> 2. There are no problems with Query Analyzer
          >> 3. Problem is related to one particular server - I don't have these
          >> difficulties with my local instance of SQL Server.[/color]
          >
          > Sounds like there are several databases that are set to autoclose. Then
          > EM needs to open all of those, to see what permissions you have in them.
          >
          >
          >
          > Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
          >[/color]


          Thank you. Can I (or rather a server administrator) change this option? Is
          it safe?

          Regards.


            Comment

            • Erland Sommarskog
              #4
              Re: Enterprise Manager - problem

              Mr Curious (courious@somew here.eu) writes:[color=blue]
              > Thank you. Can I (or rather a server administrator) change this option? Is
              > it safe?[/color]

              Yes. Autoclose is a very dubious option. There are some contexts where
              it's good, but not many.

              I believe that Autoclose is on by default in MSDE instances. Where it may
              make sense, because the database is only accessed from an application that
              is run infrequently. But as soon as you start to access the server with
              Enterprise Manager, autoclose becomes a problem. (Note that the license
              terms does not permit you to access MSDE from EM!)


              --
              Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

              Books Online for SQL Server 2005 at
              http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

              Books Online for SQL Server 2000 at
              http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

                Comment

                • Mr Curious
                  #5
                  Re: Enterprise Manager - problem

                  [color=blue]
                  > Yes. Autoclose is a very dubious option. There are some contexts where
                  > it's good, but not many.
                  >
                  > I believe that Autoclose is on by default in MSDE instances. Where it may
                  > make sense, because the database is only accessed from an application that
                  > is run infrequently. But as soon as you start to access the server with
                  > Enterprise Manager, autoclose becomes a problem. (Note that the license
                  > terms does not permit you to access MSDE from EM!)
                  >
                  >
                  > --
                  > Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
                  >[/color]

                  Thank you again. This is not MSDE of course. I'll try to find out somethig
                  about this option (how to switch off etc.).

                  Cheers.


                    Comment

                    • Mr Curious
                      #6
                      Re: Enterprise Manager - problem

                      > Yes. Autoclose is a very dubious option. There are some contexts where[color=blue]
                      > it's good, but not many.
                      >
                      > I believe that Autoclose is on by default in MSDE instances. Where it may
                      > make sense, because the database is only accessed from an application that
                      > is run infrequently. But as soon as you start to access the server with
                      > Enterprise Manager, autoclose becomes a problem. (Note that the license
                      > terms does not permit you to access MSDE from EM!)
                      >[/color]

                      One remark - if Auto close option is responsible - the server administrator
                      should have similair problem?
                      As far as I know he doesn't (I forgot to mention). That's why I suspect some
                      security issue.


                      Cheers.


                        Comment

                        • Erland Sommarskog
                          #7
                          Re: Enterprise Manager - problem

                          Mr Curious (courious@somew here.eu) writes:[color=blue]
                          > One remark - if Auto close option is responsible - the server
                          > administrator should have similair problem?
                          > As far as I know he doesn't (I forgot to mention). That's why I suspect
                          > some security issue.[/color]

                          I think the problem is due to that EM accesses the databases to see if you
                          have permission to it. Since sa has permission to all databases anyway,
                          EM might bypass the check for him.

                          The command to turn it off is ALTER DATABASE db SET AUTO_CLOSE OFF.

                          --
                          Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

                          Books Online for SQL Server 2005 at
                          http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

                          Books Online for SQL Server 2000 at
                          http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

                            Comment

                            • Mr Curious
                              #8
                              Re: Enterprise Manager - problem

                              >> One remark - if Auto close option is responsible - the server[color=blue][color=green]
                              >> administrator should have similair problem?
                              >> As far as I know he doesn't (I forgot to mention). That's why I suspect
                              >> some security issue.[/color]
                              >
                              > I think the problem is due to that EM accesses the databases to see if you
                              > have permission to it. Since sa has permission to all databases anyway,
                              > EM might bypass the check for him.
                              >
                              > The command to turn it off is ALTER DATABASE db SET AUTO_CLOSE OFF.[/color]

                              Thank you very much.



                                Comment

                                Previous template Next
                                Working...