Working with Filegroups

Adding a file and filegroup to a database

USE [master]
GO
ALTER DATABASE [DataArchive] 
ADD FILEGROUP [Archive_2015]
GO
ALTER DATABASE [DataArchive] 
ADD FILE ( NAME = N'DataArchive_2015', 
FILENAME = N'G:\DataArchive_2015.ndf' , SIZE = 104448KB , FILEGROWTH = 1048576KB ) 
TO FILEGROUP [Archive_2015]
GO

Making a filegroup default for new data

--Make a filegroup default
ALTER DATABASE core_AdvertDataArchive
MODIFY FILEGROUP Archive_2011 DEFAULT

Checking which tables are located in which filegroups

--Return which filegroup a table is located in
SELECT object_name(i.[object_id]) as Name_of_Object,
i.name as Index_Name,
i.type_desc as Index_Type,
f.name as Name_of_Filegroup,
a.type as Object_Type,
f.type,
f.type_desc
FROM sys.filegroups as f 
INNER JOIN sys.indexes as i 
 ON f.data_space_id = i.data_space_id
INNER JOIN sys.all_objects as a 
 ON i.object_id = a.object_id
WHERE a.type ='U' -- User defined tables only
--AND object_name(i.[object_id]) ='employee' -- Specific object
GO
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: