Post reply

Sql Server tools are hanging..

  • 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

     

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

  • June 16, 2005 at 7:09 am

    #566062

    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

    #566382

    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!

  • June 19, 2005 at 11:13 pm

    #566647

    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