April 17, 2015 at 9:46 am
I've got a feeling I must have missed something here.
We've currently in the process of migrating a largeish amount of data into a new system. We've got a lot of historical data which we must keep, but cannot be changed, and current information which can be for a given period. So the dB's partitioned by time period.
The cunning plan was to set non-current (for want of a better term) partitions read only, with only current read-write (and possibly a little performance boost while accessing the old info). Then I tested my consistency check routine. And, yes - for those of you in the know, it did indeed go <THUD>.
Apparently, DBCC CHECK ... does not run on a database with any partition(s) readonly.
But, hooray, you can snapshot a database and check that. All 2 Tb of it, why thank you for that, not helpful.
But maybe I can change the status, CHECK then change back on the readonly filegroups. Nope, to do that I have to grab exclusive access - chucking all the users out of our 24-7-365 highly sensitive system. Then do it again when I'm finished, or leave them locked out while I check 2Tb of data. The politics of that (and, frankly the reality for those on the front line), I wouldn't be able to get as far as making the suggestion, not happening.
Am I missing a trick, or if you have partitioning you have to choose EITHER having readonly partitions OR being able to run integrity checks? I must be missing something, surely, because that's just ridiculous.
Anyone care to make me feel foolish by pointing out an obvious solution?
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
April 17, 2015 at 10:09 am
andrew gothard (4/17/2015)
But, hooray, you can snapshot a database and check that. All 2 Tb of it, why thank you for that, not helpful.
I'm not sure why that's not helpful, since CheckDB's normal operating mechanism is to create a hidden database snapshot and run against that snapshot. Manually creating a snapshot will just save SQL the effort of creating one as part of the CheckDB process.
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
April 20, 2015 at 8:58 am
GilaMonster (4/17/2015)
andrew gothard (4/17/2015)
But, hooray, you can snapshot a database and check that. All 2 Tb of it, why thank you for that, not helpful.I'm not sure why that's not helpful, since CheckDB's normal operating mechanism is to create a hidden database snapshot and run against that snapshot. Manually creating a snapshot will just save SQL the effort of creating one as part of the CheckDB process.
Absolutely, I get that.
In this case, because the aim of the partitioning here is largely to allow things like CHECK XX, maintenance, backups etc into managable chunks (as well as prevent any changes to the non-active data). As I understand it, and it appears to be backed up by observation, when you CHECK an 80 Gb it snapshots that and not the entire database - although I've not been able to find anything definitive on this. Not that I've ever tried it, but I'd expect snapshotting an active 2Tb database could take a real bite out of my maintenance window.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
April 20, 2015 at 9:13 am
andrew gothard (4/20/2015)
Not that I've ever tried it, but I'd expect snapshotting an active 2Tb database could take a real bite out of my maintenance window.
Shouldn't do. If there are long running transactions maybe, those have to be rolled back in the context of the snapshot, but otherwise no. As I mentioned, CheckDB already takes a database snapshot to run against, I don't know if it includes all filegroups or leaves out read only ones, not something I've investigated.
Since you have filegroups and partitions, have you considered doing partial restores to a secondary server and checkDB that? If you're already backing up at the filegroup level then that should fit well into the routine. You'll still need to do the DBCC CheckCatalog on the full database, but that's a rather fast part of CheckDB
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
April 20, 2015 at 12:38 pm
GilaMonster (4/20/2015)
andrew gothard (4/20/2015)
Not that I've ever tried it, but I'd expect snapshotting an active 2Tb database could take a real bite out of my maintenance window.Shouldn't do. If there are long running transactions maybe, those have to be rolled back in the context of the snapshot, but otherwise no. As I mentioned, CheckDB already takes a database snapshot to run against, I don't know if it includes all filegroups or leaves out read only ones, not something I've investigated.
Since you have filegroups and partitions, have you considered doing partial restores to a secondary server and checkDB that? If you're already backing up at the filegroup level then that should fit well into the routine. You'll still need to do the DBCC CheckCatalog on the full database, but that's a rather fast part of CheckDB
It's worth considering. We are going to have a standby on our other site for HA, but we'd need to talk to our licencing bod to see if that would mean licencing that too. I find it unlikely - I mean who wouldn't check a critical component of a core system. Some colleagues are unconvinced.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
April 21, 2015 at 2:26 am
andrew gothard (4/20/2015)
It's worth considering. We are going to have a standby on our other site for HA, but we'd need to talk to our licencing bod to see if that would mean licencing that too. I find it unlikely - I mean who wouldn't check a critical component of a core system. Some colleagues are unconvinced.
If you're talking something like Mirroring or Always On, you can't checkDB the secondary to get any indication of the state of the primary server. That's why I said restore backups.
Have you tried taking the snapshot and CheckDB that? Even with a 24/7 system, there should be a quieter period of time where you can take that snapshot and see if that will let you run the CheckDB against it.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply