The tag has no usage guidance.

learn more… | top users | synonyms

6
votes
1answer
4k 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 ...
2
votes
1answer
449 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 ...
0
votes
2answers
1k 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
vote
1answer
704 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.
5
votes
1answer
389 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 ...
4
votes
1answer
758 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 ...
3
votes
2answers
463 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 ...
7
votes
1answer
7k 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 ...
1
vote
1answer
381 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 ...
2
votes
1answer
459 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 ...
6
votes
2answers
11k 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:- ...
3
votes
2answers
1k 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?
9
votes
3answers
1k 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 ...