Moving Data From One Filegroup To Another.....but Wait, There Is More!!

May 13, 2008

OK, I know this is out there all over and yes I did a search for this topic; however, I am confused about tables with an image data type and with moving text file group to another filegroup.

Here is what I have:

I have a table storing imaged documents and has become very large. I want to move the table to another filegroup. The table is created like this:

USE [PD51_Data]
GO
/****** Object: Table [dbo].[SCANNEDDOCUMENTS] Script Date: 05/13/2008 14:52:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SCANNEDDOCUMENTS](
[DocID] [int] IDENTITY(1,1) NOT NULL,
[CaseID] [int] NOT NULL,
[DocName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Doc] [image] NOT NULL,
[DocLocation] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DocNotes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TopicID] [int] NULL,
[ScannedDocumentsCheckSum] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,PRIMARY KEY CLUSTERED
(
[DocID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[SCANNEDDOCUMENTS] WITH NOCHECK ADD CONSTRAINT [ISCANNEDDOCUMENTS2] FOREIGN KEY([TopicID])
REFERENCES [dbo].[TOPICS] ([TopicID])
GO
ALTER TABLE [dbo].[SCANNEDDOCUMENTS] CHECK CONSTRAINT [ISCANNEDDOCUMENTS2]

On a test DB, I moved the clustered and nonclustered indexes to a secondary filegroup no problem, but it still shows to be stored in the primary filegroup. I read an article about having to create a new table in the secondary in order to move the images and text file group. Has anyone come across this?

Do I need to drop the clustered index and FK to move to a secondary filegroup?

Or

Do I create a new table into the secondary filegroup and then add the Clustered index and constraints?

View 4 Replies


ADVERTISEMENT

Moving Objects From One Filegroup To Another

Apr 25, 2000

I have my user defined objects created on the PRIMARY filegroup.
I have a secondary filegroup existing for the database.
How can I move all my user objects to the secondary filegroup from the
primary filegroup.
Info on this is appreciated.

regards
Sush.

View 1 Replies View Related

Moving Indices To New Filegroup

Mar 26, 2002

I have created a new filegroup and would like to move my indices there. Do I need to create a file for each index or can I just specify the filegroup in Enterprise Manager?

Thanks!

Ellen

View 1 Replies View Related

Moving ONE Table To A Different Filegroup Using T-SQL

Aug 29, 2002

I need to know if it is possible to move ONE table from an existing filegroup to another existing filegroup.
The answer I received to use ALTER database only modifies the file/filegroup name or changes the default filegroup.
Any assistance will be greatly appreciated.

View 1 Replies View Related

Moving Datafile To Different Filegroup

Nov 10, 2005

Hi,
In SQL Server 2000 how to move a secondary data file to different filegroup without removing and re-creating the secondary data file.

Thanks in advance.
-S

View 1 Replies View Related

Moving A Heap To Another Filegroup

Jul 20, 2005

I have a big table (heap)... well, not so big, I have a small serverand I want to spread access to it across several new disks dedicatedonly to that table.I known its possible to do that creating a clustered index with "ONfilegroup" option but I want to maintain it as a heap, is there anyway to do this without dropping indexes/references - bulk unload -create table - bulk load - create indexes?.

View 4 Replies View Related

Moving Table(s) To A Different Filegroup In SQL 2005

Oct 2, 2007

Is there anyway to move tables without any indexes to a filegroup?

View 1 Replies View Related

SQL Server 2008 :: Moving Into A Specific Filegroup

Jun 3, 2015

SQL 2008 R2

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.

View 0 Replies View Related

Partitioned View Broken After Moving Table To New Filegroup

Jul 20, 2005

I am using SQL Server 2000, SP3.I created an updatable partitioned view awhile ago and it has beenrunning smoothly for some time. The partition is on a DATETIME columnand it is partitioned by month. Each month a stored procedure isscheduled that creates the new month's table, and alters the view toinclude it. Again... working like a charm for quite some time.This past weekend I moved some of the first tables onto a new filegroup. I did this through Enterprise Manager, by going into designmode for the table, then going into the properties for the table andchanging the file group there as well as in all of the indexes. Nowthe partitioned view is no longer updatable. It gives the errormessage: "UNION ALL view '<view name>' is not updatable because apartitioning column was not found."I have extracted the DDL for all of the partition tables and comparedthem and they all look the same. I checked and then double-checked theCHECK constraints to make sure that they were all valid and they are.If I remove the tables that I moved to the new file group from theview, then it is once again updatable, but when I put them back in itfails again.Any ideas? If you would like samples of the code then I can send italong, but it's rather large, so I have not included it here.Thanks!Thomas R. Hummel

View 3 Replies View Related

SQL 2012 :: TEMPDB High Avg Write Wait Time On Data Files

Apr 15, 2014

I am currently investigating aa high avg write time ms issue (145ms) which seems to be only occuring on the tempdb data files.I have followed the recommended setup of TEMPDB in that

1. Data files = number of physical cores
2. Data files and logfiles are on separate partitions away from the other databases.
3. Tempdb is presized and no incremental file increases look like they are happening with frequency.

We have sharepoint 2012 setup on other sql servers and with TEMPDB setup following the same guidelines, with far more Sharepoint activity on a similary specified hardware which is why its confusing.FileIO auditing on the partitions themselves shows that the FileIO is very fast on the partitions that the tempdb data file which leads me to beleive that Sharepoint may be the culprit perhaps due to excess use of tempdb with operations taking a long time to resolve.

View 3 Replies View Related

Find How Much Of Partition Filegroup Contains Data?

Oct 7, 2015

I have partitions that I have filled with data. I am not trying to figure out exactly how much data the partitions contain, and therefore I will be able to see if any of them are close to hitting their autogrow conditions. If I were looking at a single unpartitioned table, then I could maybe look at the table properties to determine data and index sizes, and compare that to the size of the mdf file size, but for partitions, then I am not sure how I would query this information out. Any pointers on how this information could be queried out of the system?

View 3 Replies View Related

Move Data Between Datafile In Same Filegroup

Jan 28, 2008



Hi all

I've found this problem working with a VLDB, six month ago when I install the DBMS (Win2k3 x64+sp2, SQL 2k5 x64 +sp2, 4 dual core processor and 12 GbRAM) I've got 10 disk (actually ten LUN from a Storage Area Network), each 50Gb.
I've put TempDB and Transaction Log on two separate 50 Gb disk and put the database on 8 different data file on the 8 disk; I've created each datafile with a size of 50 Gb (autogrowth disable), so my DB has 400Gb space in it's datafile.
After a while the datafile began to fill and we decide to add a couple more 50Gb disk where I decide to put to new datafile; now my db is around 430 Gb and I've got this strange situation:


The first 8 datafile now are almost full of data, and obviously they can't growth since they already occupy the whole disk.

The two additional datafile are relatively empty (about 15 Gb each).

As far as I understand now each time that SQL should write to the databse it writes only on the 2 new datafile, and I fear that this can affect performance.
I'd like to reorganize the space in order to have 10 datafile, each with 43Gb of data, but I didn't find any instruction/tool able to move data between datafile.

Anyone has any hint ?

Thank you in advance for any suggestion
Stefano


View 5 Replies View Related

Filegroup Is Full - During Import Data

May 14, 2007

Hi,

I am getting below error while importing data in SQL 2005 Express:



"error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Could not allocate space for object 'dbo.HistoryLog'.'PK_HistoryLog' in database 'HistoryData' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.".
"



I have select :



Enable Autogrownth = Yes

Filegrowth = 1 MB

Maximum File Size = Unrestricted File growth



I don't know what else I am missing?

Please help

thanks

AA



View 8 Replies View Related

Balancing Size Of Data Files In Filegroup

Apr 26, 2008

Due to SQL's proportional fill algorithm I'd like to have the amount of data in my data files as close to equal as possible.

File sizes before:
dat 482,895 MB 0% free
2dat 436,927 MB 3% free

I made the max size of each file 600,000 MB and added a third file 3dat also 600,000 MB. I rebuilt all the clustered indexes (and nonclustered for good measure) and unfortunately the re-balancing wasn't quite right.

File sizes after:
dat 464,761 MB 77% free
2dat 443,234 MB 73% free
3dat 309,568 MB 51% free

I only have a handful of heap tables that take up <100MB total so they're not the issue. I did do an ONLINE index rebuild. I'm not sure if an offline rebuild would have been better. I will not be able to try and offline for a few weeks though as it's time consuming and I have other tasks I need to run on this test server now.

I did a FULLSCAN rebuild on any column statistics not updated by the index rebuild but that didn't help either.

View 3 Replies View Related

SQL Server 2008 :: Merge Moves Data Into Wrong Filegroup?

Feb 18, 2015

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.

This is my starting point:

boundary_idBoundary_valuePartitionNumber
11/09/2012 0:002
21/10/2012 0:003
PartitionNumberPartitionRowsFileGroupName
1799AdtLog_Archive_201208
2300AdtLog_Archive_201209

After I switch partition 1 to a staging table I run:

ALTER PARTITION SCHEME My_ps NEXT USED AdtLog_Archive_201209;

and

ALTER PARTITION FUNCTION My_pf() MERGE RANGE ('2012-09-01 00:00:00.000');

I expect the 300 records from the former 2nd partition to stay in AdtLog_Archive_201209, however I get this:

PartitionNumberPartitionRowsFileGroupName
1300AdtLog_Archive_201208
2310AdtLog_Archive_201210

How do I make sure that the data stays in AdtLog_Archive_201209 file group?

View 1 Replies View Related

SQL Server Admin 2014 :: Move Text Data From Primary To New Filegroup?

Oct 15, 2015

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

View 0 Replies View Related

Order Of Data Load And Index Creation / Move Indexes To Separate Filegroup?

Apr 15, 2015

We are running SQL Server 2014 Enterprise Edition (64-Bit) on Windows 2012 R2 Standard (64-Bit).

1. When to create indexes, before or after data is added? Please address Clustered and Non-Clustered Indexes.

2. To move indexes to it's own filegroup, is it best to create the NON-Clustered Indexes on the separate filegroup with code similar to the example below?

CREATE NONCLUSTERED INDEX IX_Employee_OrganizationLevel_OrganizationNode
ON HumanResources.Employee (OrganizationLevel, OrganizationNode)
WITH (DROP_EXISTING = ON)
ON TransactionsFG1;
GO

I have read the following links that states that if you create the Clustered Index on a separate filegroup, it would also move the base table to that particular filegroup. (So I take it that you ONLY can move NON-CLustered Indexes to a separate filegroup.)

Placing Indexes on Filegroups:

[URL]

By default, indexes are stored in the same filegroup as the base table on which the index is created. A nonpartitioned clustered index and the base table always reside in the same filegroup. However, you can do the following:

• Create nonclustered indexes on a filegroup other than the filegroup of the base table.

Move an Existing Index to a Different Filegroup:

[URL]

Limitations and Restrictions

• If a table has a clustered index, moving the clustered index to a new filegroup moves the table to that filegroup.

• You cannot move indexes created using a UNIQUE or PRIMARY KEY constraint using Management Studio. To move these indexes use the CREATE INDEX statement with the (DROP_EXISTING=ON) option in Transact-SQL.

View 1 Replies View Related

SQL Server Admin 2014 :: Primary Filegroup For System Objects / Secondary For Data

Jul 27, 2015

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 (!?)'.

View 2 Replies View Related

SQL Server Admin 2014 :: How Data Writes In Multiple Secondary Files If There Is No Filegroup Created

Nov 12, 2014

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.

View 2 Replies View Related

Moving Data

Oct 23, 2007

 I have 2 databases on one server that I want to consolidate into one database. I'm just learning SQL Server 2005. What is the easiest way to move my 3 tables from one database to a new one on the same server? Do I have use SSIS to do it, or can a simple query be written? I'm new so please be a little detailed in your answer. Thanks in advance for any comments. 

View 2 Replies View Related

Moving Data Between 6.5 And 7

Aug 3, 2000

I have a scenario where I need to refresh a database that is in 7.0 (converted from 6.5 database) from the original database. Is there an easy way to do this. I have tried creating a DTS package but the data never seems to make it accross.

View 1 Replies View Related

Moving Data From 6.5 To 7.0

Feb 16, 1999

Hello,

I'm having problems using the update wizard to move data from 6.5 sql server
(on another machine) to a 7.0 server sitting out a PDC. The wizard dies (and
passes me over to Dr Watson) when login fails for the 6.5 machine.

I am sure I have the right pasword (I've tried variations as well) and have updated the
hosts file so that the machine is known by it's name (I think one of the FAQ answers
suggested that)....

What other possibilities are there for moving the data ? I looked at bcp, but that seems
a rather long winded route (the data contains time stamps so I suppose there will be
a problem reading them in on the 7.0 side) ...

Many Thanks,

Paul.

View 1 Replies View Related

Moving Data From One DB To Another

Sep 4, 1998

Hi all!
What is the best way to move selected data from one database to another using SQL 6.5?

View 1 Replies View Related

Moving Data

Sep 6, 2001

what would be the best way to move 59 million rows from one table to another. The table has no constraint, but has has three indexes. The table has only four columns. It will be going from SQL 2000 to SQL 2000.

Thank You

View 2 Replies View Related

Moving Data From One Database To Another

Dec 5, 2006

Hi all,
I want to transfer all the data from one of my Ms Access tables to SQL Server table, using C#.
How can i do it?
Thanks in advance,
J.Jasmeeta.

View 6 Replies View Related

Moving Data From One Database To Another

Feb 2, 2007

Hi all,
                        I have an Ms Access table and a MsSql table. I am running a windows service in my localhost where the data from Ms access table will be copied to Ms sql table for every one minute. Before copying the data, the Ms sql table will be flushed inorder to avoid replicates.
                       Now i want to copy only the latest records updated within 1 min in Ms access table, to Ms sql table.
My Ms access table
Name        Id
jas            100
meena       101
viji             102
 
My Ms sql table
Name        Id
jas            100
meena       101
viji             102
 
After 1 min, say  2 records are added to my Ms access table like,
Name        Id
jas            100
meena       101
viji             102
bhuvana     103
pinky         104
Now i want to insert only the latest records from Ms access to Ms sql like,
Name        Id
jas            100
meena       101
viji             102
bhuvana     103
pinky         104
how to do this? thanx in advance.
Jasmeeta. 

View 4 Replies View Related

Moving Data Across Networks

Oct 10, 2007

I am wondering the best way to go about a task I have been assigned.  We have two similar websites but each is located on a different network.  One network is secure so it cannot be accessed on the normal WWW.  The secure network will contain the master database.  I need to write a program or do something with SQL server to retrieve all records from the WWW site and get them onto the secure database.  I also in the future will need to update records from the WWW site if they have been updated.  What is the easiest way to move data from one network to the other when I cannot connect to both databases simultaneously?
 Thanks,
Matt

View 5 Replies View Related

Moving Data Between Databases

Jan 5, 2006

I have a web app that has been regulated to a disconnected PC. It's runing IIS and 2.0 with sql server express, but no connectivity. I have changes that are made to some of the data in the db (data, bot schema). There is one particular table that I cannot overwrite, and must extract the data.
What methods are available to do this swap of data between databases? I was thinking of doing something like this:
Track last date that remote db was updated. Upload updated database into data directory, loop through records for all affected tables, any date that was past logged date then update the record if it exists or insert new record, and then loop through the remote db and delete this records that dont exist in the updated db.
This seems intensive and slow - especially as the tables get bigger but I can't think of another solution that can be done by a user using sometime of web interface.

View 1 Replies View Related

Moving Data From One Sql Server To Another

Jul 5, 2002

Our current sql server 7 is overloaded and I have been asked to move some data to a new box which is to be acquired. My query is as follows:

1what should be the ideal spec of a sql server to allow for windows 2000 in future. And how do I go about moving data from one to the other. I am new to sql and this is my first assignment.

Thanks

Eve

View 1 Replies View Related

Moving Data From One Sql Server To Another

Jul 5, 2002

Our current sql server 7 is overloaded and I have been asked to move some data to a new box which is to be acquired. My query is as follows:

1. What should be the ideal spec of a sql server to allow for windows 2000 in future.

2. Is there any kind of monitoring agent available to see how heavily loaded the server is and what load each application/database is creating.

3. And how do I go about moving data from one to the other.

4. What do I do at the clients end to ensure they are able to access their database.

I am new to sql and this is my first assignment.



Thanks

Eve

View 1 Replies View Related

MOVING DATA AND LOG FILE

May 25, 2001

I am trying to move a data and log backup file from my C drive to another drive to free my C drive. Does anybody know the easiest way to do it? Thanks.

View 6 Replies View Related

Moving Data From One Table To Another

May 27, 1999

Hi all,

This is (probably) a simple question, but I'm new to SQL Server scripting.

What I want be able to do is move data from one table in a database to another table in the same database, once one of the fields (a date field) reaches a certain value.

Specifically, we are inserting rows into a table that are stamped with an insert date and an expiry date. When the expiry date is reached, we want to move the applicable row from the original table to an identically structured table.

Ideally, we want this to be a script that is run daily.

Does anyone have any ideas or examples that we could use.

Any help is much appreciated!



Tim

View 3 Replies View Related

Moving Data Between Tables

Oct 15, 2001

What is the best method to move 1.500.000 rows from one table to another ??

Thanks,

Charles Roberto Boeing Mari

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved