November 13, 2007 at 9:09 am
Server : SQL Server 2005
Database: new_db
I got the error when I try to see properties of database 'new_db'
error message as follows,
"Database 'new_db' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details "
I checked for memory/access right/disk space , everything seems fine and then executed the below command to know about .mdf and .ldf files
Use master
go
SELECT name, physical_name
FROM sys.master_files
where database_id = db_id('new_db')
and also there is an error in the error log as
"Unable to open the physical file "e:\MSSQL_dataew_db_data1.ndf". Operating system error 32: "32(error not found)"
I suspect database is corrupt but database is not showing any staus eg:suspect.
What action do i need to take ? please help me in resolving this issue as it is a production database
Many thanks in advance.
Menaka
November 13, 2007 at 9:42 am
Does the file "e:\MSSQL_dataew_db_data1.ndf" still exist? Did it ever to your knowledge?
You could try doing a backup and restore to another DB Name just to see if it works. You could also try detaching and reattaching the DB. If however you you legitimately should have had a .ndf and its been deleted or is seriously corrupted you may have to try rebuilding your DB from scratch. Use SSIS and copy all of the objects & data into a new DB. Of course anything that existed in the NDF may be lost (assuming the file was deleted and is unrecoverable).
All of this is of course assuming that restoring from a clean backup is not an option.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
November 13, 2007 at 11:13 pm
Check if the file location path is right. it lloks like the ndf file is missing. OS error 32 corresponds to file not found error.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
November 14, 2007 at 5:01 am
no... files are not missing, below is the query I used to check for files and even checked in the drives files are existing.
use master
go
SELECT name, physical_name
FROM sys.master_files
where database_id = db_id('new_db')
new_db_data e:\MSSQL_dataew_db_data1.ndf
new_db_logd:\MSSQL_dataew_db_log1.ldf
new_db_index1f:\MSSQL_index_WINCOLew_db_Index1.ndf
and I'm not able to attach and detach as it gives the same error as I mentioned in my first post
November 14, 2007 at 6:43 pm
Check to see that the service account SQL Server is running under has permissions to modify those files (you'll have to check the files individually). You should see the group SQLServer2005MSSQLUser (or something like that if it's a named instance) with Full Control.
K. Brian Kelley
@kbriankelley
November 15, 2007 at 3:57 am
Thank you all for the inputs I tried with detach and attach...while attaching I got the error like 'e:ew-db.ndf' file is missing
Thanks again for the help.
Regards,
Menaka
November 15, 2007 at 8:28 am
Menaka (11/15/2007)
Thank you all for the inputs I tried with detach and attach...while attaching I got the error like 'e:ew-db.ndf' file is missing
That either means the file no longer exists in the file system OR the SQL Server service account no longer has access to it. Do you have a server administrator who could check on this for you?
K. Brian Kelley
@kbriankelley
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply