Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Determine if a database is read-only when the log is not marked as read-only so DBCC SHRINKFILE may be skipped? Expand / Collapse
Author
Message
Posted Monday, August 20, 2012 5:21 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 7, 2014 8:37 AM
Points: 87, Visits: 244
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,
@DB SYSNAME,
@RO BIT;

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

Post #1347095
Posted Monday, August 20, 2012 5:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:50 AM
Points: 5,438, Visits: 10,135
Not sure I understand this. Can you not just query sys.databases?

John
Post #1347098
Posted Monday, August 20, 2012 5:44 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 7, 2014 8:37 AM
Points: 87, Visits: 244
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
@Log SMALLINT,
@DB SYSNAME,
@Is_Read_Only BIT,
@False BIT = 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

Post #1347099
Posted Monday, August 20, 2012 5:47 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 7, 2014 8:37 AM
Points: 87, Visits: 244
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
Post #1347102
Posted Monday, August 20, 2012 5:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:11 AM
Points: 40,436, Visits: 36,888
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 2008, MVP
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

Post #1347103
Posted Monday, August 20, 2012 5:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:50 AM
Points: 5,438, Visits: 10,135
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
Post #1347106
Posted Monday, August 20, 2012 6:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:11 AM
Points: 40,436, Visits: 36,888
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 2008, MVP
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

Post #1347129
Posted Monday, August 20, 2012 6:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:50 AM
Points: 5,438, Visits: 10,135
Didn't realise that, but when I think about it, it makes sense. Thanks for clarifying, Gail.

John
Post #1347136
Posted Monday, August 20, 2012 9:24 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 7, 2014 8:37 AM
Points: 87, Visits: 244
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
Post #1347278
Posted Tuesday, August 21, 2012 1:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:50 AM
Points: 5,438, Visits: 10,135
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
Post #1347615
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse