SQL Database misteriously read-only

Have this ever happened to you? After moving a .mdf around, you attach it with success and even make a few queries, but after some hours the database is marked as “read-only”. The SQL Server Log won’t give you a clue, then you go ahead and try to make it online fast (investigation on WHY can wait a few moments, the users can’t).

USE [master]
GO
ALTER DATABASE [your_base] SET  READ_WRITE WITH NO_WAIT
GO

But to no avail. The database stays read-only and now the message is:

Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file "F:\your_base.mdf". Operating system error 5: "5(Access is denied.)". Msg 945, Level 14, State 2, Line 1 Database 'your_base' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.

Well, this is something we can use to work. Check your disk to see if the file is there and that there is enough space on it, then go ahead and check if the account used to start the SQL Service has the necessary rights on the folder/file. This usually happens when you change the SQL Service account from “Local system” to any other domain/local account and move a database file to a folder where this account doesn’t have write access.

When you try to make the database online SQL Server Log will give a better message:

FCB::Open failed: Could not open file F:\your_base.mdf for file number 1.  OS error: 5(Access is denied.).

and a quick search on google will point you to the right direction (but you dont need to restart the service, just make the database read-write again or detach/attach it).

cya!

Advertisements

About mauriciorpp

Hi, I am Piccolo - but not the one from Dragon Ball. I'm from a highly competitive and fast-paced world too, the IT industry, and this space will be used to share some challenges I face daily on my career. As you will see, I don't do brawls but I need to be a fighter! Stay tuned.
This entry was posted in SQLServer and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s