Filegroup question

Question

Filegroup question

Cricketdogger

SSC Veteran

Points: 259
More actions
May 18, 2005 at 9:05 am

#93044

I have a database I inherited that has a filegroup set up. I say set up, but it looks like a total accident. It caused no harm, but I see no reason for what was done and ultimately I would like to clean it up. Both filegroups are primary and everything is on the same drive.
Filenames are xxxxdata and xxxxdata1. Location is g:\mssql\data\xxx.mdf and g:\mssql\data\xxx_1.mdf. Space allocated is 20,538 mb and 78 mb respectively.
How can I find out what is in the xxxxdata1 file?? Then assuming there is something there, how can I get it into the xxx.mdf and get rid of the xxx_1.mdf.
Thanks.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

Create a test database - restore file xxxxdata1 to it and view contents !!! To copy from one to the other - you would need to create 2 databases - restore a file each to each of these dbs and then use "import/export" to tranfer objects and contents!

hth

**ASCII stupid question, get a stupid ANSI !!!**

Cricketdogger SSC Veteran Points: 259 More actions · Answer 2

Cricketdogger

SSC Veteran

Points: 259

May 18, 2005 at 12:22 pm

#559684

I will give that a try.

Thanks!

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

ok - good luck!

**ASCII stupid question, get a stupid ANSI !!!**

Amit Lohia SSCertifiable Points: 6104 More actions · Answer 4

I do not think you can attach the database with just 1 file. Try and let us know if you are successful. if you want to get rid of 1 file. you should consider using DBCC Shrinkfile (with EmptyFile option). Use Alter Database to remove the empty file.

Kindest Regards,

Amit Lohia

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

You can attach a single file using:

exec sp_attach_single_file_db @dbname = 'dbname',

@physname = 'C:\Program Files\Microsoft SQL Server\Data\xxxData.mdf'

**ASCII stupid question, get a stupid ANSI !!!**

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 6

sushila, it is not possible to attach only one file of a database that consists of several datafiles. sp_attach_single_file_db is only used for databases that only have one datafile.

Cricketdogger: As I understand your description you do not have two filegroups, but you have two datafiles in the primary filegroup. You want to get rid of one of those files. Here is how you do that:

First increase the size of file xxxxdata to at least 20,538 mb and 78 mb, plus a little extra. Why not increase it to 150 MB to have some room to grow in.

Next, execute the following:

DBCC SHRINKFILE ('xxxxdata1', EMPTYFILE)

This will move all data from xxxxdata1 to xxxxdata (which is now large enough to hold that data) and instruct SQL Server to stop placing any data in xxxxdata1. In other words xxxxdata1 will no longer be used by SQL Server at all, it will just be an 'empty' file that is 78 mb large. Since it is not in use anymore you can now remove it using ALTER DATABASE.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

sushila SSC-Dedicated Points: 35293 More actions · Answer 7

Chris - wouldn't there be only one .mdf in the primary filegroup - I thought all other datafiles created get the default .ndf extension ?!?!?!?!

**ASCII stupid question, get a stupid ANSI !!!**

Amit Lohia SSCertifiable Points: 6104 More actions · Answer 8

You can change the extension to anything you want. Secondly even ndf can be datafile

Kindest Regards,

Amit Lohia

sushila SSC-Dedicated Points: 35293 More actions · Answer 9

Yes Amit - this is true!

I just don't know of people who actually change the extensions preferring to go with the SQL Server defaults!

**ASCII stupid question, get a stupid ANSI !!!**

noeld SSC Guru Points: 96590 More actions · Answer 10

Well I think there is a reason for having more than one file in a filegroup and it is that you get a proportional fill (parallel operation) therefore you are supposed to get better throughput! it will be effective though if those files lived in separated drives!

* Noel

Amit Lohia SSCertifiable Points: 6104 More actions · Answer 11

noeld, you are correct but I would like to increase the scope of the discussion. What if you have a Large SAN Drive and logical drive create from it. Will plcae the indexes onto different logical drive will help ?

Kindest Regards,

Amit Lohia

Shawn Denogeon SSChasing Mays Points: 634 More actions · Answer 12

From another post, I got the impression that since MSSQL Server can only update, insert, delete to one filegroup at a time on one thread / processor, that having another filegroup would allow parallel operations if and only if you have more than one processor.

So, with a SAN, potentially, submitting parallel operations would still benefit. However, I'd keep in mind the actual specifications of the SAN, e.g. 10-bit encoding at 2 Gb/second is equivalent to 200 MB / second. That's b bit and B byte, respectively. I'd test first because I've seen SANs flooded by simultaneously read / write operations, e.g. reading a mdf from one logical volume and backing it up to another logical volume, all on the same SAN. The fix was to keep the backups on the SAN and the data on the faster RAID drives at 320 MB / second.

It sounds about right because it seems there are other things that work in the same way. For example, I've been investigating a connection pool issue with a .NET application and it appears that you get one pool per processor. And, if a connection in one of those pools is updating, inserting or deleting, then the whole pool is locked. It might refer to the application's processors and not the databases' processor.

I want to do some testing because, in some cases, the multiple filegroups may be beneficial if it's more than just a way to allow parallel read/write operations to different "drives".