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
3
votes
0 answers

Moving all Clustered indexes to another Filegroup in SQL Server

In SQL Server 2016, I want to move ALL Clustered indexes in a DB to a secondary filegroup. What's the easiest way to do this? This question is only for Nonclustered Indexes. Moving all non-clustered indexes to another filegroup in SQL Server Note:…
user11500819
3
votes
2 answers

How to move existing Indexes and primary keys to secondary filegroup in SQL SERVER

All the indexes and primary keys were created in primary file(.mdf). Is there any way to move all the index and primary key to secondary filegroup(.ndf)??
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
3
votes
1 answer

Automated Filegroup Migration in SQL Server

Recently I've been trying to restructure an old database that was not designed with filegroups (just the default PRIMARY) and, among other things, move a bunch of tables to a new Data filegroup residing on a SAN. I know how to migrate the…
Aaronaught
  • 120,909
  • 25
  • 266
  • 342
2
votes
2 answers

Partial clone of a database

We're currently migrating our LIMS (Laboratory Information Management System) from Oracle to MS-SQL, and I'm having difficulty in a a certain area. We run three separate instances: Production, Development & Test. The Prod instance is the live one,…
Russell Jones
  • 21
  • 3
2
votes
2 answers

Specify default filegroup for indices?

This is puzzling me - in SQL Server 2005/2008, I can specify the default file group which is then used for all data tables. I can also specify another filegroup for all BLOB type fields. But I cannot figure out if and how I can specify a default…
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
2
votes
3 answers

SQL Server: changing the filegroup of an index (which is also a PK)

We're doing a cleanup on a group of databases, and the first step is to get all indexes in the database into the correct filegroups. Currently, those indexes are mixed between the DATA filegroup and the INDEXES filegroup; they all need to move to…
Sam
  • 1,514
  • 2
  • 14
  • 22
2
votes
2 answers

Can I split a single SQL 2008 DB Table into multiple filegroups, based on a discriminator column?

I've got a SQL Server 2008 R2 database which has a number of tables. Two of these tables contains a lot of large data .. mainly because one of them is VARBINARY(MAX) and the sister table is GEOGRAPHY. (Why two tables? Read Below if you're…
Pure.Krome
  • 84,693
  • 113
  • 396
  • 647
2
votes
5 answers

Delete Filegroup and File from database

This is my database structure: 4 filegroups: PRIMARY, FileGroup201708, FileGroup201709, FileGroup201710 4 files: File1.mdf, File201708.ndf, File201709.ndf, File201710.ndf This is the partition scheme and Function: CREATE PARTITION FUNCTION…
Andrea Da Como
  • 21
  • 1
  • 4
2
votes
2 answers

Would a filegroup with many files located on the same drive outperform if it had just one file on SQL Server 2008 R2?

I've a couple of questions regarding filegroups and their files (.ndf). A filegroup with many files is faster than a filegroup with just one file? (All files are located on the same drive, i.e., same access time for their data) If (1) is false. A…
gsb
  • 1,219
  • 1
  • 16
  • 31
2
votes
1 answer

Files and File Groups in Entity Framework 6

I am trying to split off a read-only, heavily used auditing table from the rest of the database. Putting it in a filegroup and seperate file seems the best solution. However I cannot figure out how to set this up in entity framework, do I need to…
Tim
  • 2,968
  • 5
  • 29
  • 55
2
votes
2 answers

SQL 2013 Filestream Filegroup Full, but have plenty of space?

Getting errors constantly that the filegroup is full for the filestream table. Though there was plenty of space on the system (ram, disk, ...). Also noticed cases where if you delete files from table, it does not delete the file from disk. Even if…
justinlhudson
  • 41
  • 1
  • 6
2
votes
1 answer

Use the default database path to create a filegroup in a script sql server 2008

Quick question, it is possible to include in a SQL Script something like this? IF FILEGROUP_ID('FG') IS NOT NULL ALTER DATABASE [MYDB] ADD FILE (Name=[FG_DATA], Filename='{My default server path}') TO FILEGROUP [FG] GO The idea is to create a…
Ray
  • 483
  • 4
  • 17
2
votes
2 answers

Storing images in a Sql Server 2008 database using Filegroups/Filestreams

Has anyone had an experience storing user uploaded images in a MSSQL 2008 Database using Filegroups and Filestreams? I read a few articles that seemed to say they are a good idea because you get all the advantages of storing images on disk and in…
Vyrotek
  • 5,356
  • 5
  • 45
  • 70
1
vote
0 answers

Archive old records using SWITCH to another table and avoid having to backup this "stale" data repeatedly

This question is about the SQL Server 2019. Scenario: I have a source table, which is partitioned by MonthId (YYYYMM). I also have an archive table of identical structure (except it lives in a different schema). As the time goes by, the older…
SQL_Guy
  • 333
  • 4
  • 15
1
vote
3 answers

Should static database data be in its own Filegroup?

I'm creating a new DB and have a bunch of static data that won't change. If it does, it will be a manual process AND it will happen very rarely. This data is a mix of varchars and Geographies. I'm guessing it could be around 100K or so in total,…
Pure.Krome
  • 84,693
  • 113
  • 396
  • 647
1
2
3 4 5 6 7