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
0 votes
2 answers
250 views

Unable to drop file group because it is not empty

I am unable to delete a filegroup. I am getting the standard error: "The filegroup '~~~~' cannot be removed because it is not empty." I have been searching the internet, and still cannot ...
SpaceGhost440's user avatar
2 votes
1 answer
621 views

Stats update during Index rebuild

I have a task in hand to move rebuild a big table to move LOB pages to a different filegroup on SQL Server 2017 Enterprise Edition. I was testing the scripts in a proof-of-concept environment, and I ...
udhayan dharmalingam's user avatar
0 votes
1 answer
42 views

Is it advantageous to split parts of large time-based tables and store in separate data files? [closed]

On Microsoft SQL Server, I have large (multi-gigabyte) time-based tables for which I want to periodically age out and archive older data (say at the end of each year drop everything 5 years or older). ...
user avatar
0 votes
1 answer
238 views

How to remove unallocated space after partitioning

I've recently done partitioning on a table (postcode column) and created new filegroups for each postcode. Now that it is no longer using the PRIMARY filegroup, the file size is still as it is. How do ...
aYasser97's user avatar
1 vote
0 answers
63 views

What else are there in the file

I am trying to empty a file in order to remove it from database. First of all, I moved the tables and indexes with creating clustered and nonclustered indexes with DROP_EXISTING = ON in another ...
Meyssam Toluie's user avatar
0 votes
1 answer
2k views

How to merge multiple filegroup data files to a single data file?

Have a single 500gb DB with a single file group consisting of 4 data files. This was due to old SAN volume space restrictions that are no longer in play on the new SAN. Would like to consolidate ...
user2368632's user avatar
  • 1,087
0 votes
1 answer
535 views

Azure SQL - Hyperscale service tier - are table partitions supported

In the Azure SQL DB, running in the Hyperscale service tier, are table level partitions supported? It does not seem to be called out in the known limitations page. While trying to create a filegroup ...
Anand Sowmithiran's user avatar
-1 votes
1 answer
136 views

Attach of renamed files conflicts with original files

I am having difficulties attaching DB in a certain environment while the same scenario works in my dev or other production instances. SCENARIO There is a backup of database files (MDF, NDF, LDF, ...
jericzech's user avatar
  • 945
0 votes
2 answers
122 views

Does changing database growth rate force a new execution plan?

For many years there has been a long running SQL Agent job that has taken nearly two hours to complete. Approximately two weeks ago on a slight whim a decision was made to change the growth rate on ...
TJJFSQLDEV's user avatar
1 vote
3 answers
987 views

Does spreading table data across multiple files (in the filegroup) improve performance?

From the product documentation on filegroups: For example: Data1.ndf, Data2.ndf, and Data3.ndf, can be created on three disk drives, respectively, and assigned to the filegroup fgroup1. A table can ...
variable's user avatar
  • 3,442
2 votes
2 answers
1k views

SQL Server - Restore only primary Filegroup but bring the database online

I'm trying to find out if it's possible to bring a SQL Server 2016 Enterprise database online by restoring only the primary Filegroup. My scenario is, we have a fairly large OLTP database, of around ...
FrugalShaun's user avatar
2 votes
2 answers
39 views

Indexes all Exist in non Primary Filegroup - Advantages?

I have come across a database where all the indexes are stored on a filegroup that is not the primary filegroup. The database has 4 files, one of which is in the primary filegroup and the other 3 are ...
SE1986's user avatar
  • 1,928
3 votes
2 answers
2k views

How to identify the top disk space consuming tables by file?

Is there a query I can run to see the top disk space consuming tables for a given file in a file group? Need to free up some disk space for a given SAN drive and need to identify which tables are ...
user2368632's user avatar
  • 1,087
0 votes
2 answers
241 views

Guidance on strategy for serving files remotely using SQL Server

Thanks for the help. "I" am the IT department for a small business and my ignorance may keep me from asking the right questions so please bear with me. Currently, we are using MS Access ...
Tom Schreiner's user avatar
0 votes
0 answers
385 views

SQL Server File or Filegroup Creation Date

Is there a way to find out when a File was added to a filegroup in SQL Server. Or how to determine when a filegroup was created? I've tried querying the sys tables looking at the lsn information and ...
user210084's user avatar
4 votes
0 answers
701 views

Remove table from DEFUNCT filegroup so we can recreate the table again in a different online filegroup

We have a BIG production database. We also have STG and UAT servers, where we restore the PROD database. One of the biggest tables (+250 GBs) in this PROD database is in the SECONDARY filegroup. We ...
Victor Barajas's user avatar
1 vote
0 answers
73 views

Removing files from a filegroup

I am writing some T-SQL code to retrieve the file names that make up a file group so that I can remove the files and the related filegroup. The way that I coded it is below: set @cnt = (select ...
Paul McKibben's user avatar
1 vote
1 answer
59 views

Where would the table data be if you had 3 filegroups, 2 with tables and 1 with all indexes?

I'm studying filegroups at the moment as I haven't used them much before. A class I'm taking mentions the above scenario. I was wondering if I'm understanding it correctly: Would the 2 filegroups ...
Crattik's user avatar
  • 11
1 vote
1 answer
114 views

Control SQL Server pace of db shrink to split into multiple files

(Manually moving this from StackOverflow, as it really belongs here.) We have a production SQL Server Enterprise database that has a few TB of data in it. We have a need to split the data into ...
GaTechThomas's user avatar
0 votes
1 answer
3k views

How to fill/insert fake data into a MS Sql Server

I'm trying to test out some configuration settings for an MS Sql Server FILEGROUP and to do this I wish to fill my FILEGROUPdisk up so it will start populating the 2nd FILEGROUP. I've just created a ...
Pure.Krome's user avatar
0 votes
1 answer
3k views

'PRIMARY' filegroup is full [closed]

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 ...
Logain Theran's user avatar
2 votes
1 answer
894 views

Architecture of table for lot of inserts per second in SQL Server 2019

We are in process of development of project which will process around 51 000 000 records daily (~around 500-600 inserts per second). We choose Always On solution which will contain: One Primary ...
axdna's user avatar
  • 111
2 votes
2 answers
746 views

With multiple files in a file group, when does data start being placed on the 2nd file in the filegroup?

In a previous question, it was suggested that I should add an extra "File" to my existing (Primary) Filegroup to easily add more disk space for my DB. So I did that and nothing broke. But I'm not ...
Pure.Krome's user avatar
2 votes
1 answer
372 views

Do I need to enable Trace Flag 1117 for equally sized data files?

I was reading about fill proportional algorithm in SQL Server and then I recalled TF1117. BOL states: When a file in the filegroup meets the autogrow threshold, all files in the filegroup grow. ...
Rauf Asadov's user avatar
  • 1,253
0 votes
1 answer
716 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: ...
Avi's user avatar
  • 265
2 votes
2 answers
1k 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 ...
Pure.Krome's user avatar
2 votes
1 answer
362 views

Can you create a new filegroup for a table in a database that is live?

I have a SQL Server 12.0.5223.6 instance with a database on it. I'm wondering if I can create a filegroup for one (or more) tables while it's running? Or does it have to have no data in the tables? ...
Pure.Krome's user avatar
0 votes
2 answers
408 views

Initial Full Backup required for Filegroup Backups?

I'm assuming that an initial FULL BACKUP is required if I am taking FILEGROUP backups and would like this confirmed. Many examples show one at the start, but don't make note that it is explicitly ...
crucible's user avatar
  • 307
0 votes
1 answer
235 views

Multiple mdf, empty one is missing

Hi guys i have a big problem. I know i did such stupid and unforgivable mistake. And know please help me if you can. I wanted to make copy of database. I should make it by backup and then restore as ...
user3590388's user avatar
1 vote
0 answers
102 views

Transactional Replication and distribution database size

I have a transactional replication set up with initialize from backup. In the secondary database server,i have a drive(d:) for data files alone. I have noticed that this drive is getting filled and i ...
user9516827's user avatar
  • 1,305
-1 votes
1 answer
3k views

Reducing .mdf file size

I have a drive, :d, in my server where I have my .mdf file stored and now all of a sudden I notice that the drive :d has no space left. When I checked the .mdf file of the database demo1, it is 1.6TB ...
user9516827's user avatar
  • 1,305
2 votes
3 answers
2k views

Moving all Heap Tables to another Filegroup in SQL Server

In SQL Server 2017, I want to move ALL Heaps in a DB to a secondary filegroup. What's the easiest way to do this? This question is only for Nonclustered Indexes. https://stackoverflow.com/questions/...
user avatar
1 vote
2 answers
87 views

What is the impact on the Cost-Based Optimizer (CBO) when implementing ISCSI SAN solid-state drives

A Microsoft Technet article recommends creating a secondary file group as the default file group (see reference below). The secondary file group should have a number of files, say four, that are each ...
J Weezy's user avatar
  • 199
2 votes
2 answers
1k views

Automatically have Nonclustered indexes in a Different Filegroup

Is there a way to automatically move clustered indexes A on one filegroup, and all nonclustered indexes to a different filegroup B upon DDL creation? We have developers constantly creating tables/...
user avatar
2 votes
0 answers
1k views

Default filegroup, specified in the SQL Server CREATE INDEX statement, is ignored (ON [DEFAULT] storage clause)

Am I missing something? Documentation clearly states, that: ON "default" Applies to: SQL Server 2008 through SQL Server 2017 and Azure SQL Database. Creates the specified index on the ...
AlexM's user avatar
  • 21
0 votes
2 answers
305 views

What happens when one of the filegroup is full within SQL server

Just a basic question due to my lack of understanding on how below works: For one of the databases around 1 TB in size we have 8 file groups with multiple files added to them. Now for one of the FG ...
BeginnerDBA's user avatar
  • 2,169
1 vote
0 answers
118 views

Filegroup per Table partition - remove filegroup and re-add filegroup

I've seaching all over the web for a solution, if it is even possible... I'm not a savvy in Partitioned tables in different filegroups... Our software is supported by SQL 2008 R2 SP2 to SQL 2017 for ...
JohnG's user avatar
  • 1,071
2 votes
1 answer
162 views

Secondary filegroup for Full Text Index only - rebuild when lost

Here's our config: We have all our data in PRIMARY file group We have our Full Text Index in another SECONDARY file group which resides on a very fast (but very unreliable) SSD drive. By "unreliable" ...
jitbit's user avatar
  • 745
1 vote
1 answer
202 views

File Group Association while Snapshop Replication

I want to know how can I save filegroup structure during snapshop replication in SQL Server? Does this property solve my problem? I want the same filegroups in publishers be created in the subscriber. ...
igelr's user avatar
  • 2,120
0 votes
1 answer
102 views

SQL server - Re. Filegroup / partitioning

Assuming VLDB is split into multiple file groups, each having its own file and on a separate disk. Now let's say there is a disk crash bringing down one of the file groups - then will the other parts ...
variable's user avatar
  • 3,442
7 votes
1 answer
991 views

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 ...
igelr's user avatar
  • 2,120
1 vote
1 answer
2k views

MS SQL Server does not create tables in a specified filegroup [closed]

I have three filegroups in my database: Default is TERTIARY. Now I want to create table on PRIMARY and it shows the following error Incorrect syntax near the keyword 'primary'. create table tb1 ( ...
igelr's user avatar
  • 2,120
2 votes
2 answers
3k views

How to restore a file group from file group backup?

I want to restore a specific file group from file group backup. I found examples how to restore it from full backup, but I want to do it from file group backup. Every time when I try, all of the files ...
Swetoslav's user avatar
0 votes
2 answers
480 views

Backup of FileGroups Including Primary

I want to back up my DB. I have some huge tables in some specific FileGroups which I want to exclude them to be getting backup. I have tried this script: BACKUP DATABASE TestDB FILEGROUP = '...
A.Kazemi's user avatar
0 votes
0 answers
51 views

How does the data move within a filegroup for its respective secondary data files

I am confused or do not have better understanding on how SQL works in below scenario and need some views: For a database X, we have a FILEGROUP named FG which has 4 data files tagged to it as below ...
BeginnerDBA's user avatar
  • 2,169
4 votes
1 answer
943 views

Where does SQL Server store symmetric keys?

I am wondering if symmetric keys created in particular database are being stored in the primary file group? Or they are stored in some special file group which the user is not able to interact with? ...
gotqn's user avatar
  • 3,780
3 votes
1 answer
392 views

Why didn't primary file size change after moving large table to new file group?

After looking and finding the close answer, I'd like to post a follow-up, but separate, question to this one (split db to fit on ssds) I have a 60 gig database that is growing quickly, due to one ...
Bill Cummings's user avatar
15 votes
1 answer
455 views

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 ...
Peter Vandivier's user avatar
0 votes
1 answer
2k views

should I shrink the filegroup? how? SQL Server

I just have a question. We recently purged 1,5 millions of record from a table. The Current allocated space for the related Filegroup is 700GB, but the space Used is 300GB. Should I perform a shrink ...
Carla Guelpa's user avatar
1 vote
2 answers
318 views

Tempdb Splitting File/Filegroups among different Databases

A common problem placing databases on one server Instance is they all share same tempdb. Is it possible to allocate tempdb file/and filegroups for separate databases. Does Resource Governor allow this?...
user avatar