Filegroup status

Question

Filegroup status

NicHopper

SSCrazy Eights

Points: 9090
More actions
September 3, 2017 at 4:21 am

#325125

Hi,
Does anyone know a way to check a filegroups status to see if it has been restored and is online.
We have a dataase with 40 + file groups, the majority of which are read only. In DR test we restore the RW FG's first then move on to the read only ones. I wondered if there is somewhere I can list out the FG and show which are online (have been restored and which have not)?
sys.filegroups only lists them, it doesn't state their status.
Thanks,
Nic
Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 5 posts - 1 through 4 (of 4 total)

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

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

Check out sys.master_files. The data_space_id is the number for the filegroup. You'll also find a status in the "state" and "state_desc" columns. There is one entry for every file in the system and every file is a member of a filegroup. Obviously, there can be more than one file per filegroup.

--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)

Johan Bijnens SSC Guru Points: 135377 More actions · Answer 2

This will work when performing piecemeal restores (after primary FG has been restored):

Select ... from sys.data_spaces DS inner join sys.database_files F on F.data_space_id = DS.data_space_id -- where DS.type in ( 'FG', 'FX') order by DS.name, F.file_id

When performing a regular restore, the full db stays unavailable until the last (log) restores are processing their rollback operations (at best)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

NicHopper SSCrazy Eights Points: 9090 More actions · Answer 3

Hi,
Thanks for the table name, that worked a treat. For others this is how I do it;

SELECT

FG.name,FG.type,FG.type_desc,FG.is_read_only,filegroup_guid,MF.state_desc
FROM sys.filegroups FG

LEFT JOIN sys.master_files MF ON FG.data_space_id = MF.data_space_id

Thanks for the help and thank you everyone for reading.
Nic

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

NicHopper - Monday, September 4, 2017 2:55 AM
Hi,
Thanks for the table name, that worked a treat. For others this is how I do it;
SELECT
FG.name,FG.type,FG.type_desc,FG.is_read_only,filegroup_guid,MF.state_desc
FROM sys.filegroups FG
LEFT JOIN sys.master_files MF ON FG.data_space_id = MF.data_space_id
Thanks for the help and thank you everyone for reading.
Nic

Excellent. Thanks for the feedback and the code that you ended up using.

--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)