August 19, 2009 at 6:28 am
My application throws a runtime error and went ahead the DB server and found that none of the folders(Database Diagrams, Tables, Views, Synonyms, Programmability..) available under DB and I saw only DB name in the object explorer window. Can any one assist me on what was the issue for occurring this?
August 19, 2009 at 6:53 am
Sounds like your DB is not in an online state... Have you looked at the SQL Server error logs? Do you have working backups of your DBs?
Run this select [name], user_access_desc, state_desc from sys.databases, user_access_desc should be MULTI_USER and state_desc ONLINE.
August 19, 2009 at 7:30 am
Sounds like the DB is suspect or recovery-pending.
Did SQL restart just before this happened?
Any errors relating to this DB in the SQL error log?
What happens if in a query window you run this?
USE <Database Name>
GO
It'll likely throw an error, I'm interested to know what the error msg is.
Do you have up-to-date backups and what's your downtime allowance for this DB?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 19, 2009 at 7:33 am
I was re-attached the database using their MDF file. I want to know the reason why this was happened? On what cause the problem?
Thx.
August 19, 2009 at 7:43 am
What happened to the log files? You need those for a reattach as well.
There's no way that anyone can say what's happened or why without the information that we've asked for.
Database state?
Errors in error log?
Message that you get when trying to use the DB?
If you've deleted the transaction log file, read this - http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
p.s. Do you have an up-to-date backup of this database?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 19, 2009 at 12:16 pm
Database State:
----------------
I don't know the database state since I didn't check the database state before re-attaching the database.
Errors in Error log:
--------------------
I went ahead the erorr log and found the following logs befor login was failed when tried to connecting SQL server.
#) The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
#) Starting up database [dbname1]
#) Starting up database [dbname2]
#) Starting up database [dbname3]
#) Starting up database [dbname4]
#) Starting up database [dbname5]
(P.S: Thhe list of available databases starting up)
#)FCB::Open: Operating system error 3(The system cannot find the path specified.) occurred while creating or opening file 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DB.mdf'. Diagnose and correct the operating system error, and retry the operation.
#)Error: 17204, Severity: 16, State: 1.
#)FCB::Open failed: Could not open file E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DB.mdf for file number 1. OS error: 3(The system cannot find the path specified.).
#)Error: 5120, Severity: 16, State: 101.
#)Unable to open the physical file "E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DB.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
#)Error: 17207, Severity: 16, State: 1.
#)FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DB_log.ldf'. Diagnose and correct the operating system error, and retry the operation.
#)File activation failure. The physical file name "E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DB_log.ldf" may be incorrect.
#)The Service Broker protocol transport is disabled or not configured.
#)The Database Mirroring protocol transport is disabled or not configured.
#)Service Broker manager has started.
#)Error: 18456, Severity: 14, State: 16.
#)Login failed for user 'NT AUTHORITY\SYSTEM'. [CLIENT: ]
I don't have DB back up but I 've MDF & LDF file and using those files I was re-attached the database.
Thx.
August 19, 2009 at 2:00 pm
Whizkid (8/19/2009)
I don't have DB back up but I 've MDF & LDF file and using those files I was re-attached the database.
So do you still have a problem or is everything fixed?
From the errors, the mdf and ldf files was not where SQL expected them to be, hence it considered the database suspect. Without knowing exactly what happened before that error occurred, it's hard to say what caused it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2009 at 4:31 am
Everything fixed now using the MDF & LDF files.Is there any way to find what was happened before this db suspect?
Thx.
August 20, 2009 at 4:42 am
Whizkid (8/20/2009)
Is there any way to find what was happened before this db suspect?
From the error messages it shows that when SQL restarted the data and log files for the DB were not where they should have been. Means that while SQL was shut down either someone moved the files, the permissions on the folder were changed or something happened that the drive wasn't visible.
Basic root cause is that SQL used the path and filename stored in the system DB to find and open the DB, and the files were not there.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2009 at 5:50 am
Thanks for your reply.I have many databases in my server and all the DB MDF & LDF files pointed to the same drive (E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\). If there was a problem in the folder all the DB would have been deleted from Object explorer window, but this happened only for one database. Any idea?
Thx.
August 20, 2009 at 7:03 am
Either the files for that database were moved or the permissions on those files were changed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 21, 2009 at 5:01 am
No, the files were not moved. I was re-attached the MDF & LDF files from the actual location. And I believe the permission of the files were not changed because I'm not getting any permission error while re-attaching again.
Thx.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply