Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [filegroups]

In SQL Server, a filegroup is a logical collection of one or more physical files onto which the database will write.

Database Files and Filegroups

At a minimum, every SQL Server database has two operating system files: a data file and a log file. Data files contain data and objects such as tables, indexes, stored procedures, and views. Log files contain the information that is required to recover all transactions in the database. Data files can be grouped together in filegroups for allocation and administration purposes.

  • The filegroup contains the primary data file and any secondary files that aren't put into other filegroups.
  • User-defined filegroups can be created to group data files together for administrative, data allocation, and placement purposes.
131 questions
18
votes
1 answer

Move Primary Key to Filegroup (SQL Server 2012)

How can I move a clustered primary key to a new filegroup? I already have found a possible "algorithm" but it is horribly inefficient: Drop non-clustered indexed (requires them to be resorted and rebuilt) Drop clustered index (requires the whole…
usr
  • 7,390
  • 5
  • 33
  • 58
15
votes
1 answer

Columnstore Index in read_only filegroup prevents CheckDB

It appears setting a filegroup to read_only prevents dbcc checkdb for the entire database if the filegroup contains a columnstore index. When attempting to run checkdb or checkfilegroup (for any filegroup in the database, including read-write…
Peter Vandivier
  • 5,485
  • 1
  • 25
  • 49
13
votes
1 answer

Benefit of filegroups and setting filegroups to read-only

Can someone cite me a real world scenario on when changing multiple filegroups to read-only is a good option and when to use them? What benefits are there if you set it to read-only? On a database with multiple filegroups, do you have to do…
Keith Rivera
  • 617
  • 2
  • 9
  • 20
13
votes
2 answers

Does there exist a way to determine the exact file that contains an allocation unit in a filegroup of multiple files?

I was hoping to get a granular view of which database files contained which allocation units for the various HoBTs (both aligned and non-aligned) living in a database. The query I've always used (see below) has served me well until we began creating…
swasheck
  • 10,755
  • 5
  • 48
  • 89
12
votes
2 answers

CREATE TABLE TEXTIMAGE_ON [PRIMARY] is redundant it there's only one filegroup?

I've recently generated scripts for a legacy database, and discovered that most of the tables were created with TEXTIMAGE_ON [PRIMARY], but there's only one filegroup. TEXTIMAGE_ON [PRIMARY] is redundant when there is only a single filegroup,…
jbd
  • 233
  • 1
  • 2
  • 6
12
votes
2 answers

Partitioning on a single filegroup

I have some very large tables in my database, but a substantial chunk of this data is "old". Due to circumstances beyond my control, I am not allowed to remove this "old" data. The other limitation is that I cannot modify the database, meaning…
Michael Neymit
12
votes
3 answers

SQL Server: filegroup for system tables only?

One of our corporate standards to is have a separate filegroup/file for user tables/indexes. This is set as the default so no need to qualify CREATE TABLE statements. So it looks like this fileid 1 = system tables, MDF fileid 2 = t-log =…
gbn
  • 70,237
  • 8
  • 167
  • 244
9
votes
2 answers

Cannot remove filegroup with no files associated

I am experiencing some odd error messages on SQL Server 2017 CU3. I am migrating databases and reorganising filegroups. By "reorganising" I mean that I use a stored procedure which creates a partition function and partition scheme on the new…
Martin Guth
  • 715
  • 1
  • 8
  • 22
8
votes
7 answers

Cannot remove unused filegroups

I wanted to remove some unused filegroups/files in a SQL Server Database but am stuck because SQL Server thinks the filegroups/files are still in use. Some background: We had some partioned tables that we converted back to non-partitioned ones All…
sarnu
  • 203
  • 1
  • 2
  • 6
8
votes
1 answer

SQL Server Updated Record on Read-Only Filegroup?

I have a very large database in our data warehouse where we have implemented partitioning to manage maintenance and backups. Records of a certain age are eventually migrated to a read-only file group once a month. Occasionally our ETL process…
toosuto
  • 103
  • 10
7
votes
2 answers

How can I backup & restore a single FILEGROUP in Sql Server 2008

Previously, on ServerFault I asked a question about backing up and restoring a Sql Server 2008 Filegroup. Today, when I tried to RESTORE one of these FILEGROUP backups, I got the following error:- Processed 1895080 pages for database 'XWing', file…
Pure.Krome
  • 283
  • 1
  • 3
  • 12
7
votes
1 answer

How to specify the exact .ndf file on a filegroup to save a table in?

I have a filegroup(not the primary one) which contains two different .ndf files, for example a1.ndf and a2.ndf. Now I want to create a table and I want my table to be saved in a2.ndf file not a1. Is there a way to do things like this? I am using SQL…
igelr
  • 2,162
  • 3
  • 26
  • 56
7
votes
2 answers

Multiple filegroups vs. multiple files in a single filegroup

From what I've read online, it seems that multiple filegroups offer only two advantages over having multiple files in a single filegroup: The ability to isolate a specific table to a specific drive - the only way to accomplish this is to add a new…
CaptainSlock
  • 464
  • 1
  • 6
  • 13
5
votes
1 answer

SQL Server Database File Groups on a SAN: Relevant or Not?

I am about to build out a new SQL Server and I was planning to make extensive use of file groups. I expect heavy growth, and heavy read/write to 5 different databases on this server. I was planning on creating 2 additional file groups (one for user…
DMill
  • 191
  • 1
  • 2
  • 4
5
votes
3 answers

Dynamic Table partitioning on a daily basis

I have a SQL Server database which contains two tables -- Acks and Logs. These two tables are related in logic but not in a relational database way. Basically, every message that comes in gets saved in the Log table and, if our server acknowledges…
Zapnologica
  • 779
  • 4
  • 9
  • 19
1
2 3
…
8 9