October 27, 2009 at 2:59 am
Hello all,
i need a small info about a question i have.
In a SQL 2000 Server, I have a database with around 20 tables inside. One of the tables is around 29 GB. I have a problem with the Disk space and i wonder if i can create a new mdf file with only that table inside. Is it possible to create and have 2 mdf files?
Ty all.
October 27, 2009 at 3:16 am
Yes, though secondary data files are usually given the .ndf extension. Look up ALTER DATABASE in Books online for the syntax for adding a data file. To move specific tables, that new file has to be in a different filegroup. Again, look up ALTER DATABASE. iirc, it's add the filegroup then add the file.
To move a table over you'll need to move the clustered index to the new filegroup. I believe the syntax is CREATE INDEX ... WITH DROP_EXISTING (assuming it's not the primary key). If the clustered index is enforcing the primary key then it's ALTER TABLE. Again, all syntax is in Books Online.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2009 at 3:18 am
GilaMonster (10/27/2009)
Yes, though secondary data files are usually given the .ndf extension. Look up ALTER DATABASE in Books online for the syntax for adding a data file. To move specific tables, that new file has to be in a different filegroup. Again, look up ALTER DATABASE. iirc, it's add the filegroup then add the file.To move a table over you'll need to move the clustered index to the new filegroup. I believe the syntax is CREATE INDEX ... WITH DROP_EXISTING (assuming it's not the primary key). If the clustered index is enforcing the primary key then it's ALTER TABLE. Again, all syntax is in Books Online.
Ty alot for the Quickest reply GilaMonster.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply