Questions tagged [filegroups]
The filegroups tag has no usage guidance.
113
questions
0
votes
0answers
14 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 ...
2
votes
0answers
46 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 ...
1
vote
0answers
15 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 ...
0
votes
1answer
28 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 ...
0
votes
0answers
38 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 ...
0
votes
1answer
63 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 ...
-1
votes
1answer
62 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 ...
0
votes
1answer
79 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 ...
0
votes
0answers
21 views
Alter Filegroups to move from alternative FG back to Primary [duplicate]
I have a multitude of SQL Db's that have in excess of 80 individual file groups - I have been tasked with a project to try and get those FG's moved back to the primary - so I'd essentially have to ...
1
vote
2answers
34 views
With MS Sql Server + Multiple files in a file group -> when does it start placing data 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 ...
2
votes
1answer
84 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. ...
0
votes
1answer
95 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: ...
2
votes
2answers
157 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 ...
2
votes
1answer
31 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?
...
0
votes
2answers
70 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 ...
0
votes
1answer
102 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 ...
1
vote
0answers
25 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 ...
-1
votes
1answer
329 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 ...
2
votes
2answers
352 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/...
1
vote
2answers
54 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 ...
1
vote
2answers
515 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/...
2
votes
0answers
462 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 ...
0
votes
2answers
90 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 ...
1
vote
0answers
52 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 ...
2
votes
1answer
73 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" ...
1
vote
1answer
45 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.
...
0
votes
1answer
68 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 ...
5
votes
1answer
445 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 ...
0
votes
1answer
677 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
(
...
2
votes
2answers
1k 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 ...
0
votes
2answers
181 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 = '...
0
votes
0answers
45 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
...
4
votes
1answer
540 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?
...
3
votes
1answer
105 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 ...
15
votes
2answers
353 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 ...
0
votes
1answer
874 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 ...
1
vote
2answers
206 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?...
0
votes
1answer
67 views
SQL OLTP Filegroups - Lookup Tables
According to resources, in Dimensional Warehousing, it is a good idea to place Dimension tables in a different filegroups from Fact Tables. In OLTP, is it a good idea to place Lookup tables in ...
1
vote
1answer
193 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 ...
8
votes
2answers
3k 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 ...
0
votes
0answers
48 views
SQL Server - Partial Backup
I want to check if my Data Warehouse historical filegroup backups are valid with my most recent Primary Filegroup backup.
I backup the Primary Filegroup every night but historical filegroup backups ...
-1
votes
1answer
2k views
FILEPROPERTY - SPACEUSED
I used SELECT FILEPROPERTY('file_name', 'SpaceUsed'), and it returns NULL, what does it mean?
I am correctly using it in the correct database.
I cannot assume that NULL means empty since:
I tried ...
0
votes
1answer
85 views
Database Files and Filegroups
In my case wherein I have a file file_01.mdf and file_02.ndf under 1 filegroup, if ever file_01.mdf was already full (no autogrowth enabled), will it give me error on file_01.mdf if data is attempted ...
3
votes
1answer
559 views
How to force users to specify a filegroup when creating tables/indexes
I will soon be migrating to SQL Server 2017 and hence am working on beautifying some neglected aspects of our databases. One aspect is filegroups: my DBA predecessor has created multiple filegroups ...
1
vote
0answers
60 views
Any performance gains from having identical clustered and nonclustered indexes on table on different filegroups?
I am attempting to rearchitect our MSSQL database such that we have two filegroups, one for data, on a 15K HDD, primary, and one for indexes on a separate SSD, indexes, in order to realize some ...
1
vote
1answer
176 views
Is SQL Server's fill strategy the same for multi-file tempdb as multi-file (non-tempdb) filegroups?
Will tempdb I/O from a single query be split across multiple tempdb files? (assuming that tempdb is configured to use multiple files, of course!)
For non-tempdb databases, MDSN seems to say that yes, ...
2
votes
2answers
1k views
Backup and Restore SQL server files and filegroups
I have a database called Admin. It has only one default filegroup called primary.
File group: primary
files in the file group: Admin_data.mdf
Now I have taken a full filegroup backup.
Backup file ...
0
votes
1answer
854 views
TSQL - merge partitions online
the short version of my question is: "During the merge of two partitions is the whole partitioned table being blocked. How can I access, for read&write, the part that is not affected by the merge?"...
0
votes
1answer
205 views
Unable to write to ndf after shrink emptyfile
I am doing some file balancing practice/experiments on AdventureWorks and pages stopped writing to the ndf file (AdventureWorks2014_Data2.ndf).
Early tests were working as expected. Proportional ...
0
votes
1answer
2k views
move AG database files between disks
Environment: SQL 2016 SP1, AG with 2 replicas, sync commit, no readable secondary. 1 file in the PRIMARY FG, 1 for the index FG.
Problem: How to move the files for a database in this AG, from the ...
