September 4, 2014 at 8:26 am
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
September 4, 2014 at 8:58 am
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.
September 4, 2014 at 9:48 am
Thanks for the info but the auto close is set to false so I'm still looking for something else.
September 4, 2014 at 11:19 am
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?
September 4, 2014 at 12:27 pm
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
September 4, 2014 at 12:42 pm
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.
September 4, 2014 at 12:46 pm
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:-)
September 4, 2014 at 12:48 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy