Questions tagged [filegroups]
In SQL Server, a filegroup is a logical collection of one or more physical files onto which the database will write.
125
questions
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
3
votes
2
answers
2k
views
Autogrow. Primary vs additional data files
My databases all use autogrow, which grows the primary MDF file on a percentage. But one of the databases, from a third party application grows by adding additional .NDF files.
Where is this option ...
4
votes
2
answers
360
views
Partitioned Views - Storage of Indexes
I have a single table that contains 4 billions rows that I plan on splitting up in to separate tables for each Quarter of the year. Then I want to create a partitioned view across all tables.
At the ...
- 466
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
1
vote
1
answer
3k
views
How to fix filestream?
I have SQL Server 2008 R2, when I try to access any value stored in filestream I get the following error:
Msg 233, Level 20, State 0, Line 0
A transport-level error has occurred when receiving ...
- 147
1
vote
1
answer
85
views
Best way to perform backups with filegroups and then restore those backups
Scenario:
Database consists of these file groups:
PRIMARY
xxTEST2
xxTEST3
xxTEST4
Database consists of the following files in those file groups:
xxTEST_RESTORE - Rows Data - PRIMARY - ...
ShaunStL
1
vote
0
answers
268
views
file extension of the file types cdump, incident, trace
I need to know the file extension of the file types cdump, incident, trace, alert.
Actually I want to create files of the above types manually using mkfile command.
There are no existing files of ...
- 11
1
vote
4
answers
769
views
SQL Server 2008 DB Performance on single disk
I have a database in SQL Server 2008 with around 20 GB in size. And it is increasing rapidly.
Somehow I can not add multiple independent harddisks to increase IO performance.
If a add large table ...
- 279
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
5
votes
1
answer
2k
views
The backup of the file or filegroup "fileStream" is not permitted, because it is not online
I'm trying to backup a database, which has been created by someone I don't have access to. I get this error message:
The backup of the file or filegroup "fileStream" is not permitted, because it is ...
- 1,327
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
2
votes
1
answer
407
views
Why 2 allocation units during online index rebuild to different file group?
I'm in the process of rebuilding the clustered index of a big table to a different file group. I'm monitoring the rebuild process using sys.system_internals_allocation_units. I'm 3h in but not done ...
- 7,240
18
votes
1
answer
12k
views
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 ...
- 7,240
2
votes
1
answer
693
views
Creating the database on different file groups
What is the pros and cons of creating the database for a operational system on different file groups? I know it is useful if you want to keep the static part of a database online when the other part (...
- 3,674
0
votes
2
answers
2k
views
How to move all non-unique and non-clustered indexes to a different filegroup?
I want to move all indexes to a separate hard drive. To that effect, I created a new filegroup, then added a new file to it (e.g. alter database foo add file(...) to FileGroup).
Now I want to move ...
- 1,919
1
vote
1
answer
1k
views
How many files should I create for one filegroup and 9 weekly partitions?
I have 9 weekly partitions on one filegroup. How many files do I need to create?
There are 3 tables containing 46 million, 16 million and 10 million rows.
- 11
5
votes
1
answer
455
views
Query to find optimal filegroups for objects
I have been assigned a task to create four file groups (one per volume) locate tables so that they are located in a manner that during an ETL process no filegroup is being read and written to at the ...
- 179
4
votes
1
answer
1k
views
Sql Server Index Data File Drive Location
As part of my ongoing saga of migrating from one SAN to another, the new SAN vendor said that I need to have my index data file in the same drive as my primary data file. Otherwise, given the tools ...
- 10.6k
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
10
votes
2
answers
16k
views
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 ...
- 213
1
vote
1
answer
519
views
What is the effect on primary keys when moving a CLUSTERED index to a new filegroup?
I'm moving a CLUSTERED index from one filegroup to another, using the with drop_existing=on functionality.
Do I need to be concerned about primary keys? In other words, is there some special ...
- 11
2
votes
1
answer
760
views
What system information is stored in the primary file in SQL Server?
Suppose I have created a new database in SQL Server with a file group FG1 and marked this as the default. All my user tables etc are now created in FG1. However I also created a file that is part of ...
- 123
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
4
votes
2
answers
2k
views
SQL 2008, how does it link Full Text Catalog to a filegroup
While creating Full Text Catalog, if "on filegroup" is specified - it creates the catalog on this file, but on MSDN I read "On Filegroup" clause has no affect?
- 41
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