Sql Server tools are hanging..

Question

Post reply

Sql Server tools are hanging..

Jeswanth Jaishanker

SSCommitted

Points: 1869
More actions
June 16, 2005 at 6:09 am

#164682

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

--------------------------------

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

Check your Sql Server Service Manager - is it running ? Is it configured to start automatically ?

**ASCII stupid question, get a stupid ANSI !!!**

Lowell SSC Guru Points: 323518 More actions · Answer 2

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeswanth Jaishanker SSCommitted Points: 1869 More actions · Answer 3

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.

--------------------------------