We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

SQL Server 2005 database in Read-only state= True, can't set to False

jmoss111
jmoss111 asked
on
Medium Priority
807 Views
Last Modified: 2012-06-27
I have a SQL Server 2005 database moved from one machine to another and when moved Read-only = True; I can't set Read-Only to false via the interface, or by using either of the below:
 ALTER DATABASE ASSCAP SET READ_WRITE,
or
EXEC sp_dboption 'ASSCAP', 'read only', false

Thanks in advance for any assistance.

Jim
Comment
Watch Question

Commented:
run 'sp_helpdb ASSCAP'

Can you copy/paste in the status and compatability level

Cheers

Author

Commented:
copy/paste wont pull over from rdp:

status  = online, updatability = read_only, useraccess=multi_user, recovery = full
compatibility =90

Commented:
Sidenote: It will if under 'Local Resources' you click on 'Clipboard'.

Anyway....

What if you try:

ALTER DATABASE ASSCAP SET READ_WRITE WITH ROLLBACK_IMMEDIATE

??

If that fails, run "sp_who2" and see who's in that DB, kick them out and try again. Other suggestions, check the physical files to ensure the account running SQL has full permission to them.

HTH

Author

Commented:
the ALTER  gave me incorrect syntax near ROLLBACK_IMMEDIATE. User was kicked out, my account is dbo

Commented:
Doh. Try taking out the _

File permissions all OK, No-one in the database, including you ?

Author

Commented:
correctamundo

Commented:
What error does it give when it fails?

Author

Commented:
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "D:\SQL2005\ASSCAP.mdf". Operating system error 5: "5(Access is denied.)".
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "D:\SQL2005\ASSCAP_log.ldf". Operating system error 5: "5(Access is denied.)".
File activation failure. The physical file name "D:\SQL2005\ASSCAP_log.ldf" may be incorrect.
Msg 945, Level 14, State 2, Line 1
Database 'ASSCAP' 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.

Author

Commented:
I can attach and detach on the other machine at will no problem, and no space problem

Commented:
>>Msg 5120, Level 16, State 101, Line 1
>>Unable to open the physical file "D:\SQL2005\ASSCAP.mdf". Operating system error 5: "5(Access is denied.)".

I'm still thinking physical file security and/or the file is marked as read only.

Author

Commented:
files not set to read only. i'm dbo...
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks for hanging with me on this.

Commented:
No Worries :o)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.