How to add a sql table to a different FILEGROUP

02 March,2016 by Jack Vamvas

Question: I'd like to add a table to a different FILEGROUP . The problem I've got is I don't have enough room on a drive and would like to add another file

in another filegroup, onto a new drive  .  What are the steps required?

Answer: This is a common problem, and can be achieved in three steps. A typical situation is where you've discovered there isn't enough disk space on the current drive and you have a temporary job - requiring some temp disk space.

In summary  these are the steps :

a)  Create a new filegroup
b) Add a file to the new FILEGROUP
c) Create the table  and associate to the new FILEGROUP

I'm assuming you've organised the disk space and completed some capacity planning.

ALTER DATABASE MyDatabase ADD FILEGROUP myfilegroup
GO
USE master
GO
ALTER DATABASE myDatabase ADD FILE 
( NAME = N'MyDB_temp', FILENAME = N'I:\MyDB_temp.ndf' ,
 SIZE = 5120KB , FILEGROWTH = 51200KB ) TO FILEGROUP myfilegroup
GO
use MyDatabase
GO
CREATE TABLE myTempTable(
	col1 ...
        col2 ....
) ON myFilegroup


 Read More

SQL Server – files and filegroups. Improving database performance ...

SQL Server – Moving a Non Clustered Index to another filegroup ...


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to add a sql table to a different FILEGROUP


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer