Questions tagged [filegroups]

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

Filter by
Sorted by
Tagged with
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 ...
  • 10.6k
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 ...
0 votes
1 answer

Piecemeal restore only one filegroup without primary filegroup restore

I have a db in SIMPLE recovery model, and regularly take FULL,DIFF backups. This db also have filegroups for each month. Each filegroup has exactly one NDF file. Like this: FileGroup: PRIMARY File: ...
  • 266
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 ...
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 ...
  • 69.4k
11 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 ...
user avatar
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 ...
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 ...
  • 203
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:- ...
5 votes
2 answers

One filegroup, multiple datafiles, how to get list of tables in each file

In one of my production databases, I have a filegroup with 3 data files sitting on 3 different disks. There are over 50 tables on this filegroup. How do I identify the location of each of the 50 ...
  • 369
3 votes
1 answer

Moving Clustered Index to new filegroup results in excessive filegrowth

I've Moved the clustered index on large tables to new filegroups to defrag, compress, and to recover space more easily from the original "primary" filegroup. Problem: A 315GB compressed Clustered ...
3 votes
2 answers

Archive a single giant table from a SQL 2008 database - filegroup backup?

I have an extremely large table (400GB data size, inside a 2.5TB database) that is no longer needed. Before we drop it, however, our client contract says we need to archive it to tape in case in needs ...
  • 9,732
2 votes
2 answers

MS SQL running out of Disk Space - can FileGroups be used to free up some space?

Problem We have a Sql Server 2014 with a 1TB attached disk (on Azure) that's running out of disk space. We have about 20GB's left (maybe a few weeks of space). As such, we need to move some data off ...
1 vote
1 answer

Filegroup with multiple files: odd behaviour when moving stuff in

I am currently migrating databases from SQL Server 2008 (SP4) to SQL Server 2017 (CU3). A significant change is that all filegroups have two data files after migration. To accomplish this task I ...
1 vote
1 answer

Restore .mdf data file for a File Group in SQL Server

I have a database with three filegroups and each filegroup hosting one datafile. One of the secondary filegroups XYZ with data file xyz.ndf got updated with wrong/bad information in various tables ...
  • 41