7

The title is clear enough, I created a new Filegroup "ArchiveFileGroup":

ALTER DATABASE MyDataBase
ADD FILEGROUP ArchiveFileGroup;
GO

I want to create a table called : arc_myTable in order to store old data from this one : myTable

I used the following query :

CREATE TABLE [dbo].acr_myTable(
    [Id] [bigint] NOT NULL,
    [label] [nvarchar](max) NOT NULL,
)on ArchiveFileGroup

I'm not sure if it's the right way, I don't know where the FileGroup is created to check if it contains the table.

GSDa
  • 193
  • 2
  • 4
  • 21
  • 1
    If you're using SSMS, it will show the filegroup of a table. In the object explorer table properties, select "storage". – Jim H. Jun 06 '14 at 17:44

4 Answers4

7

You can easily check with this sql query:

SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]
FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id]
WHERE i.data_space_id = f.data_space_id
AND o.type = 'U' -- User Created Tables
GO

Just add: 

AND f.name = ArchiveFileGroup

to see everything in your new filegroup or:

AND o.name = acr_myTable

to see where your table is located.

If you never added a file to your filegroup, then I would expect an error but you didn't include either an error message or anything saying you did create a file. If you did not, I suggest starting at the microsoft documentation if needed.

The OP found the this helpful trying to create a new file in his filegroup.

Vulcronos
  • 3,428
  • 3
  • 16
  • 24
  • Indeed when I checked the properties of the FILEGROUP , it had 0Mb no file was added. I thought when the FILEGROUP was created the file is added by default. I have no idea how to add this file. I'll check the documentation. Thank you! – GSDa Jun 06 '14 at 18:07
  • @GSDa, are you sure? cause per your post I see you already created a table on that file group. BTW, is it a empty table? if yes, then it may be. – Rahul Jun 06 '14 at 18:12
  • @Rahul Yes, you're right it was empty, I wasn't able to add a row. the error said that the filgroup has no file added. – GSDa Jun 06 '14 at 18:14
  • @GSDa, You may want to post that as separate question (if you want). – Rahul Jun 06 '14 at 18:16
  • I found a solution, It may help others here's how to add a file to an existing FILEGROUP http://forums.asp.net/t/1397838.aspx?The+filegroup+comn_data+has+no+files+assigned+to+it+sql – GSDa Jun 06 '14 at 18:32
2

You can use sys.filegroups to see all the created file groups in your server like

SELECT *
FROM sys.filegroups

See here for more information List All Objects Created on All Filegroups

Rahul
  • 76,197
  • 13
  • 71
  • 125
1

It is old post. Want to add information, it might help somebody in future.

sp_help <table_name>

You can see the filegroup, on which the table is created.

Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
0

NB. You can check by Right-clicking on the table then select properties. on the storage you can see to which the filegroup the new table is belonging

In your case: It will create the table on the default filegroup, not on the new filegroup you created. A filegroup is logical and used to create a secondary file. ex. if you need to create the table on a different location than the default drive, you have to define fileName for the new filegroup.

ALTER DATABASE [db] ADD FILEGROUP [NewFileGroup]

ALTER DATABASE [db] ADD FILE ( NAME = N'NewFile', FILENAME = N':D\..\Newfile.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [NewFileGroup]

GO

user11256637
  • 1
  • 1