SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DBCC CHECKDB with READONLY Filegroup(s)


DBCC CHECKDB with READONLY Filegroup(s)

Author
Message
SQL_ME_RICH
SQL_ME_RICH
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2532 Visits: 1596
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...
GilaMonster
GilaMonster
SSC Guru
SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)

Group: General Forum Members
Points: 234546 Visits: 46373
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, MVP, M.Sc (Comp Sci)
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


SQL_ME_RICH
SQL_ME_RICH
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2532 Visits: 1596
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.
SQL_ME_RICH
SQL_ME_RICH
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2532 Visits: 1596
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)

Group: General Forum Members
Points: 234546 Visits: 46373
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, MVP, M.Sc (Comp Sci)
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


SQL_ME_RICH
SQL_ME_RICH
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2532 Visits: 1596
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.
SQL_ME_RICH
SQL_ME_RICH
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2532 Visits: 1596
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!

w00t
GilaMonster
GilaMonster
SSC Guru
SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)

Group: General Forum Members
Points: 234546 Visits: 46373
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, MVP, M.Sc (Comp Sci)
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


EwanCourtney
EwanCourtney
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 80
<<Deleted Post>>



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search