Can't log into a database without re-attaching

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Can't log into a database without re-attaching

Author

Topic

CraigYoungman
Starting Member

6 Posts

Posted - 2014-10-16 : 10:43:29

There are several databases on an SQL2008R2 server. But only one is used normally. The others are for testing.

The database is used in a Point of Sale application every day. Sometimes in the morning the application can not log into the database. They can log into one of the other testing databases.

If I use Microsoft SQL Server Management Studio The database folder is there, but no [+] sign and you can't view any tables, views, etc. If I Detach the database then Attach the database everything works.

It makes no difference if I use SQL Server Authentication or Windows Authentication.

Any ideas on what causes this or how to fix it?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-16 : 11:33:32

Run this query and examine the row for the database in question to see if there is anything unusual. Is it online, is it in multi_user mode etc.

USE master;
GO
SELECT * FROM sys.databases

Also, examine the agent jobs to see if it is doing something that requires the database or tables to be locked - off-line index rebuild for example.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-16 : 12:35:38

Check the SQL Server Error Log and Application Log in Event Viewer. There should be information in there that should help figure this out. Please let us know what it says.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

CraigYoungman
Starting Member

6 Posts

Posted - 2014-10-16 : 14:21:24

This Server is at a different location but I will try both suggestions and post the results.

Thanks,
Craig.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-16 : 14:26:03

I never am at the same location as my servers. I just RDP to them. I haven't even seen a server in a few years.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources