I want to change the filegroup of a table. While we can do this in enterprise mgr, I would like to accomplish this in transact sql. We have a new configuration where the filegroups are located on different disks and to take advantage of that, I want to move the heavily accessed tables onto different groups. Example: table emp needs to be moved from filegroup Primary to file group Secondary. please help...
CREATE DATABASE Dummy ON -- Primary file contains Startup information of the database PRIMARY ( NAME = PrimaryLog, FILENAME = 'D:primary.mdf', SIZE = 5MB, MAXSIZE = 500MB, FILEGROWTH = 20MB ), -- Holds The Data of LookUPTables,TPProfile,CRM. ( NAME = Data, FILENAME = 'D:Data.ndf', SIZE = 5MB, MAXSIZE = 500MB, FILEGROWTH = 20MB ), LOG ON -- Stores The Log Information used To Recover The Database ( NAME = Log, FILENAME = 'D:Log.ldf', SIZE = 5MB, MAXSIZE = 500MB, FILEGROWTH = 20MB ) Go
After this I want to create table on Data .
CREATE TABLE Sample (
No INT ,Name VARCHAR(30) , Department VARCHAR(4000) NULL
CREATE DATABASE Dummy ON -- Primary file contains Startup information of the database PRIMARY ( NAME = PrimaryLog, FILENAME = 'D:primary.mdf', SIZE = 5MB, MAXSIZE = 500MB, FILEGROWTH = 20MB ), -- Holds The Data of LookUPTables,TPProfile,CRM. ( NAME = Data, FILENAME = 'D:Data.ndf', SIZE = 5MB, MAXSIZE = 500MB, FILEGROWTH = 20MB ), LOG ON -- Stores The Log Information used To Recover The Database ( NAME = Log, FILENAME = 'D:Log.ldf', SIZE = 5MB, MAXSIZE = 500MB, FILEGROWTH = 20MB ) Go
After this I want to create table on Data .
CREATE TABLE Sample (
No INT ,Name VARCHAR(30) , Department VARCHAR(4000) NULL
Recently, we had some tables inadvertantly dropped from a 20+ GB data warehouse in the middle of one of our load cycles. In order to avoid restarting the load, we had to make restore a copy of the database from a full database backup and then manually move in the dropped tables using DTS.
This ended up being a painful process, and I am looking to avoid it by restructuring the database into filegroups so I can mimic the table restore function from 6.5.
I am looking for pros, cons, gotchas, and best practices regarding splitting a MSSQL 7 database into multiple filegroups. We would be including 1 or more tables and all of thier indexes in each filegroup. Specifically, I have the following questions:
1. All the literature I read mentions the need for doing transaction log backups and restores if I utilize filegroup backups. Is this really necessary? We have turned off transaction logging for performance reasons, and since we completely control the addition of new data into the db.
2. Are there implications for disk read performance involved with filegroups? Our data resides on an EMC disk array. I feel that we'll most likely have between 20 and 30 filegroups.
3. If we decide to move a table (and its indexes) between filegroups, is the process as simple a dropping the clustered index and recreating it on the new filegroup (then dropping and recreating the n-c indexes)?
Any information y'all can post would be greatly appreciated.
I have approximately 400 - 600 tables that need to have their filegroups changed on a new SQL 2005 installation. In 2000, I could go the table properties in Enterprise Manager and change the filegroup, but on 2005 Management Studio, I can view (but not change) the table properties.
I realize that I can create the table on the other filegroup using a new name, move my constraints and indexes to this new table, copy the data over, drop the original table, then rename the new table to the name my application is expecting. But that could take me weeks (especially with such a time-consuming process)! I don't mind the process being so slow, but I do mind it being labor intensive.
Does anyone have an idea how I can automate this process (or at least make it as easy as it was with Enterprise Manager?)
Hi,I want to find out in my SP the filegroup associated with a particulartable? I was not able to establish the relationship between sysobjectsand sysfilegroups table. Is there any other way to find filegroup of atable in T_SQL?Also lets say there are 3 dtatfiles in a filegroup 'FG1' and table'xyz' is created on filegroup 'FG1' - Is there any way to find out thedatafile's names on which table xyz's data is scattered?Thanks in Advance,Subodh
I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem....
I need to extract data from DataFile table, here's my query:
select * from dbo.DataFile where DF_SC_Case_Nbr not like '0000%';
Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one.
Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)?
I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:
Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.
I have some code that dynamically creates a database (name is @FullName) andthen creates a table within that database. Is it possible to wrap thesethings into a transaction such that if any one of the following fails, thedatabase "creation" is rolledback. Otherwise, I would try deleting on errordetection, but it could get messy.IF @Error = 0BEGINSET @ExecString = 'CREATE DATABASE ' + @FullNameEXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'CREATE TABLE ' + @FullName + '.[dbo].[Image] ( [ID][int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded] [datetime]NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'ALTER TABLE ' + @FullName + '.[dbo].[Image] WITHNOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON[PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorEND
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.
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?
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.
I'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
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.
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?
