May 14, 2012 at 7:43 pm
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...
May 15, 2012 at 12:18 am
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
May 15, 2012 at 10:53 am
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.
May 15, 2012 at 11:02 am
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.
May 15, 2012 at 11:03 am
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
May 15, 2012 at 11:23 am
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.
May 16, 2012 at 2:23 pm
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:
May 16, 2012 at 4:02 pm
SQL_ME_RICH (5/16/2012)
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
January 13, 2014 at 8:20 am
<<Deleted Post>>
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply