I know the database in question should not be setup this way - one of the Finance guys copied a DB and made it read-only on a production box.
I had one of my jobs blow up because I was attempting to shrink a database marked read only even though the log is not marked read-only.
If I run this query - I see the database is marked read-only - Is_Read_Only = 1
SELECT SD.Name, SD.Is_Read_Only, *
FROM sys.databases AS SD
WHERE SD.Name = 'MyDatabase'
If I look at the log for the database - the log is not read-only - Is_Read_Only = 0
WHERE type_desc = 'Log';
To further complicate matters, if I run this - it says both the data and the log are not read-only ( Is this a bug? SQL 2008 R2 SP2 )
SELECT name, physical_name AS current_file_location, *
So - bottom line - how can I tell if a database is marked read-only so I do not throw an error when I try to shrink the log? I do not see a good join to determine this?
SELECT @Log = FILE_ID,
@DB = Name,
@RO = Is_Read_Only
WHERE type_desc = ''Log'';
IF( @RO = 0 ) -- Doesn't work - Msg 7992, Level 16, State 2, Line 18 - Cannot shrink 'read only' database 'MyDatabase'.
DBCC SHRINKFILE( @Log, TRUNCATEONLY );