Tag Info

Hot answers tagged

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

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.


8

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.


7

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, ...


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

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 ...


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 ...


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

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


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

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 ...


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

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 ...


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

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

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

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.) ...


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.


2

First off, gather a performance baseline in order to know what the situation is now. Keep monitoring the performance to see if there is a trend for degeneration. If the performance seems to become an issue, there are a few things to do. Identify the root cause for lack of performance. Is the server expiring memory pressure, IO waits or high CPU usage? The ...


2

There are two advantages to partitioning a table in the same filegroup: Allowing portions of a large index to be rebuilt incrementally, allowing for more efficient maintenance. Review the ALTER INDEX [foo] REBUILD PARTITION=n for more details. Leveraging partition elimination and (possibly) partition level locking to improve query maintenance. I discuss ...


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

The error seems to point at the SQL Server Service account not being able to access the filestream data. Try to change the SQL Server Service account using the SQL Server Configuration Manager (NOT the Windows Services manager!) to one of the build in accounts. Then change it right back to the original account (you will need to know the password here!). ...


2

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 ...


2

I'm going to assume that, as TomTom went through in detail, some of your hardware acronyms are incorrect regarding your actual server (which you apparently already have). I'm also assuming that your reference to a "75GB database" indicates you have one database of 75GB that's your primary DB. This should have only one log file. Assuming you have 8 disks ...


2

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 ...


1

You cannot restore a single filegroup to an earlier state, because that will leave the database in an inconsistent state. You can restore a database one filegroup at a time. However, as long as a particular filegroup is not up to date with the PRIMARY filegroup, any access to a table that is stored (even in part) on that filegroup will cause the query to ...



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