Questions tagged [filegroups]
In SQL Server, a filegroup is a logical collection of one or more physical files onto which the database will write.
15
questions
13
votes
2
answers
1k
views
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
27k
views
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 ...
- 739
0
votes
1
answer
605
views
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
5k
views
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 ...
- 617
12
votes
3
answers
2k
views
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
10k
views
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 ...
Michael Neymit
9
votes
2
answers
7k
views
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 ...
- 645
8
votes
7
answers
29k
views
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
13k
views
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:-
...
- 235
5
votes
2
answers
5k
views
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
105
views
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 ...
- 31
3
votes
2
answers
551
views
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
919
views
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 ...
- 235
1
vote
1
answer
355
views
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 ...
- 645
1
vote
1
answer
945
views
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