Moving 100+ Tables To New Filegroups?
Jan 29, 2008Hi!
When you have many tables with data
- is there an easy way with SSIS to migrate/move to another filegroup?
Greetins
ADVERTISEMENT
Moving Tables Between Filegroups
Jul 5, 2007Does anyone know how to move tables between filegroups in 2005?
Cogito ergo spam -- I'm pink therefore I'm Spam
Merge Tables And FileGroups
Dec 6, 2005Hey all
For performance reasons i want to move the Merge Replication tables in a DB of mine (mainly MSMerge_Tomestone and MSMerge_Contents) to a seperate filegroup ... it's a heavy transactional DB and the index fragamentation rate is quite phenominal!
Re-arrange Of Tables (putting Them In Seperate Filegroups)
Jan 28, 2008
Hi!
I have a big database which need some separation
in multiple filegroups.
Therefore I plan to move tables to new filegroups.
As I can understand this must be done by
dropping the clustered index and
recreating it on new filegroup, then data will move.
1. best way to get a list of all clustered indexes (sys.indexes)?
2. how to get index scripts? (using SSMS, right click is manual task, but I need scripts for than 100 tables)
3. when having the scripts it should be easy to replace filegroup part
4. after movement: just shrinking old files?
Thanks for help ;-)
Moving Tables
Oct 30, 2002Is there a way you move tables from one filegroup to another without deleting and re-creating them?
Thanks
Moving Tables
Jan 31, 2006Hi,ms-sqlserver 2000 sp4 - entrerprise editionCan you help me on this issue. I would like to move some usertables(about 100 MB) to new file group. Can you tell me what I need todo?Thanks in advance
View 2 Replies View RelatedMoving Data Between Tables
Oct 15, 2001What is the best method to move 1.500.000 rows from one table to another ??
Thanks,
Charles Roberto Boeing Mari
Moving Tables & Diagrams To Different DB
Dec 31, 2007HELLO
I need to know how can I move my tables and diagrams(from a database) to different database. Any suggestion will appritiated
Many thanks
Using Updatable Views When Moving Tables From One DB To Another?
Dec 3, 2007Hello,We are researching whether the following scenario would be possible:In an upcoming application release, we have to move some tables (Logtables, look up tables, and a couple of secure tables) from database Ato database B.Rather than wait and do everything all at once, and have no roll-backplan should it fail, we'd like to create database B now, and startmoving those tables one by one over to it.To ensure compatibility with the existing code-base, we'd like todetermine whether we can use updatable views to allow the current codeto continue to run against the existing DB.Essentially, we would do this:Given a table named LogTable In database A, we'd copy all ofLogTable's data to database B. (We'd look at the transaction log tocopy any changes made on rows modified after copying started.)Then, we'd turn off the site for a few moments, and:In database A, we'd rename LogTable to LogTable-Old, and create a viewcalled LogTable which points to DatabaseB.dbo.LogTable.When we turn the site back on, updates and selects to LogTable wouldphysically pull from database B from now on.I have already verified that performing selects and updates against aview that refers to another physical database actually does work inSQL 2K5.My question is are there any pitfalls or things we should be aware ofthat anyone else has experienced trying to do something like this?Does it sound feasible?Thank you,Josh
View 3 Replies View RelatedMoving Data Tables Between SQL Servers
Jul 20, 2005I have user that we just migrated his Access database to SQLServer. All went well with the migration, but then he came up withanother requirement to be able to replicate the database to a localSQL server living on the hard drive of a laptop. Before the migrationhe just copied the entire Access databse to the lap top.I tried using the Copy SQL Server Objects Task to move thenecessary tables from the production server to the laptop, but noticedit doesn't copy over the table Indexes/keys identiy fields etc. Iended up backing up the production database and restoring it to thelaptop database, but wondered if there is any way to move the tables,with their properties from one server to another? I know I can setup the backup process to run as scheduled, but the problem is the dataneeds to be moved on an irregular time table. I thought about justwriting code on the remaining Access front end to empty the localtables and then query the data from the production side to reloadthem, but I'm sure there's an easier way.Any suggestions would be appreciated.Thanks,Tom
View 1 Replies View RelatedMoving Tables Between Databases And Setting Alias
Apr 27, 2006Due to bad programming techniques and legacy databases, my company has a large database with all the tables in it. I would like to start moving these tables out of the database and into more appropriately named databases.I was wondering if there was a way to set an alias up so that, when the table is moved, there will be no problems with out code still trying to access the table from the old database.Maybe an example is the best way to explain this. Lets say we have a DB_Company database with table T_Customers, T_Suppliers, etc.Now, as the company grows, we decide to create a dedicated database for all the customers. So we create a database DB_Customers and move the T_Customers table into it.The problem is that they may be some code that is still trying to access the DB_Company.T_Customers table. Is there a way of setting an alias up so that, when someone tries to access the DB_Company.T_ Customers table, he is automatically redirected to the DB_Customers.T_Customers?Hope that's not too confusing :-(Jag
View 2 Replies View RelatedSQL Server 2008 :: Moving Tables To New File Group
Mar 16, 2015I'm presented with an issue where by I need to reclaim a fair bit of unused space currently sat in the primary data file for my database. I don't want to run DBCC SHRINKDATABASE as we all know this could potentially have a some serious negative effects relating to index fragmentation.
So, how do I get the free space out of the data file? - I've decided to:
1. Add new new file group
2. Add a clustered index for all tables on the new file group
3. Shrink the primary file group as much as possible (hopefully giving me the free space back)
4. Drop the newly created clustered indexes for all tables
There are no clustered indexes currently for any of the tables!, so me temporarily creating/dropping one shouldn't be an issue. Are there any other ways I can get the free space back to the OS?
SQL 2012 :: Free Space After Moving Tables From One File Group To Another
Aug 13, 2014Recently maintenance was done removing some tables from the original filegroup in one drive of our SQL Server 2012 Standard Edition 64bits to another created on a separate physical drive. I was expecting the full amount of data moved to the secondary filegroup to show up as unused on the primary filegroup but that doesn't seem to be the case. Do I have to do anything after the move to release that space, not to disk, but to the database as unused?
View 2 Replies View RelatedMoving New Data Into Dimension Tables Automatically Using Integration Services
Jul 25, 2006I have an Integration Services package that loads new data into tables that are dimension tables wi my cube. The same situation exists for my fact table. If I perform an "Analysis Services Processing Task" for the dimensions ,cube and measures, will that move the new data into my cube or do I need to perform the "Dimension Processing Destination" data flow task prior to this? Is the initial processing task good enough?
thx,
-Marilyn
Filegroups
May 25, 2000I was told to move tables to new filegroups by placing the clustered index on the filegroup and the table would
follow. There are times when I see tables listed on the new filegroup but still listed also on Primary. My goal is
to have only system tables on Primary. How can I get a table to totally leave the PRIMARY group?
There seems to be a system index on the table that was not created by me.
FileGroups
Jan 23, 2001I have databse that was created when I got here and the database was created with 3 file groups. The tables in the database are spread out over the 3 file groups.
How can I find out which table belongs to which file group.
Thank You,
John
FileGroups
Oct 8, 1999If I'm running RAID 5, is it still good practice to split system and user data onto separate filegroups ? .....
On separate disks ?
Thanks in advance..
Any comments welcome ! Peter
Filegroups ?
Oct 8, 1999If I'm running RAID 5, is it still good practice to split system and user data onto separate filegroups ? .....On separate disks ? Thanks in advance..
Any comments welcome ! Peter
Filegroups
Feb 8, 2006Hi,
I was wondering if there is a downside of placing individual tables into their own filegroup (i.e., 1:1). This would seem to allow me to continue to backup entire databases but give me the flexibility to restore at a table level if necessary. I ask this because the number of filegroups allowed per database has increased from 256 in SS2000 to 32K in SS2005. What kind of headaches could I get into if I follow this type of design ?
Thanks,
Mario
Filegroups
Sep 22, 2005i want to obtain information about all the filegroups for every database. is there a special command to obtain this information like the dbcc sqlper or something similar ??
View 1 Replies View RelatedFilegroups
Oct 8, 2007hi all,
What is the purpose of files?(primary secondary,transaction log ). what is the use of these files?
Filegroups In Sql7
Jul 21, 2000Is there a way to list all the tables in each of the filegroup
in a database. I have a database with 7 filegroups and would like
to have a list of tables in each filegroup
Sp_helpfilegroup gives you list of filegroups and the names of files
Filegroups In SQL7.0
Jan 23, 2001Is there any way in which I can move an objects/ data to another
filegroup using transact sql?
I have created a table such that the data is on a secondary filegroup.
But the table has image and text data and this data is going to
the PRIMARY filegroup.
I tried chnging the filegroup for the text data but this property but does not change(I did it through EM).
As a result, the disk which contains the primary filegroup is getting filled up.
I have configured my database such that the primary file group is on one disk and the secondary on another and the log on the same disk as the primary filegroup.
I have given lesser space to the primary file group and more space to the secondary filegroup.
help on this is greatly appreciated because my system is rapidly growing with respect to data.
thanks and best regards
Sush.
Files/Filegroups
Sep 10, 1999I was under the impression that if you are using multiple files in your filegroup to store your data that the data should be written evenly across the files.
I have 5 files and the data is being written to only one file which is growing rather large, while the other files are still at 5 - 7 MB. They are all marked for Automatic Growth at this point, but only the 1 file is growing.
Is there something else that needs to be done to enable this? BOL left me with the impression that this is automatic.
Backup With Filegroups
Sep 14, 2006We are going to start using fielgroups to move some indexes to a different RAID. Simple ? is, when you do a full database backup, that backup alss contains the new filegroup?
View 2 Replies View RelatedFilegroups In Sql Server...
Feb 9, 2006hi all,
i have a problem with creating filegroups in sql server...i just want to copy the names of the columns from one table into my filegroups...
this is my problem : i m writting the following code in tsql:
SELECT * INTO Sales FROM Sales1 WHERE 0=1 ON [MyFG1]
Can anyone please help meeee???? i'd like it to work this way, to copy only first column names from Sales1 to Sales but in this filegroup.
if this is not the write way...then can anyone please tell me the right syntax and the way to copy just the names into a filegroup?????
thanx in advance.
Regards.
Filegroups Question
Jul 18, 2007Hello everyone -
Currently the setup is a single filegroup sql machine
with each company as a new database in a single system.
Would breaking the companies into thier own filegroups
be benefitial?
or perhaps creating a instance of SQL for each company be a better approach?
thoughts / ideas would be appreciated
thanks
tony
Files && FileGroups
Oct 12, 2007Hey guys I have a VLDB and I would like to point or move several tables to another filegroup.
1). Add a filegroup to the database
ALTER DATABASE dbname
ADD FILEGROUP filename_table
go
2). Add a file to the file group
ALTER DATABASE dbname
ADD FILE
.....
TO FILEGROUP filename_table
go
If a table is already part of the primary filegroup:
Can I change it to another filegroup?
When changed to the another filegroup will it move the whole table to the other filegroup or just start to write data to the newly created filegroup?
Indexes And Filegroups
May 19, 2004I'm fishing for some advice here.
I'm porting an old Access 97 application to SQL Server 2000. The Access app uses 9 separate files for a series of linked tables (one table in each file to get around the 1Gb Access file size limit). The tables vary in size form 2Mb to 800Mb so the whole data set weighs in at around 6Gb.
I've prepared tables for the data in SQL Server and assigned each table into its own filegroup each of whihc has its own file so I can separate the data out and also keep an eye on the amount of data.
My next problem is the indexes. The Access tables don't have primary keys. My SQL tables do (Large Integers) but each table is mainly indexed on an account number which is an 11 char alphanumeric. This is non unique so I can't use it as a primary key.
At present I have all the table indexes in the PRIMARY filegroup (which in tunr just has the default MDF file in it)
I've built a small version of the DB for testing various triggers and new views and the DTS import packages and the indexing for the 300Mb of data I have now is obviously fairly quick. I am wondering if I should split the indexes out into each of the separate MDF files that I am storing the table data or should I split the indexes into their own files?
I want this thing to be fast. The VBA app that will be plugging into the DB has a huge amount of code and currently struggles especially when several dozen people are all connected to the same tables.
So any advice? Indexes in separate files? Or in with the data? or all together in one index file? Any performance impacts I should be aware of?
The DB is running on its own dedicated box. Its not huge 1Gb Ram, 30Gb drive and a 3Ghz P4. But given that it isn't running anything else it should be up to the job. It should certainly be faster than the current shared drive that the access app runs from.
many thanks
Steve
Question With Filegroups
Jul 23, 2005I'm trying to tweak our DB as much as I can. We have the full applicationdatabase sitting on a RAID 10. Logs, tempdb, etc.. are on separate arrays.I read some articles about making separate filegroups. Of course, they allsay to move them to different arrays, which because of budget is notpossible right now. I also read articles that say to put large tables in afilegroup then add another data file to the group. This allows SQL Serverto use multiple threads to access the tables. Therefore, to my question.If I split our database up into multipele file groups on the same array,could I see a performance gain. Would the database be able to issuedifferent threads for each group when a query is run and improve overallperformance.Thank you,Adam
View 2 Replies View RelatedQueues And Filegroups
Apr 19, 2006Hi
We can create a queue on a filegroup for performance reasons however what about the sys.transmission_queue which could have messages backing up when the target is down.
What are your thoughts.
Cheers
Log Shipping && Filegroups
May 7, 2008Hi Guys,
I need some help for my Log Shipping database please.
I have a database X on the production server and on the log shipping standby server (Read-only). The logs on the production server is shipped to the standby server. The standby server restores the logs through a schedule job.
Now what happended is couple of days back we created another filegroup (Secondary) on the production server for the database X to keep our history separated from the most current data. When we created the secondary filegroup our logs on the log shipping server cannot restored. We investigate the problem and found that there is no secondary filegroup attached to our database X on the log shipping server, which is obvious.
My question now is there a way we can create a secondary filegroup for the database X on the log shipping server without breaking our existing log shipping? Do we must restore the full backup of the database X on the log shipping server in order to keep the transaction logs applied on the log shipping database X?
Please help.
Thanks.
Moving Tables/Procedures From SQL Server Express To SQL Server
Mar 31, 2008My searches have come up blank, so I'm hoping someone else can point me in the right direction.
I'm done some development in SQL Server Express, working on tables and procedures. I've now got to move those tables/procedures into SQL Server and I don't know of an easy way to do this. Are there any tools I can take advantage of that would allow me to get all these objects moved? The only way I can see to do it is to move objects over one-by-one. This would be really time-consuming, and I'd like to avoid it if possible.
I'm positive I'm not the first one to try this, but what should I be searching for? Are there tools/procedures I should be looking for?
Appreciate any help I can get.
Thanks.