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 (
NAME = PrimaryLog,
FILENAME = 'D:primary.mdf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 20MB ),
(
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
)
ON Data
GO
it shows invalid filegroup DATA specified
what may be wrong
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
)
ON Data
GO
it shows invalid filegroup DATA specified
what may be wrong
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
)
ON Data
GO
it shows invalid filegroup DATA specified
what may be wrong
Hi
I am using SQL Server 2005 Developer Edition.
I want a list of the following things from the database: -
Table Name , FileGroup Table resides on
Table Name, Index Name, FileGroup index resides on
To put it simply, consider the following example:-
Lets say I have a table XYZ in my database created on Filegroup F1. It has a PK PK1 nonclustered index on Filegroup F2.
List1
-------
XYZ F1
List2
---------
XYZ PK1 F2
Please do not tell me of sp_help <table> option
Regards
Imtiaz
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?)
Paul Teal
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
View 1 Replies View RelatedHi All,
If the column names are changed , is there are a easy way to change the referred columns in the SP's. What I am doing is going into each SP and replace the old name with the new name.
Thank you for any help on this.
Necessity is the mother of all inventions!
Hi,
Just want to ask that if it is possible to change a table ownership?
thanks
Hi there,
Its probably easier to draw this problem than describe it, so here goes:
I have a sales forecast table (A,B,C are products [PRODUCT], the dates refer to the month for which the forecast is [FORECAST_DATE], and the integer is the forecast sales qty [FORECAST_QTY])
A 01/03/2004 30
B 01/03/2004 28
C 01/03/2004 24
A 01/04/2004 11
B 01/04/2004 09
C 01/04/2004 41
I need to convert the table into a more sensible format, like this:
(NB ...Dots are just there to help with formatting - basically I'm talking about a field for FORECAST_03_2004, FORECAST_04_2004 etc etc)
........ 01/03/2004....01/04/2004
A..........30..................11
B..........28..................09
C..........24..................41
I'm really no t-SQL guru, and I can't seem to get any joy out of BOL. It must only be a tiny bit of code. Can anyone help?
Thanks very much
Sam
Hi- apologies for asking a stupid newbie question, but I'm really stuck atthe moment. I need to change table ownership.I've got an asp script which is looking for a table owned by the dbo role,however the table was created under a different ownership. I understand theproblem, and almost understand the solution, but I can't seem to get all theway.THE PROBLEM (using [server].[database].[owner].[table])[mgbsvr1].[dnn].[dnnadmin].[aspsearch]needs to be[mgbsvr1].[dnn].[dbo].[aspsearch]I looked up the books online and found this syntax:sp_changeobjectowner [ @objname = ] 'object' , [ @newowner = ] 'owner'But I can't see how to use it, nor more importantly, where I should use it.It won't work in the query tool in Enterprise Manager. If I need to create ascript (which I've never done before), how do I execute the script?All help deeply appreciatedManning, Sydney
View 3 Replies View RelatedHi All,I am trying to change column name on an existing table. I am using SQLServer 7.As the table is quite big, it is taking quite long time to do it.By the way I could change the column name only through the EnterprizeManager.Is it possible to change the column name using SQL script?Why the change of column name will depend on the size of the table?Thanks for your answer.-Mokles
View 4 Replies View RelatedHello,
I created a package using the import/export wizard in SSIS, that loads data from one database to the other. I am trying to find out how I can add and remove the tables that were originally selected when the package was created. I opened the package in BIDS, and I could not find that particular option. I know you can do this in 2000/DTS...
Any help would be appreciated...
Thank you,
David
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.
Thank You,
John
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
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
Hi,
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
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 ??
View 1 Replies View Relatedhi all,
What is the purpose of files?(primary secondary,transaction log ). what is the use of these files?
Hi All,
I want to know what will be the impact of changing the primarykey on a table which already has a lot of data.
For example, column A is unique, primary key. I want to make column B as unique, primary key.
Can I do that? What will be the impact on database performance?
Thanks
Sri
Changing datatype in a table used for merge replication ?
I'm trying to change the data type on a column in a table that is used for a merge-replication with another (identical) DB.
I get this error:
Cannot alter the table '[Tablename]' because it is being published for replication.
I've tried to remove that specific table from the publication (Publication properties -> Articles tab) so I can change the data type and then put the table back into the publication, but I can't (probably because it's a MERGE rep.)
Any ideas ?
/CN
Hi,
Is it possible to change any fieldname of an existing table?I mean to say
by TSQL statement.We know that we can alter the data type and width etc.
But I haven't got any info about filedname change.So if it is possible
Please help...
And Is there any TSQL command to alter multiple columns in a single statement?
Please help...
Thanks!!
Joydeep
Dear All,I'm trying to alter the name of several columns' in a table which gets created in a stored procedure.trying to use:exec sp_rename '#tblBd.week1', '2007_18', 'COLUMN'I get:Server: Msg 15248, Level 11, State 1, Procedure sp_rename, Line 163Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.There is no mistype, the table name and column name are correct.Can temp table's column names not be altered?If yes, how?Thanks in advance!
View 7 Replies View RelatedI have a table that looks like this:
ID Type
123 Phone
123 Meeting
123 Phone
and I would like the data to look like this
ID phone Meeting
123 2 1
How do I do this?
I'm new to mssql.
I need to change the size of a column of a table from char(255) to char(500). I used the line:
alter table table_name alter column column_name char(500)
When I run that command, I get a message that it was sucessful. However, when I try to enter data into the changed column, the number of characters I can enter is still 255. I check the information schema for the column and the 'character_maximum_length' field is 500.
What is the problem here? Is the maximum allowable length for char 255? How can I get a column to have 500 characters?
This subject has been posted several times, but I haven't seen a goodanswer.Problem:I want to change the order of the columns in a table using T-SQL only.Explanation:After running your code, I want to see the following table...CREATE TABLE [dbo].[TableName] ([First_Column] [int] NULL ,[Second_Column] [varchar] (20) NULL) ON [PRIMARY]look like this...CREATE TABLE [dbo].[TableName] ([Second_Column] [varchar] (20) NULL ,[First_Column] [int] NULL) ON [PRIMARY]Limitations:Don't post if your post would fall in the following categories:1. If you don't think it can be done2. If you think Enterprise Manager is the only way to do this3. If you think I should just change the order of my Selectstatements4. If you want to state that order column doesn't matter in arelational database5. If you want to ask me why I want to do thisWish:Hopefully the answer WON'T involve creating a brand new table, movingthe data from old to new, dropping the old table, then renaming thenew table to the old name. Yes, I can do that. The table I'm workingwith is extremely huge -- I don't want to do the data juggling.Thanks in advance!
View 2 Replies View RelatedI created a package using the Import Data wizard and everything works fine when I re-execute it. I needed to change the name of the destination table so I went into SQL Server and renamed the table. I then went in and edited the DTSX file via a text editor and changed the OpenRowset setting from [CMBS].[dbo].[raw_Note] -> [CMBS].[dbo].[T_Raw_Note]
<property id="104" name="OpenRowset" dataType="System.String" state="default" isArray="false" description="Specifies the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">[CMBS].[dbo].[T_Raw_Note]</property>
There are no other references to the table in the DTSX, i saved the file and then re-ran.
When I run, I get invalid object name dbo.raw_Note (old table name). Is there some data being cached somewhere or hidden elsewhere that it would reference the old table? When I go back into the DTSX file, the correct name is in there so I don't know where it is getting the old name from? Any help would be appreciated.
Tim
Hi everyone,
I am trying to create a proof of concept to show that we can have packages deployed on 3 different servers and all we need to do is tell the package, upon execution/scheduled task, where to go fetch it's configurations from.
The configurations are using SQL Server as the package configuration. This makes it easier for DBA's to maintain since DBA's are responsible for package executions and job scheduling.
For example, the configuration database and all package configurations would be found on server1, server2 and server3 The difference in values is that on Server1, the configuration for the source data and the destination data point to Server1DatabaseSource and Server1DatabaseDestination and on server2, the configs point the source to Server2DatabaseSource and Server2DatabaseDestination and for server 3, the same thing but pointing to server3.
There is also a connection for the SSIS_Config database we're getting the configurations from. In theory, if we specify a different server where this SSIS_Config database is found, it should override the settings in the package. No?
When I schedule OR execute the package, it's always getting it's configs from the config specified in the package independent of wether I specify it in the Connection Managers of the Execute Package Utility when I manually execute it OR in the data sources tab when I configure the package to be run as a job in SQL Server 2005.
Am I missing something here?
Thanks,
Rob
Hi all,
Is there a way to change the dataset being used by a table dynamically ?
Regards,
Neil
Hi
We have a merge publication - I want to change the primary key for one of the tables (add another column to the primary key)
How do I do it ?
Currently there is no data in the table - which I guess might help..
thanks
Bruce
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