Hot answers tagged

12

An object in a filegroup will use all datafiles in the filegroup. Any table in FG1 resides equally on Datafile1, Datafile2 and Datafile3. If you need to control placement you need to create distinct filegroups. To remove a file from a file group use DBCC SHRINKFILE EMPTYFILE.


12

CREATE UNIQUE CLUSTERED INDEX Your_PK_Name ON YourTable(YourColumnList) WITH (DROP_EXISTING = ON ) ON [YourOtherFileGroup] This preserves the logical PK property despite it not being mentioned in the syntax.


10

The isn't a performance gain to this, there is a recoverabily gain to be made. If file corruption happens in the system tables then the database is lost. If you keep the user data in a separate file group (or groups) then you can restore just those files keeping the rest of the database online during the restore (assuming Enterprise Edition here). If this ...


9

Try the following query. It first creates a local temporary table and then populates it with the AllocationUnitID-to-FileID associations found in sys.dm_db_database_page_allocations, an undocumented Dynamic Management Function (DMF) introduced in SQL Server 2012 (for versions prior to 2012, you can get this info from DBCC IND()). That local temp table is ...


8

Yes, this is redundant. By default, SSMS will script these The option is here:


8

The system objects are the objects which store the schema for all your objects. The stored procedures, table schemas, etc. are all stored in these system objects. If the system objects become corrupt you have to restore the primary filegroup. You can view the data within the system objects by querying the system catalog views such as sys.tables, ...


7

Microsoft's 70-432 training book says "The main reason not to place any of your objects on the primary file group is to provide as much isolation in the I/O as possible. The data in the system objects does not change as frequently as data in your objects. By minimizing the write activity to the primary data file, you reduce the possibility of introducing ...


6

It sounds like the vendor's snapshotting functionality is putting some pretty serious limitations on your flexibility for database layouts. If you have performance issues (and it would be a very long way from the first time someone had database performance issues on a SAN) then you have very little flexibility to spread the database across multiple physical ...


6

You may have some LOB data (text/image/varchar(max)/nvarchar(max)) sitting in the filegroup still. I got caught up by this briefly not too long ago. Rebuilding a table/index on a different filegroup/partition does not move any of the LOB data. SELECT au.*, ds.name AS [data_space_name], ds.type AS [data_space_type], p.rows, o.name AS ...


6

Your question is a bit wide-ranging, however some of the main points include the following. Multiple file groups, in general: can be a performance improvement, assuming they are physically isolated. Typically, you might create one filegroup for clustered indexes, one filegroup for non-clustered indexes, and one filegroup for heaps. Or you might create ...


5

One of the main benefits of utilizing multiple files and filegroups is that you have great control over file growth. Also, prominently you can control and optimize I/O performance, as putting database files on separate physical disks can lead to faster I/O. If you have an I/O expensive query across two tables, putting then on different disks can lead to ...


5

The following SQL will show you which file groups your tables and indexes are in which will make it easy to see if there is data in any file group that shouldn't have data. SELECT f.[name] AS FileGroupName , o.[name] AS ObjectName , o.[type] AS [Type] , i.[name] AS IndexNAme , i.[index_id] AS IndexId ...


4

If the file is still present, you should be able to do this: RESTORE DATABASE YourDB FILEGROUP=YourFG WITH RECOVERY; If the file is no longer present (or present but not eligible to recover the data), you're in for a rough weekend. Depending on the complexity of the database, I would probably start making a backup plan (no pun intended): create a new ...


4

Not sure I understand, are you asking for someone to justify your corporate standard? I would think that whoever wrote that standards doc for your company would be able to shed some light as to why this would be done. That being said, it is not unusual for some shops to want to break out system data from user data. And if used in conjunction with dedicated ...


4

If you're going with traditional partitioned views with each table containing a different yearly quarter, then you have a few levels of separation here: Each Table Each Index on Each Table So in other words, say you have 10 tables that the partitioned view accesses, and you have a clustered index and a nonclustered index on each table. In this case you ...


4

All of them had at least one file with auto-growth enabled and disks hosting them had enough free space available. So then why my index maintenance failed? As it says in the product documentation Reorganize and Rebuild Indexes: (emphasis added) The ALTER INDEX REORGANIZE statement requires the data file containing the index to have space ...


3

Try using sys.allocation_units instead of sys.indexes. BOL says it's for internal use only so I wouldn't write any long term code on it but for this purpose it should be ok. It points to the first IAM page of each allocation unit. I believe it will work better for what you are trying to do. SELECT * FROM sys.filegroups fg LEFT OUTER JOIN sysfilegroups ...


3

But I see nothing that tells it whether to grow the primary MDF, or grow by adding additional NDFs. There is NO option native in SQL Server that will allow you to do this, unless you explicitly specify to add additional secondary files (.ndf) using Alter database command. More info Files and Filegroups Architecture & Database Files and Filegroups. Is ...


3

Like @JonSeigel said above in his comment, this is not a SQL Server action. Your third party application(s) must be doing this on their own, or somebody manually creating the additional data files. And, is there a way to combine these NDF files back into the primary MDF? What you're looking for is DBCC SHRINKFILE(YourDataFileName, EMPTYFILE); where ...


3

How Online Index Operations Work: Temporary mapping index Online index operations that create, drop, or rebuild a clustered index also require a temporary mapping index. This temporary index is used by concurrent transactions to determine which records to delete in the new indexes that are being built when rows in the underlying table are ...


3

In SQL Server 2005 and prior, you could specify the filegroup for full text. In SQL Server 2008 and newer, full text is completely different, and the ON FILEGROUP stuff doesn't matter. However, Microsoft doesn't just rip out syntax - they deprecate it, and a few versions later, it'll disappear. (Backup log with truncate_only is a good example of this.) ...


3

You need to restore the transaction logs so that you have a consistent database. Currently the filegroup which you have restored is at a different point in time than the rest of the database. Once the logs have been rolled forward and everything is consistent then you can bring the database online. If you look at the data in the msdb database you'll be ...


3

Only PRIMARY KEY and UNIQUE constraints have an optional ON filegroup clause. That's because these constraints always create an automatic index to support their uniqueness. The filegroup clause decides where this index will live, so in your case it should always be 'app_index'. You cannot specify a filegroup for other constraints such as FOREIGN KEY, CHECK ...


3

First thanks to everyone who tried to help me with this here and on twitter. We did however finally find the answer. It turns out that someone created a database trigger on model. The trigger parses every create index/table command and changes it to a DATA filegroup if it's a heap or clustered index and an INDEX filegroup for everything else. We were ...


3

It's all about clustered vs non-clustered indexes actually. Primary key has nothing to do with it. A clustered index IS the data. The actual table data is contained in the data pages of the clustered index. If you put a clustered index on a table then the data is stored where you tell the clustered index to be. If you changed your code above to be a ...


3

Is it possible to restore this to a different file group No. possibly even on a different database) No.


2

No. From an "on disk" organisation perspective the fact it is a constraint doesn't matter. That is, the physical and logical table layouts are separate And PK when not clustered is just a unique non-clustered index with some rules


2

As it states in the message you have to restore the log backups up to the point in time of the filegroup backup. In order to bring the database online it needs to play the log back to the same point in time in order for the database to be consistent. However restoring just the primary file group is a special situation. I believe in order to restore the ...


2

The only way to restore a single file group is to run in all the logs so it's up to date with the rest of the files. This would of course run in the bad data modification you're trying to prevent but it's necessary to ensure consistency throughout the database. You'll need to restore it to another location and move the data over. ...


2

I'd favour a BCP extract. BCP export to file. Import somewhere (different database or server) to confirm the file "is good". Archive multiple copies, to multiple tapes. @Marian answered a question of mine with a great list of BCP references that you might find useful. The BCP Basics by SQLFool is also a good starter for ten.



Only top voted, non community-wiki answers of a minimum length are eligible