Problem With Database Filegroup Restructuring In SQL Server?
Mar 7, 2006
Hi,
I have the following SQL procedure I am running to clean up a filegroup
and move all data to a single .MDF file:
use <db_name>
print 'Move <db_name> db contents to MDF file'
DBCC SHRINKFILE ('<db_name>_1_Data', EMPTYFILE)
DBCC SHRINKFILE ('<db_name>_Log', EMPTYFILE)
DBCC SHRINKFILE ('<db_name>_2_Data', EMPTYFILE)
DBCC SHRINKFILE ('<db_name>_log2', EMPTYFILE)
go
Trouble is that I get the following error:
Server: Msg 1105, Level 17, State 2, Line 3
Could not allocate space for object '<company_name>. Inv. Line' in
database '<db_name>' because the 'Data Filegroup 1' filegroup is full.
There are five files:
- a .MDF file (which is already set as the primary filegroup, 43MB in
size);
- <db_name>_1_data.ndf (this file is 11GB in size);
- <db_name>_2_data.ndf (this file is 4GB in size, empty & cannot be
shrunk);
- 2 .LDF files
I have been able to remove the "<db_name>_2_data.ndf" file
successfully, but moving the objects from filegroup 1 to the primary
filegroup has yielded the same error message every time. Any ideas on
what I can do to resolve this?
I have two databases like each other that one is the backup of another. Each DB have 2 filegroups. I want to replace one filegroup from one db to another. How do I do this? Or how do I backup and then restore?
I have below DB structure in MSSQL for a small application which follow relational approach. Data retrieval (for Hostels) will need several Join, may be Key-Value approach where data retrieval will be fast.
I have backup my database with "file and filegroup"selected. I have dropped my database by accident. is it possible to restore my data back from "file and filegroup".bak??
I must determine FILEGROUP on my DB in SQL Server. Because when i wants to retrieve files which is stored in db, it would be face with error about FILEGROUP.
i must determin FILEGROUP on my DB in SQL Server. because when i wants to retrieve files which is stored in db, it would be face with error about FILEGROUP.
I have a partitioned table in which one of the partitions is on the Primary filegroup. I want to move the data off of that Primary filegroup, and and on to a new filegroup named RTFG6.
Scheme and function currently defined as:
CREATE PARTITION SCHEME [PS1_Left_id] AS PARTITION [PF1_Left_id] TO ([RTFG1], [RTFG2], [RTFG3], [RTFG4], [RTFG5], [PRIMARY])
CREATE PARTITION FUNCTION [PF1_Left_id](int) AS RANGE LEFT FOR VALUES (10, 15, 35, 48, 53)
I've tried split and merge, and for whatever reason, always end up with the Primary filegroup holding data.
How do i get it off of Primary completely, and onto RTFG1 to RTFG6?
I don't want to export to a holding table and re-create the table if i can avoid it, due to identity columns and relationships with multiple tables.
I work for a 24/7 shop. We currently have a table that is partition on monthly. I have to created a script that will add a new file group, add the new file to the group, and alter the the partition scheme and function. However, I need for this process to not cause a lock on the table. Typically I get the locking and issues when I am run the split command. Is there a way to prevent this from happening?
I'm being asked to create multiple filegroups for a new database based on the table type, transaction, lookup, misc... From what i'm reading this doesn't make sense. I'm reading either large tables get file groups, nonclustered indexes when they are about the same size of the data, or a few other reasons...
First of all, we are talking about the same disk (please don't ask me about how it is configured) and I'm not sure yet if restoring separate file groups is even going to be necessary.
So here are my questions (beyond, the test and see what happens) because in the end I'm going to probably have to do what i'm told. So this is for my professional knowledge.
1. Does file groups separated by table type make sense? 2. Should you put tables that are queried often together in the same or different file groups. 3. I'm pretty sure you can't restore single file group for write access, am I correct?
I am new to DBA activities. I have a database name Sample_DB in SQL Server 2014.
This database has below files
LogicalName FileType FileGroup Sample_DB_Rows ROWS Data PRIMARY Sample_DB_C1 ROWS Data Country1 Sample_DB_C2 ROWS Data Country2 Sample_DB_Docs FILESTREAM Data FileGroupForDoc
My database has 3 tables namely Tbl1, Tbl2 and Tbl3.
Tbl1 data stored in primary file group Tbl2 is partitioned based on a key column CountryId and stored in respective filegroup Tbl3 is enabled to store documents, files etc., and its data will be stored in filestream enabled filegroup.
Problem: I need to take backup of each file group separately. I need to restore the backed up file group separately. Is this possible, how to do it.
I've got a partitioned table where I am trying to switch the first partition into a staging table, merge the first boundary and later drop the file and the file group.
How do i find Total allocated space and used space of a memory optimized filegroup?
use memory_optimized_db Go select (SUM(size)*8.0)/1024.0 as Space, FILEGROUP_NAME ( data_space_id ) , type_desc from sys.database_files group by data_space_id,type_desc;
above query gives "current used size of the container " of memory optimized file group but doesn't give Total space detail.
I need to modify a table to reside on a new filegroup and also point TEXTIMAGE_ON to that filegroup instead of PRIMARY. Apparently in the past, the only way to achieve this via SQL is to create a new table, copy over data, drop the old table and rename the new table to the original name. I found this solution in the SQL Server 2005 forum.
Is there any other way to alter this table in order to point the TEXTIMAGE_ON to new filegroup using SQL Server 2014? We are on Standard edition. The technique I am using is the drop constraint (with move option) and add constraint (to new filegroup) commands. The data and indexes move, but not the text data (it still is in primary filegroup).
I have been creating databases in SQL 2008 with a primary filegroup for the system objects and a secondary, marked Default, for the data.
We are preparing a migration to SQL 2014, and the administrator is complaining he won't adopt this structure on the new servers because 'there is no benefit' and 'a backup cannot be restored (!?)'.
I read , When sql server Database having multiple data files within single filegroup then sql server writes data in multiple proportional file algorithm where the amount of data written to a file is proportionate to the amount of free space in that file, compared to other files in the filegroup.
so if there is no filegroups created and multiple secondary files are attached in databse , is there same way data stored and writes data in multiple files by the same algorithm or any different way.
hello, ihave atablelike this - z uniqueidentifier a varchar(40) b varchar(40) c varchar(40) d varchar(40) e varchar(40) f text g image
I am asked to split this table into 2 filegroups and move text and images on to one and other stuff on one. I am new to MS-SQL 7.0. Can anyone suggest me how to go for it. Thanking you.
i understand that the filegroup for a table can be specified in the CREATE Table statement. just out of curiosity, is there any way to move a table to a different filegroup, that too, if the table contains data.
Hi, There is a table which is about 50GB is size. I am thinking of placing the database of this table into a separate .ndf on a separate drive on the server. Is it possible to place this particular table into a searate drive to increase the performance even more? Basically my question is: is it possible to place tables into separate .ndf files? Thanks
Hi, I have created a database with two filegroups called FG_GroupData, FG_GroupHistory. FG_GroupData is set as default. FG_GroupData contains two secondary data files i.e. GroupData1.ndf and GroupData2.ndf I can create a table so that it is stored in FG_GroupHistory. i.e. CREATE TABLE dbo.OrdersHistory ( OrderID int NOT NULL, ProductID int NOT NULL, CustomerID int NOT NULL, UnitPrice money NOT NULL, OrderQty smallint NOT NULL ) ON FG_GroupHistory Questions: 1) How do I add tables to each .ndf file inside a group i.e. FG_GroupData For example, how do I add a table to GroupData1.ndf and one to GroupData2.ndf ? 2) I guess there is no need to place the file name i.e. .ndf inside the select query Thanks
What is the advantage of having two secondary files inside a filegroup. For example, I can create a table and place it inside a filegroup which obviously will use the secondary files. Not sure why some DBAs create just one but otherscreate more than one secondar files in a filegroup. I have read the booksonline but can not figure out the advantages. Thanks
Can anyone show me in SQL7 how to obtain Available Space on a particular filegroup in a database (not the database or datafile). I am trying to include this in a script to monitor my database which uses Filegroups and I have every other info that I need (from the sysfiles table) except the available space. Thanks in advance!
1. from Transcender ..... One file for each filegroup is sufficient because creating multiple files for the same filegroup on the same disk does not provide any performance gain
2. from bol .... Therefore, creating more files per filegroup can help increase performance because a separate thread is used to scan each file in parallel
Hi , I am restoring a VLDB from a Standby server which has just .mdf and .ldf to my production server which has .mdf and a file group with a single .ndf file, i want to restore the databse on to the Secondary file(is this possible) i checked the BOl but could not figure out of what command is to be given
Is it possible to back up one filegroup independently from the second when tables in one group have foreign key relationships with tables in the second group?
A few days ago one our database's default filegroup filled up. To solve the problem one of my colleagues created a new filegroup. This didn't solve the problem as no objects were placed on this filegroup and the initial one is still full. The idea was to expand the original filegroup over a new physical disk, not to create a new filegroup on that disk. Unfortunately, this is what happened. To solve this I want to delete the new filegroup, but when I issue the command 'alter database PvgOmcsOds remove filegroup ternary' I get the message 'Server: Msg 5042, Level 16, State 7, Line 1 The filegroup 'ternary' cannot be removed because it is not empty.' Yet there're no objects placed on this filegroup. How can I get rid of this filegroup?
