Partitioned table

Question

Partitioned table

NicHopper

SSCrazy Eights

Points: 9090
More actions
January 3, 2014 at 6:43 am

#396053

Hi,
I have a table which is spread over a number of partitions.
Assuming a disaster happens I can quickly restore the most recent partition and everyone can get back to things whilst I work on restoring the other partitions.
My question is this, does anyone know how for a table/database to determine which partitions are accessible and which are not?
Thanks,
Nic
Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 1

NicHopper (1/3/2014)
Hi,
I have a table which is spread over a number of partitions.
Assuming a disaster happens I can quickly restore the most recent partition and everyone can get back to things whilst I work on restoring the other partitions.
My question is this, does anyone know how for a table/database to determine which partitions are accessible and which are not?
Thanks,
Nic

Are your partitions spread over different File Groups?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

NicHopper SSCrazy Eights Points: 9090 More actions · Answer 2

Hi Jeff,

Thanks for the reply.

Each partition sits on it's own filegroup, so in essence I just need a method to determine if the partition or the filegroup is online.

Any help would be appreciated.

Thanks,

Nic

Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 3

sys.Filegroups may be the simple key for you then. If the filegroup has been "recovered", it'll appear there. It also has a "read only" flag, just in case you ever need such a thing.

I've built a pretty sophisticated view for partition information at work. Let me "sanitize" it for the public and I'll try to remember to post it on this thread. In the meantime, see if sys.FileGroups does what you need in the short haul.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

NicHopper SSCrazy Eights Points: 9090 More actions · Answer 4

Hi Jeff,

The behaviour is a little different on my development instance (SQL 2012 SP1).

In that instance I have a database with 5 file groups;

PRIMARY - Primary file group.

FSPre2014 - holds filestream data for records before 2014.

MDPre2014 - holds meta data for records before 2014.

FS20140101 - holds filestream data for records on or after 2014.

MD20140101 - holds meta data for records on or after 2014.

I have a full back which has 4 of the writeable partitions (all but the 'FSPre2014 '). When I restore this backup however I can then see all 5 filegroups, if I try to run a query (for the pre 2014 data) on the partitioned table I get and error as expected;

"Large object (LOB) data for table "dbo.DocStoreTestingTable" resides on an offline filegroup ("FSPre2014") that cannot be accessed."

I can understand the error and that is fine however I would have expected it to only show the 4 file groups that had been restored, not sure if I'm doing some wrong or this is the intended behaviour?

Thanks,

Nic

Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

NicHopper SSCrazy Eights Points: 9090 More actions · Answer 5

Hi,

I managed to get this done by using sys.database_files.

In my scenario each partition was represented by 1 file group so in the table it shows them as 'RECOVERY_PENDING' if they have not been restored.

Thanks for your help Jeff, it's appreciated.

Nic

Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/