The tag has no usage guidance.

learn more… | top users | synonyms

0
votes
0answers
22 views

New Archive Instance or use of Filegroups

I am working with a fairly large database (1.5TB). We are looking to offload old data that is not going to change. I have done multiple searches to try and determine the best course of action but have ...
5
votes
3answers
2k views

what is causing arithmetic overflow in the query below? [closed]

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, ...
1
vote
1answer
105 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
142 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
38 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 ...
4
votes
6answers
9k views

Cannot remove unused filegroups

I wanted to remove some unused filegroups/files in a SQL Server Database but am stuck because SQL Server thinks the filegroups/files are still in use. Some background: We had some partioned tables ...
0
votes
0answers
16 views

Backup and restore specific filegroups [duplicate]

I've seen this Stack Overflow question: Backup and restore SQL Server database filegroup. However, it didn't do what I needed. I have a database with three filegroups: primary, reporting, and ...
0
votes
0answers
35 views

Unable to Remove Filestream Filegroup and Associated Database File

I had 5 FileTables which I have since converted to regular tables with varbinary(max) columns instead, migrated all the data from the filetables to the new tables and dropped the filetables completely....
1
vote
1answer
44 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 ...
1
vote
0answers
58 views

Best ratio for files and file groups for a highly partitioned table

I have a number of tables with exactly 1000 partitions. Each table can be up to 200G with 100M rows and cannot be compressed. Data in partitions distributed quite equally. The SQL Server has 8 cores....
6
votes
1answer
134 views

Benefit of filegroups and setting filegroups to read-only

Can someone site me a real world scenario on when making multiple filegroups to read only a good option and when to use them? What benefits are there if you set it to read-only? On a database with ...
10
votes
2answers
135 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 ...
0
votes
0answers
90 views

Create File,secondary FileGroup then move a table and restore database with 'Primary'

I created a secondary FileGroup, added a File, moved a table there, and then restored the database with 'Primary'; however, the table name is still there and when I use a select statement I get an ...
1
vote
1answer
123 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 ...
1
vote
3answers
7k views

SQL Server 2008 R2 Log File Location

One of the databases I am going to support does not have a validate log file location. The drive which the log file is pointing to does not exist. But I don't see any error in database error log ...
0
votes
1answer
437 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
55 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
155 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
150 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
0answers
100 views

Moving Large Tables and All Indexes to a new File Group

I'm looking at implementing File Groups on an existing database that's roughly 600GB. The largest hurdle I have is actually setting this up in production. I need the database to still be available but ...
1
vote
1answer
85 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 ...
0
votes
0answers
55 views

Database still reports 600GB after removing 500GB File

I have a database with three files Log Primary Data File Blob Data file the database was becoming too big to manage (600GB) so we removed the blob data (500GB) into FileStorage outside the DB. ...
0
votes
1answer
67 views

How to remove the duplicate records from primary filegroup after partitioning?

Basically, our database is created based on primary filegroup alone. Now, I have done partitioning for a large table which was approx 25 GB. The partitioning was done on secondary filegroups which ...
1
vote
1answer
167 views

Data Files Outgrowing Hardware - Split the File?

Where I'm currently working, the primary business database is located on a virtual server (SQL Server 2005 standard). The temp DB and logs are on one disk, the database itself on another. The SAN is ...
2
votes
2answers
187 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
1answer
101 views

Restore backup to different filegroup

Using Sql Server 2008R2. I have a backup of a filegroup. Is it possible to restore this to a different file group (possibly even on a different database)? I have tried RESTORE DATABASE my_database ...
3
votes
1answer
650 views

SQL Server data files in Windows Azure: filegroups and partitioning still applicable?

I just created a database using the new functionality "SQL Server 2014 data files on Windows Azure" and this works quite well. However, I have a table with a billion records and in our local ...
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:- ...
2
votes
0answers
208 views

SQL Server, Mount Points and File Groups design [closed]

I am looking for some advice on a data warehouse we are building. Specifically around SQL Server, mount points and file groups. We have two design options for how mount points and files groups should ...
5
votes
1answer
406 views

SQL Server Database File Groups on a SAN: Relevant or Not?

I am about to build out a new SQL Server and I was planning to make extensive use of file groups. I expect heavy growth, and heavy read/write to 5 different databases on this server. I was planning on ...
2
votes
1answer
768 views

Database Migration - Create New Filegroup and Move Objects

We have built a new SQL Server Database server instance. I am migrating our databases from existing instance to the new server instance using Backup and Restore. One existing database has a single ...
1
vote
2answers
7k views

Dynamic Table partitioning on a daily basis

I have a SQL Server database which contains two tables -- Acks and Logs. These two tables are related in logic but not in a relational database way. Basically, every message that comes in gets saved ...
1
vote
0answers
368 views

500GB Data Warehouse - how to split it into FileGroups?

OK, so I have: 500 GB DW 5 GB in smaller DBs 220 GB TempDB 350 GB in Log files. My machine is Fujitsu Primergy 64 cores (with HT) and 192 GB RAM. I have several IO locations: 540 GB in-server ...
4
votes
1answer
357 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 <X64&...
5
votes
1answer
392 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 ...
3
votes
1answer
72 views

Moving Clustered Index to new filegroup results in excessive filegrowth

I've Moved the clustered index on large tables to new filegroups to defrag, compress, and to recover space more easily from the original "primary" filegroup. Problem: A 315GB compressed Clustered ...
3
votes
1answer
87 views

Impact of Log backups restore process after file\file group restore on a database

When we restore file\file group(backup1) backup on a database is it necessary to restore all log backups that were taken after backup1 to get database in consistent state?? and also what will be the ...
3
votes
1answer
652 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 [...
1
vote
1answer
1k views

Moving a heap between filegroups

I would like to move the tables in my database from one filegroup (Primary) to a another (a new one). I can find lots of advice on this concerning tables with clustered indexes but all these tables in ...
2
votes
2answers
94 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
834 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
303 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
1k 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
192 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
3k 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
2k 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
65 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 - ...
1
vote
0answers
125 views

file extension of the file types cdump, incident, trace

I need to know the file extension of the file types cdump, incident, trace, alert. Actually I want to create files of the above types manually using mkfile command. There are no existing files of ...
1
vote
4answers
654 views

SQL Server 2008 DB Performance on single disk

I have a database in SQL Server 2008 with around 20 GB in size. And it is increasing rapidly. Somehow I can not add multiple independent harddisks to increase IO performance. If a add large table ...
3
votes
2answers
4k 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 ...