Database error when tried to open

Question

Database error when tried to open

anjan.ashok

SSCrazy

Points: 2783
More actions
July 2, 2008 at 5:38 am

#187783

Hi,
When i tried to open the database its giving the following error.
database 'MY_DB' can not be opened due to inaccessible files or insufficient memory or disk space. (Microsoft SQL Server, error : 945)
Their is enough disk space also.
Pls tell me wat do asap.

Viewing 14 posts - 1 through 13 (of 13 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Are all the files where SQL expects them to be? Query sys.master_files and see where SQL thinks the data files should be. Make sure they are 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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 2

Have you delete database files???

Check whether sufficient disk space is available in the drive where the database files resides.

anjan.ashok SSCrazy Points: 2783 More actions · Answer 3

All the sql related files are there and space is also there.

and one more error "Status= Recovery Pending "

when i ran the query

select * from sysdatabases

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Is the log file where SQL expects it to be? That error indicates that the log fle couldn't be read.

Have a look in the SQL error log, see if you can find any references to the log file of that database.

Are any of the drives/folders compressed, encrypted or readonly?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MannySingh SSChampion Points: 12465 More actions · Answer 5

Which Database has Status= Recovery Pending?

is it the system Database or the User Database.

This issues relates to a Database not Shut Down properly or a transaction Log active and uncommitted when the Database was shut Down...

Was there anything that happened, before this error, you want to tell us? Like a server shutdown situation.

ALTER DATABASE [databasename] SET EMERGENCY Or

ALTER DATABASE [database name ]SET SINGLE_USER

DBCC CHECKDB (database name, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS

ALTER DATABASE [database name] SET MULTI_USER;

Use [databaes name]

DBCC CHECKDB [database name]

Use this to repaid the damaged Trans Log etc..

Let me know how this works, out ...

Maninder
www.dbanation.com

anjan.ashok SSCrazy Points: 2783 More actions · Answer 6

Recovery pending error is for user database only.

User db is not detecting at all.

Yesterday there was server shutdown and restarted by admin people.

so wat can be the reason

Log files are there.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Are there any errors in the SQL error log?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

anjan.ashok SSCrazy Points: 2783 More actions · Answer 8

gila,

recent error is

Operating system error 32(The process cannot access the file because used by another proccess),

occurred while creating or opening file 'G:\mydb.ldf'

tell me wat to do

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Ah. Now we get somewhere.

Do you have an anitvirus that may be (or may have been) scanning the file? Do you perhaps have a file backup that may be (or may have been) copying the file?

Try restarting the SQL service now, and see if it comes right. Basically what it's saying is that when SQL tried to start, some other process was reading the log file and SQL could not open it. It may be a transitive error.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

anjan.ashok SSCrazy Points: 2783 More actions · Answer 10

I restarted the sql service by still it was giving error.

For time being i can restore the database.

so any idea how to overcome this.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Try downloading process explorer and see if you can identify what is using that file. If you can find out and you can remove that open file handle, you should be able to get the database online.

You can find process explorer here:

http://technet.microsoft.com/en-us/sysinternals/default.aspx

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

JeremyE SSCoach Points: 16199 More actions · Answer 12

I think Gail has provided the correct answer. I have seen this happen when anti-virus software is scanning the data files. You can stop the anti-virus service, restart the SQL service and then start the anti-virus service.

You should also consider adding the folders where the data and log files reside to the "excludes" location so that the anti-virus software does not scan them.

sqlservercenter-793614 SSC-Addicted Points: 463 More actions · Answer 13

Have you ever setup default database in your login in security configuration

[font="Arial"]MCDBA, MCITP (DB Dev, DB Admin), MCSE,MCTS, OCA 10g[/font]