database locks up and the expanding tables in SQL Server Management Studio clear the problem

  • I'm a newbie to this forum so please let me know if I posted this in the wrong place. Thanks.

    Weird problem (has happened 3 times in last two weeks). Access to a database times out for the two programs that access this database. One is an asp.net web site and the other is a VB6 windows application. They continue to time out until I go into SQL Server Management Studio, click on + to expand the list of database, click on + next to the the offending database, click on the + next to "tables", and then wait for over a minute to see this list of tables. Next to "tables" you see "expanding tables". Usually this display happens so fast that you can't read it. Once this wait has been done and the list of tables has been shown then both of the applications are able to use the database.

    Any ideas on what the expand tables operation is doing and why it clears the problem of accessing the database.

    Clearing the problem by doing this procedure has worked 3 times but I have no idea why.

    TIA

  • Possibly the database in question might be set to auto close? Check in the properties -> options section for the database, and if the "Auto Close" option is set to "True," change it to "False" and see how things go.

  • Thanks for the info but the auto close is set to false so I'm still looking for something else.

  • No problem. It sounded like an auto close issue.

    From the way you described the problem, there are other (non-system) databases on this server, which are not having a problem?

  • Is this SQL Server Express? It could be the instance isn't starting without the connection. Guessing. I've never seen a problem that SSMS, as SSMS, fixes.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Yes, there other databases (about a dozen) that I don't believe are having the problem. I don't really have anything to do with them but at least one of them is used by a web server that is a portal to our web server.

  • Thanks for reading my post and replying.

    It is SQL Server 2008 R2 running on Windows Server 2008R2.

    It seems weird that SSMS "fixes" the problem but 3 times in a row it was locked up for several hours and immediately was "fixed" immediately after I expanded the list of tables on the database. Might be magic or just Windows:-)

  • Next time, instead of expanding the stuff out, open a query window and look at sys.dm_exec_requests to see what is blocked, blocking, etc., what the wait stats are, that sort of thing. I'd be very curious what this might be.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 8 posts - 1 through 8 (of 8 total)

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