2
votes
1answer
35 views

Sql Server data files in Windows Azure

excuse me for my bad english I just created a database using the new functionality "SQL Server 2014 data files on Windows Azure" and works quite well, however, I have a table with a Billion records, ...
3
votes
1answer
69 views

What happens when you create the primary key of a table on a different filegroup?

Creating a table on a different filegroup is absolutely no problem when using Sql Server. create table common.test ( id int, CONSTRAINT [pk_test] PRIMARY KEY CLUSTERED (id ASC) ON ...
3
votes
1answer
91 views

Why isn't my new index/table being created on the filegroup I specify?

Results from @@Version Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) Sep 21 2011 22:45:45 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 ...
3
votes
3answers
523 views

Best practices for configuring the Drive/Filegroup setup for Microsoft SQL 2008

I am a software developer for a small company. I wish we had a dba but not in the cards at this point for this company. I was working on migrating our 75gb SQL Server 2000 database to SQL Server 2008 ...
1
vote
1answer
124 views

Partition : understanding files and file groups

We are trying to partition a large history table. Being new to partitioning, I was trying to understand the concept of files and file groups. File groups can be set up in following three ways : 1. ...
2
votes
2answers
52 views

Unexpected Results with File Restore

I am practicing a file restore using the AdventureWorks database and I am not getting the restore I am expecting. I am sure there's some embarrassingly simple cause, but I have not found it yet. Here ...
3
votes
1answer
131 views

On which filegroup will constraints be created by default when adding them to a table on a filegroup and how should they be separeated?

We have multiple filegroups, that look something like this: PRIMARY (default) app_data app_index When I create a table on filegroup app_data CREATE TABLE Person ( [PersonID] INT NOT NULL ...
3
votes
2answers
114 views

Can I run concurrent backups of multiple read only filegroups?

I have 62 readonly filegroups in a SQL server 2008 enterprise database. Can I backup multiple read only filegroups at the same time? I would assume this to be the case but given this database is over ...
3
votes
2answers
396 views

Autogrow. Primary vs additional data files

My databases all use autogrow, which grows the primary MDF file on a percentage. But one of the databases, from a third party application grows by adding additional .NDF files. Where is this option ...
4
votes
2answers
87 views

Partitioned Views - Storage of Indexes

I have a single table that contains 4 billions rows that I plan on splitting up in to separate tables for each Quarter of the year. Then I want to create a partitioned view across all tables. At the ...
4
votes
2answers
1k views

One filegroup, multiple datafiles, how to get list of tables in each file

In one of my production databases, I have a filegroup with 3 data files sitting on 3 different disks. There are over 50 tables on this filegroup. How do I identify the location of each of the 50 ...
0
votes
1answer
574 views

How to fix filestream?

I have SQL Server 2008 R2, when I try to access any value stored in filestream I get the following error: Msg 233, Level 20, State 0, Line 0 A transport-level error has occurred when receiving ...
1
vote
1answer
45 views

Best way to perform backups with filegroups and then restore those backups

Scenario: Database consists of these file groups: PRIMARY xxTEST2 xxTEST3 xxTEST4 Database consists of the following files in those file groups: xxTEST_RESTORE - Rows Data - PRIMARY - ...
3
votes
2answers
1k views

Partitioning on a single filegroup

I have some very large tables in my database, but a substantial chunk of this data is "old". Due to circumstances beyond my control, I am not allowed to remove this "old" data. The other ...
4
votes
1answer
502 views

The backup of the file or filegroup “fileStream” is not permitted, because it is not online

I'm trying to backup a database, which has been created by someone I don't have access to. I get this error message: The backup of the file or filegroup "fileStream" is not permitted, because it ...
2
votes
1answer
247 views

Why 2 allocation units during online index rebuild to different file group?

I'm in the process of rebuilding the clustered index of a big table to a different file group. I'm monitoring the rebuild process using sys.system_internals_allocation_units. I'm 3h in but not done ...
3
votes
1answer
1k 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
348 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
465 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
578 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
333 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
514 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 ...
1
vote
1answer
351 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
281 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 ...
3
votes
2answers
6k 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:- ...
8
votes
3answers
791 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 ...