Questions tagged [filegroups]
The filegroups tag has no usage guidance.
1
vote
2answers
46 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
125 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/...
0
votes
0answers
35 views
how to backup, remove and then restore a filegroup of a partitioned table
Continuation of the question "Archiving SQL Server data on disk to be pottentially restored".
I'm still looking for a way to archive data per month which can easily be restored...
I'm in the ...
2
votes
0answers
107 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
32 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
18 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
27 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
16 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
49 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
299 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
207 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
450 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
74 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
36 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
...
3
votes
1answer
315 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
50 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
259 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
498 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
156 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
57 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
126 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
1k 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
44 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
848 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
70 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
447 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
55 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
107 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, ...
3
votes
2answers
703 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
573 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
103 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
1k 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 ...
5
votes
2answers
3k views
Multiple filegroups vs. multiple files in a single filegroup
From what I've read online, it seems that multiple filegroups offer only two advantages over having multiple files in a single filegroup:
The ability to isolate a specific table to a specific drive - ...
0
votes
1answer
60 views
Size of an “empty” PRIMARY filegroup
I have a question for you concerning the PRIMARY filegroup.
Out goal is to have a small PRIMARY, so that we can do partial restores to other systems (test, dev)
So, we moved all tables and indexed ...
3
votes
1answer
1k views
Can I set a filegroup of database offline,change the location of files and bring it online again?
I found that we can only take filegroup offline through t-sql,but to bring that filegroup online we need to restore the whole database.
2
votes
1answer
120 views
SQL Server seperate data files
When splitting out a data file into separate files is there any benefit in having tables usually queried together in the same file?
I'm against it but a vendor has suggested it as part of setup. May ...
8
votes
1answer
638 views
SQL Server Updated Record on Read-Only Filegroup?
I have a very large database in our data warehouse where we have implemented partitioning to manage maintenance and backups. Records of a certain age are eventually migrated to a read-only file group ...
5
votes
3answers
11k views
what is causing arithmetic overflow in the query below?
when I run the following query on MY_Database
select * from sys.sysfiles
I get the following results:
But when I run a dynamic query that gets the percent of free space I get:
Msg 8115, Level ...
1
vote
1answer
150 views
SQL Server specific file group
I have a database with three file groups, Primary, FG_ExtendedTables, and FG_ReportingTables.
I need to create another database (production db let's say), and restore only specific file groups and ...
5
votes
2answers
1k views
How to find the specific file in a filegroup in which an object resides
Today, I encountered below error on one my SQL Server which I manage.
Could not allocate space for object '%.*ls'%.*ls in database '%.*ls'
because the '%.*ls' filegroup is full. Create disk ...
1
vote
2answers
63 views
List objects in SQL file
We are trying to migrate a database from SQL 2014 to Azure SQL.
In SSMS, you can right click on the database and then on a task to "Deploy Database to Windows Azure SQL Database..".
This task ...
1
vote
1answer
288 views
How to remove secondary file group
We have a SQL Server database with two filegroups; PRIMARY and SECONDARY, with a single file in each file group.
All the clustered indexes are stored in the secondary filegroup. How can we remove ...
11
votes
1answer
3k 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 ...
13
votes
2answers
778 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 ...
1
vote
1answer
862 views
Bringing SQL Server filegroups online
We've experienced an sql injection attack which corrupted our main db.
The main db had two partitioned tables into filegroups.
The corrupt database entered into Suspect mode, and it's been ...
0
votes
1answer
4k views
Error: 'PRIMARY' filegroup is full
We have increased disk space on the SQL Server, and there is definitely enough space on the server, yet below error is raised:
com.softmar.fileserver.WebFileException: com.microsoft.sqlserver.jdbc....
5
votes
2answers
59 views
Allocation strategy where tables regularly grow and shrink by large amounts
I have a SQL Server 2014 database with 100+ tables that have a 5% growth rate over the course of a year. Lots of reading, very little writing.
This database is going to expand by another set of 20+ ...
2
votes
0answers
324 views
SQL Server 2008 R2 - Two Files Stuck In Restoring Status
Background: SQL Server 2008 R2 mirrored and clustered.
We recently failed over the databases via mirror and everything went smoothly. When it was time to do a differential backup, I got an error so I ...
5
votes
1answer
640 views
Find what data is placed in a datafile
I have a database with 5 .ndf (secondary) datafiles on SQL Server 2008 R2. (Each of these datafiles is assigned to a separate filegroup.) I want to know what data is there in those files. Is it ...
1
vote
1answer
160 views
Possible to restore a damaged database's PRIMARY filegroup only?
I have a database (MyDatabase) with 2 filegroups, [PRIMARY] and [SECONDARY].
The [SECONDARY] filegroup has 2 datafiles, [DataFileA] and [DataFileB].
[DataFileA] is damaged (even after DBCC CHECKDB ...
