By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,261 Members | 1,674 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,261 IT Pros & Developers. It's quick & easy.

Enterprise Manager - problem

P: n/a
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.
Nov 23 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Mr Curious (co******@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, es****@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
Nov 23 '05 #2

P: n/a

Uzytkownik "Erland Sommarskog" <es****@sommarskog.se> napisal w wiadomosci
news:Xn**********************@127.0.0.1...
Mr Curious (co******@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, es****@sommarskog.se

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

Regards.
Nov 23 '05 #3

P: n/a
Mr Curious (co******@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, es****@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
Nov 23 '05 #4

P: n/a
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, es****@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.
Nov 23 '05 #5

P: n/a
> 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.
Nov 23 '05 #6

P: n/a
Mr Curious (co******@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, es****@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
Nov 23 '05 #7

P: n/a
>> 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.

Nov 23 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Browse more Microsoft SQL Server Questions on Bytes