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.

Mr Curious (courious@somewhere.eu) writes:
> 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.

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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Uzytkownik "Erland Sommarskog" <esquel@sommarskog.se> napisal w wiadomosci
news:Xns9716F29CBA659Yazorman@127.0.0.1...
> Mr Curious (courious@somewhere.eu) writes:
>> 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.
>
> 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@sommarskog.se
>


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

Regards.

Mr Curious (courious@somewhere.eu) writes:
> Thank you. Can I (or rather a server administrator) change this option? Is
> it safe?

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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

> 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@sommarskog.se
>

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.

> 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!)
>

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.

Mr Curious (courious@somewhere.eu) writes:
> 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.

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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

>> 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.
>
> 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.

Thank you very much.