0

I have moved some data from one file group to another, however when I check the drive with master.sys.xp_fixeddrives the drive does not say that it has been altered in size.

I want to know how to check what drive the filegroup is physically stored on.

Thanks

Charles Bryant
  • 995
  • 2
  • 18
  • 30
  • How did you "move" the data? What specific steps did you take? – Dave Mason Jun 25 '14 at 13:39
  • I created an index with drop, I have been told that I may need to shrink the file group, and found this link http://stackoverflow.com/questions/5315020/reclaim-space-after-moving-indexes-to-file-group – Charles Bryant Jun 25 '14 at 14:12
  • That sounds like a good place to start. In SSMS, you can right-click on a db and select Tasks | Shrink | Files. From there, you can check the available free space in the log and data file(s). – Dave Mason Jun 25 '14 at 14:21

1 Answers1

0

Filegroups are made up of a collection of files, all of which could be on separate drives, partitions, mount points, smb shares, etc. The filegroup doesn't exist anywhere but logically, you want to know where the file inside of the filegroup reside.

Below can be run to give you the information you are looking for in terms of where the files are in that filegroup. Optionally you can omit the where clause to see all of the files and filegroups, where the files reside. It will need to be run in the context of the database in question.

SELECT f.name AS [FGName], df.name AS [FileName], LEFT(df.physical_name, 1) AS [Drive]
    , df.physical_name AS [Full_Path]
FROM sys.database_files df 
    inner join sys.filegroups f 
        on df.data_space_id = f.data_space_id
WHERE f.name = 'MyFilegroupNameHere'