SQL Server File groups

[sendba]

Hi,
I have created a filegroup named CardPin and I created 2 files under this filegroup Cardpin1.ndf and Cardpin2.ndf.

Can someone please let me know how to find which objects reside on which files under the same filegroup.

Thanks in advance

Sen

 

[mrdenny]

The table will be on all files within the file group. As both files were created at the same time, the table will be spread evenly over both files.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[sendba]

When I rebuild the clustered index online would the data be created in the new file which is added to the filegroup.
Will that affect performance and how can I reclaim the free space from any one of the files.

Please advice

Thanks
Sen

 

[mrdenny]

Are you adding a new file to an existing file group, or are you adding a new file group with two files?

If you are simply adding a new file to an existing file group, then no the data won't be moved to the new file. If you are moving to a new file group, then yes.

If you are adding a new file to an existing file group, you'll need to move the table out of the file group then back in, in order to spread the data accross the two files evenly.

Free space from a single file can be reclaimed via the DBCC SHRINKFILE command.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[sendba]

I wanted to test partitioning in SQL Server 2005. I have a 800 Million record table which I want partition.
The file is 200 GB in size and the occupied space is 175GB so I created a new file in the same filegroup and restricted the first file growth to 176 GB.
When i rebuilt the clustered index the oocupied space in the new file is 101 GB and the old file freed up 60 GB of free space.
My real concern is the file which we are adding to the filegroup resides on RAID 5 compared to the first file which is on RAID 1 which is fast.

Can you let me know how to move data and avoid sql server from writing the data to the new file.

Thanks Denny
Sen