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

DBCC CHECKDB with READONLY Filegroup(s) Expand / Collapse
Author
Message
Posted Monday, May 14, 2012 7:43 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 6:00 PM
Points: 476, Visits: 1,328
Let me ask this of the experts...Microsoft says to make a snapshot of the database and run DBCC against that if you have a READONLY Filegroup and are attempting to check data integrity. Sounds a little odd to me, so I came up with the following...

ALTER DATABASE [FLEET-V3]
MODIFY FILEGROUP ROFG1 READWRITE;
GO

USE [FLEET-V3];
GO
DBCC CHECKDB

ALTER DATABASE [FLEET-V3]
MODIFY FILEGROUP ROFG1 READONLY;
GO

That is what is in my SQL Server Agent job for the database with the RO Filegroup in question. Is this a bad practice? If so - why, and please let me know if it will somehow cause any issues down the road.

Many thanks in advance...
Post #1299999
Posted Tuesday, May 15, 2012 12:18 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 @ 3:52 AM
Points: 42,467, Visits: 35,535
The first question is why you want to switch back to read-write? If you're planning on doing file/filegroup backups and relying on the fact that you won't need more than one restore for those read only files, than this switch to read-write and back to read-only will be messing that up quite nicely as the files are no longer assumed to be static from the time they were first made read-only

I have to ask, why does the MS advice sound 'odd' when what CheckDB does normally is take a database snapshot of the entire database and run against that?



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 #1300048
Posted Tuesday, May 15, 2012 10:53 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 6:00 PM
Points: 476, Visits: 1,328
It was more to the point of trying to get the DBCC command to run...I hadn't researched it enough Gail, but your points are valid.

As it turns out, there is no way to change the database state while other users are using the database, so the idea is definitely not going to work anyways.

The reason I thought it odd to make a complete snapshot of the db to then run DBCC against is that my database is large, and the time it would take to do a daily snapshot to do this with, just seemed like an off approach to me.
Post #1300422
Posted Tuesday, May 15, 2012 11:02 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 6:00 PM
Points: 476, Visits: 1,328
P.S. We are not doing Filegroup backups, but Fulls, Differentials, and TLogs. This Filegroup never did get defined and used so I am most likely going to be removing it all together.
Post #1300430
Posted Tuesday, May 15, 2012 11:03 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 @ 3:52 AM
Points: 42,467, Visits: 35,535
Snapshots are near-instantaneous, they take very, very little time to create and they only use space = amount of changes made during their existence.

As I mentioned, checkDB normally takes a snapshot of the database before it starts so that it can get a consistent view of the database without needing locks. So by creating one manually, you're just doing what checkDB woukld do automatically, no extra time, no extra storage space.



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 #1300431
Posted Tuesday, May 15, 2012 11:23 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 6:00 PM
Points: 476, Visits: 1,328
That makes better sense to me now that I am hearing you describe it.

Now I need to figure out a way to get rid of this RO Filegroup without having to kick everyone out of the database.
Post #1300449
Posted Wednesday, May 16, 2012 2:23 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 6:00 PM
Points: 476, Visits: 1,328
GilaMonster (5/15/2012)
Snapshots are near-instantaneous, they take very, very little time to create and they only use space = amount of changes made during their existence.

As I mentioned, checkDB normally takes a snapshot of the database before it starts so that it can get a consistent view of the database without needing locks. So by creating one manually, you're just doing what checkDB woukld do automatically, no extra time, no extra storage space.


Gail - I just read this on creating a database snapshot...

http://msdn.microsoft.com/en-us/library/ms175876(v=SQL.90).aspx

You describe it as the amount of changes made during their existence, but the information I am getting from MSDN is that based on the current size of the source database, I'll need to have sufficient disk space to hold the database snapshot to the maximum size of the source database at snapshot creation.

I guess I am wanting to know if I am going to need another 300+gb of disk space to create the snapshot, and then incremental amounts based on the original for every time I need to run DBCC CHECKDB?

If I have this correct (i.e. needing another 300+gb to do a snapshot for DBCC CHECKDB), then I am going to find a maintenance window to simply remove this RO FG, and be done with it, but if I am not understanding something correctly here - please help me get it right in my thick skull!

Post #1301354
Posted Wednesday, May 16, 2012 4:02 PM


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 @ 3:52 AM
Points: 42,467, Visits: 35,535
SQL_ME_RICH (5/16/2012)

[quote]I guess I am wanting to know if I am going to need another 300+gb of disk space to create the snapshot, and then incremental amounts based on the original for every time I need to run DBCC CHECKDB?


No, you do not. You need enough space to hold the changes made to the source database during the run of checkDB.

What MSDN says is:
The maximum size of a database snapshot is the size of the source database at snapshot creation.


Now, if you expect that during a run of CheckDB all 300GB of the source database will change, then the snapshot will reach it's maximum size of 300 GB and you will need all 300GB on disk.

If I have this correct (i.e. needing another 300+gb to do a snapshot for DBCC CHECKDB), then I am going to find a maintenance window to simply remove this RO FG, and be done with it


As I've said more than once, CheckDB automatically takes a database snapshot before running, exactly the same way that you create one. Hence if a snapshot did require the full size of the database (which is does not), changing the read-only filegroup would change nothing as CheckDB would still take the snapshot itself and would still require that 300 GB.

Snapshot sizes:
To store the copied original pages, the snapshot uses one or more sparse files. When a snapshot is taken, the sparse file takes up little disk space. As the database is updated over time, however, a sparse file can grow into a very large file.

http://msdn.microsoft.com/en-us/library/ms187054%28v=sql.90%29



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 #1301413
Posted Monday, January 13, 2014 8:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 18, 2014 3:30 AM
Points: 2, Visits: 80
<<Deleted Post>>


Post #1530310
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse