August 20, 2012 at 5:21 am
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
SELECT *
FROM sys.database_files
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, *
FROM sys.master_files
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?
DECLARE
@Log SMALLINT,
@DBSYSNAME,
@ROBIT;
SELECT @Log = FILE_ID,
@DB = Name,
@RO = Is_Read_Only
FROM sys.database_files
WHERE type_desc = ''Log'';
IF( @RO = 0 ) -- Doesn't work - Msg 7992, Level 16, State 2, Line 18 - Cannot shrink 'read only' database 'MyDatabase'.
BEGIN
DBCC SHRINKFILE( @Log, TRUNCATEONLY );
END
August 20, 2012 at 5:41 am
Not sure I understand this. Can you not just query sys.databases?
John
August 20, 2012 at 5:44 am
Ok - so I threw together below - which works around it. It seems like the Is_Read_Only setting is a "bug" depending upon where you look?
DECLARE
@LogSMALLINT,
@DBSYSNAME,
@Is_Read_OnlyBIT,
@FalseBIT= 0;
SELECT
@Is_Read_Only = SD.is_read_only,
@Log = DF.FILE_ID,
@DB = DF.Name
FROM sys.database_files AS DF
INNER JOIN sys.master_files AS mf ON DF.physical_name = MF.physical_name
INNER JOIN sys.databases AS SD ON mf.database_id = SD.database_id
WHERE DF.type_desc = ''Log'';
IF( @Is_Read_Only = @False )
BEGIN
DBCC SHRINKFILE( @Log, TRUNCATEONLY );
END
August 20, 2012 at 5:47 am
John,
I need the File_ID to pass to DBCC SHRINKFILE - but I'm all ears in terms of suggestions.
SELECT @Log = FILE_ID,
@DB = Name
FROM sys.database_files
WHERE type_desc = ''Log'';
DBCC SHRINKFILE( @Log, TRUNCATEONLY );
Thanks,
Doug
August 20, 2012 at 5:49 am
The database as a whole can be read-only (sys.databases) or individual files can be read-only.
Aside, why do you have a job shrinking the log? On a dev system I could understand, but on production that's just harmful.
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
August 20, 2012 at 5:54 am
Gail's point about shrinking the log was going to be my next question. But if you have a good reason for doing this, you need to check that the database is read-write and that each individual file you're going to shrink is read-write. Have a go at writing the logic for that and post back if you're struggling.
John
August 20, 2012 at 6:28 am
John Mitchell-245523 (8/20/2012)
But if you have a good reason for doing this, you need to check that the database is read-write and that each individual file you're going to shrink is read-write.
But note that you cannot have a log that's read-only on a database that's read-write. It's only data files that can individually be read-only (well filegroups actually)
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
August 20, 2012 at 6:43 am
Didn't realise that, but when I think about it, it makes sense. Thanks for clarifying, Gail.
John
August 20, 2012 at 9:24 am
Gail,
'Prod' system for Finance what-if scenarios - which we rebuild every morning from the previous day's 'true' prod.
They run all kinds of queries against the old data - so when we go to restore yesterday's data - we first clear out yesterday's log in case we have disk space issues.
Don't lose sight of the actual question 😀
Thanks,
Doug
August 21, 2012 at 1:47 am
Doug
Are you saying you clear out the log before you do the restore, or after? There's no point in doing so before, since the restore operation will plonk an exact copy of your "true prod" database in place of whatever you have there already (assuming you restore the database with the same name as the existing one). Do you use this server for anything else? If not, there's not much point in shrinking the log afterwards, either, since it's not going to grow if you're only using it to query. If you do go ahead and shrink the database, I would recommend you do so immediately after the restore, and then you don't need to do the read-only check.
John
Edit - slightly revised my advice having re-read your original post
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply