June 16, 2005 at 6:09 am
Hi All,
Suddenly yesterday onwards the SQL Server Tools (Query Analyzer,Enterprise manager) Started hanging if try to select the databases. from the combo box in QA or try to open the DB List in EM.. It will come after 2-3 Minutes. I tried to connect to all sql servers in the network. even i have a developer version in my system is also behaving like this.. any idea why this is happening.
Thanks in advance
Jeswanth
--------------------------------
June 16, 2005 at 7:09 am
Check your Sql Server Service Manager - is it running ? Is it configured to start automatically ?
**ASCII stupid question, get a stupid ANSI !!!**
June 17, 2005 at 6:46 am
I've found that the AUTOCLOSE property can cause EM to open very slowly; this is especially true on instances that are on the personal Edition, where any database that is restored or created has the AUTOCLOSE property as TRUE by default.
for EM to expand the "Databases" folder, each database must be open....if the folder has 30 databases, and each database must be opened before it's drilldown list of information can be displayed, you would see a significant wait time...worse if you have even more databases that need to be opened to get their details such as tables,procs, etc.
I use this cursor as a scheduled task to run every couple of days, since i resotre and create constantly on my dev box:
declare
@isql varchar(2000),
@dbname varchar(64)
declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb')
open c1
fetch next from c1 into @dbname
While @@fetch_status <> -1
begin
select @isql = 'ALTER DATABASE @dbname SET AUTO_CLOSE OFF'
select @isql = replace(@isql,'@dbname',@dbname)
print @isql
exec(@isql)
select @isql = 'ALTER DATABASE @dbname SET RECOVERY SIMPLE'
select @isql = replace(@isql,'@dbname',@dbname)
print @isql
exec(@isql)
fetch next from c1 into @dbname
end
close c1
deallocate c1
Lowell
June 19, 2005 at 11:13 pm
Thank you all for the replies. Again this time also I learned some thing from your replies..
Then I got this problem solved by doing a cold boot.. the server was up for quite some time. i dont know how this is related to the problem i was facing. it not only solved the issue of one server but solved the problem for another two server with out restarting those ones.Quite Strange isnt it ??? this was one of the craziest errors i got ever.. still no clue what happend or what made it work now.
Thanks a Lot
Jeswanth.
--------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply