Stack Overflow
Stack Overflow

Questions tagged [filegroup]

A filegroup is a named collection of SQL Server database objects and files used for allocation and administration purposes.

SQL Server databases can store their data in one or more filegroups. There is always a "primary" filegroup which is the default storage area. There can be one or more additional "user-defined" filegroups which can also store data. Reasons to use multiple filegroups include:

  • Larger storage capacity
  • Better performance by distributing requests to multiple drives
  • Additional security options

Drawbacks include:

  • Restore, backups, and disaster recovery can be more complicated
  • Additional programming to make sure that all filegroups are used

See File and Filegroups Architecture at MSDN for more information and examples.

93 questions
71
votes
10 answers

How can I move a table to another filegroup in MS SQL Server?

I have SQL Server 2008 Ent and OLTP database with two big tables. How I can move these tables to another filegroup without service interrupting? Now, about 100-130 records inserted and 30-50 records updated each second in these tables. Each table…
user272887
  • 751
  • 1
  • 7
  • 7
48
votes
10 answers

"Primary Filegroup is Full" in SQL Server 2008 Standard for no apparent reason

Our database is currently at 64 Gb and one of our apps started to fail with the following error: System.Data.SqlClient.SqlException: Could not allocate space for object 'cnv.LoggedUnpreparedSpos'.'PK_LoggedUnpreparedSpos' in database…
Anton Gogolev
  • 113,561
  • 39
  • 200
  • 288
18
votes
3 answers

How do i move a table to a particular FileGroup in SQL Server 2008

I've just created a secondary filegroup and wish to move some tables over to it and then make it read-only. I'm not sure how to do this? do i just use the ALTER blah blah TO MyFileGroup ?
Pure.Krome
  • 84,693
  • 113
  • 396
  • 647
17
votes
3 answers

Moving all non-clustered indexes to another filegroup in SQL Server

In SQL Server 2008, I want to move ALL non-clustered indexes in a DB to a secondary filegroup. What's the easiest way to do this?
IamIC
  • 17,747
  • 20
  • 91
  • 154
13
votes
6 answers

SQL Server 2005 / 2008 - multiple filegroups?

I'm a developer at heart - but every now and then, a customer doesn't have a decent DBA to deal with these issues, so I'm called in to decide.... What are your strategies / best practices when it comes to dealing with a reasonably sized SQL Server…
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
7
votes
4 answers

Create a table on a filegroup other than the default

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…
GSDa
  • 193
  • 2
  • 4
  • 21
5
votes
3 answers

Why do we need secondary data files in SQL Server?

I always ignore this option when creating a new database on SQL Server 2005, simply because we can ignore something that we do not understand and leave it as it is. (I'm not so into DBA) so now I am curious what it is about. From your experience,…
Anwar Chandra
  • 8,538
  • 9
  • 45
  • 61
4
votes
3 answers

SQL Server file and filegroup

I can not think of any reasons why we need to have multiple files inside a file group. The reason why I think of this way is we can control from T-SQL (end user) level about file group, but can not control from T-SQL (end user) level about…
George2
  • 44,761
  • 110
  • 317
  • 455
4
votes
2 answers

Separating an SQL Server database

I'm using SQL Server 2008. My database is almost 2GB in size. 90% of it is one table (as per sp_spaceused), that I need don't for most of my work. I was wondering if it was possible to take this table, and have it backed up in a separate file,…
Jason Kealey
  • 7,988
  • 11
  • 42
  • 55
4
votes
2 answers

How to Restore data in File group databases?

I have a database with two file groups: 1- PrimaryFileGroup 2- ArchiveFileGroup now I have a backup from first filegroup: Primary.bak. and restore with below script : USE [master] GO ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK…
Nima Rostami
  • 2,652
  • 3
  • 15
  • 23
4
votes
3 answers

How to use a variable to specify filegroup in SQL Server

I want to alter a table to add a constraint during upgrade on a SQL Server database. This table is normally indexed on a filegroup called 'MY_INDEX' - but may also be on a database without this filegroup. In this case I want the indexing to be done…
g t
  • 7,287
  • 7
  • 50
  • 85
4
votes
1 answer

How to use a relative file path when adding a file to MSSQL database

I'm creating a database with a filegroup and a file: CREATE DATABASE SuperDb; ALTER DATABASE SuperDb ADD FILEGROUP FileStreamGroup CONTAINS FILESTREAM ALTER DATABASE SuperDb ADD FILE ( NAME = Blobbiez, FILENAME = 'C:\Program…
astef
  • 8,575
  • 4
  • 56
  • 95
3
votes
1 answer

Xcode 4 group file and filesystem sync

Is there any easy way to sync the group structure inside Xcode 4 and the actual folders structure in the filesystem? I have read that some people create new folders on Finder and them drag-and-drop into Xcode, but that sounds like a hack to me. I…
tie
  • 543
  • 6
  • 14
3
votes
2 answers

How to partition huge (15 TB) existing table in SQL server without creating clustered index

I am trying to partition a huge table in SQL. Its size is 15 TB with millions of records. The main idea is to save the coming data onto different disks by creating new filegroup where the current disk can't be expanded any more. I created the needed…
Ala' Mohsen
  • 41
  • 1
  • 5
3
votes
2 answers

Insufficient disk space in filegroup 'PRIMARY' - SQL Server 2008

Am getting following error: Msg 1101, Level 17, State 12, Line 1 Could not allocate a new page for database 'CTL' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup,…
Sreedhar
  • 29,307
  • 34
  • 118
  • 188
1
2 3 4 5 6 7