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?
My boss has asked me to look into this and I haven't been able to find any information on the web. I hope someone can answer this for me. We currently have a single database that is storing all the user information and transactions. Within the same database we are also logging different types of user activity. If both these tables are heavily used, would it make sense to separate it into different database, one for data and one for logging? Is there any pro or cons of having more than one database? Any opinion or suggestion would be greatly appreciated. I'm the closest thing they have to DBA and I'm really new to this. Thanks.
Hey 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!
I have 4 tables with the respective amount of records 1) 6755 2) 2021 3) 2021 4) 355
They all have the same columns. However, they need to be seperate, or at least when I query them. I'll be accessing this database via the web. i was first afraid that a large database would cause major slow down when accessing the db. So I broke it up into 4 tables. If I combined all 4 tables into one large table and just had a column that differentiated the 4, how significant would be the change in speed when accessing the table? It's not a big deal to keep them seperate, its just that when I have to add or remove a column from one table I have to remove it from all the tables. Furthermore, I'm using a module from DEVEXPRESS, don't know if anyone has heard of it, but when you use a gridview, it loads up the entire table even though your paging (which I think is retarded), so for that reason I was afraid it would slow up my access to the db. Any thoughts?
Does anyone have any idea how to syncronise two SQL identical tables acrossa network, between servers?We want to run our website from a SQL tables on the same server, but set upso that if the main SQL tables across the network change, or the tables onthe web server change, the two seperate servers willsyncronise/update/trigger updates to the tables on each other?We are running SQL server 2000.AHAJohn
Hi folks, I've got a fairly easy one here me thinx. I'm looking for the best way to compare two entire rows from two seperate tables which have the same primary key.
Here's the basic lowdown:
I get some data every night from an external system (cache') via DTS. This is more or less my "master" data which drives my application. I have just been informed of an interesting constraint. If any of the data changes in the external system, those changes do not become effective until the first day of the ensuing fiscal quarter.
I'm solving this by running the DTS as normal, but populating a "duplicate" table which I will evaluate once per quarter for any changes. This is also the preferred solution because they would like to see a snapshot between current cache' data and the data my application is currently working with.
So, I end up with two identically structured tables. both tables have the same primary key and can be linked by an id field with relative ease. What I would like to do is a full row comparision once this join is established.
Right now I explicitly check the value of each column. ie:
WHERE t1.field1 <> t2.field1 OR t1.field2 <> t2.field2 OR t1.field3 <> t2.field3 ... etc
I'm hoping there is something buried in TSQL that I just don't know about that can handle comparing entire rows and tell me if they're different. Or perhaps there's another approach all-together.
I have two databases in sql server. I'll call them DB1 and DB2. I have a table in DB2 that needs to form a relationship with a table in DB1. When I attempt to add a relationship I only see tables in DB2. Can this be done?
Hello. I just created separate tables for each of my categories and wanted to know how to return them all to be viewed on one page using the SQL Datasource (or whatever) This is for user accounts. I just need to know that part. Sincerely, Computergirl
I want to produce some output for Mainframe application. For that I want to insert values from multiple table as source to a single column (huge in size)of a different table (Destination table). There may be same related records in all of the source tables with the primary key. When I export values from the source tables , each related records should be insterted to the destination table's field (multiple entries for each table). Please advise.
I am workong on this Pivot select. At it is last part, I want to find a way to re-arrange the column order. Here is the test data:
if OBJECT_ID ('tempdb..#temp') is not null drop table #temp go create table #temp(b_date varchar(15), Gender varchar(5), Admin_date varchar(15), S_ID int, TEST_NAME varchar(5),SCHOOL_YEARvarchar(5), PART_DESCRIPTIONvarchar(15),Test_Score int)
[code]....
But the output requries the last couple columns to be SCHOOL_YEAR, TEST_NAME, i.e. they come after the scores.I thought the easiest way will be to select into #temp2, then extract from there. Even that, I couldn't get that to work with the select pivot.
Hi,I have following data,India 91USA 01UK 44Like this, I have 100 Records (Rows) with 2 Column DataNow I want to have report like belowIndia 91 USA 01 UK 44Pakistan 92 .....How can I do this?Nilesh
Can the items, i.e. reports within the SSRS Report Designer Solution Explorer be arranged in a custom manner instead of being sorted alphabetically only?
i am trying to build a database that contains a coloumn of phone number but i reallly dont know where and how i will be able to give a specific format for phone numbers.. or anything else any common type of information that may have a format..
Will i use Visual Web Developer or SQL Server Management or whatever else to arrange my phone number coloumn?
I 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.
I 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.
If 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
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 ?
i 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 ??
Is 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
Is 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.
I 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.
We 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?
Hey 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?
