Dug ourselves into a hole

We just upgraded from 6.5 to 2000. The original database lived on two device
s and as a result the upgrade process created an mdf and ndf files for the p
rimary filegroup.
Here is the current allocation
db_data1.mdf 9MB
db_data2.ndf 3600MB
Logically, we would use alter database to empty the file contents of the mdf
file. SQL lets us to this. However, when we ask it to delete the file after
it is emptied, it informs us that we cannot delete the primary file.
The only solution is to empty the db_data2.ndf and then remove it.
Is this the best solution? And is this process of moving data from one file
to another highly reliable?I tend to use the primary file - the .MDF file - just for system objects and
place al of my application objects in a separate file. Looks like you're
there already. :-)_
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Gil Darves" <gil.darves@.phtech> wrote in message
news:90CC2143-8DC0-4C9F-A8BC-37D3FE369899@.microsoft.com...
We just upgraded from 6.5 to 2000. The original database lived on two
devices and as a result the upgrade process created an mdf and ndf files for
the primary filegroup.
Here is the current allocation
db_data1.mdf 9MB
db_data2.ndf 3600MB
Logically, we would use alter database to empty the file contents of the mdf
file. SQL lets us to this. However, when we ask it to delete the file after
it is emptied, it informs us that we cannot delete the primary file.
The only solution is to empty the db_data2.ndf and then remove it.
Is this the best solution? And is this process of moving data from one file
to another highly reliable?|||Thank you Thomas.
Yes, I understand about splitting objects but this pre-supposes the secondar
y file is on another filegroup. It is not; it's still the primary filegroup.
So I guess I am still back to square one and I am currently testing the proc
ess of moving the data from the ndf back to the mdf. Looks like it is a very
slow process and with 36 GB will take over 4 hours to accomplish. So I am c
urious, can you perform thi
s action if there is moderate activity on the db? We are thinking of doing t
his over the weekend.
Thanks again!
Gilles de Montreal|||Oh, OK. I'd still go with two filegroups. The big problem here is the
migration, which you can do in stages - as long as the users don't need
access during the migration. First, create a new, single-file filegroup.
Make sure it has lots of space. Then, for each table to be moved:
1. Drop all nonclustered indexes.
2. Drop the clustered index.
3. Create the clustered index on the new filegroup.
4. Create the nonclustered indexes.
Start with the largest table. It could be that you have to do this in a
series of outages. Ideally, you should take a backup of production and try
this out on a similar server.
You can still go the other way and refill the primary file. Consider the
following:
use Northwind
go
alter database Northwind
add file
(
name = Second
, filename = 'E:\temp\Second.ndf'
) to filegroup [PRIMARY]
go
sp_helpfile
go
dbcc shrinkfile (1, emptyfile)
go
dbcc shrinkfile (3, emptyfile)
go
alter database Test
remove file Second
This pushes all of the data back into the primary file. (I'd expand it
first.)
HTH
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Gil Darves" <anonymous@.discussions.microsoft.com> wrote in message
news:30EA8F1E-A019-4E6A-BEDB-AF2A6C57B6DB@.microsoft.com...
Thank you Thomas.
Yes, I understand about splitting objects but this pre-supposes the
secondary file is on another filegroup. It is not; it's still the primary
filegroup.
So I guess I am still back to square one and I am currently testing the
process of moving the data from the ndf back to the mdf. Looks like it is a
very slow process and with 36 GB will take over 4 hours to accomplish. So I
am curious, can you perform this action if there is moderate activity on the
db? We are thinking of doing this over the weekend.
Thanks again!
Gilles de Montreal

Dug ourselves into a hole

We just upgraded from 6.5 to 2000. The original database lived on two devices and as a result the upgrade process created an mdf and ndf files for the primary filegroup
Here is the current allocatio
db_data1.mdf 9M
db_data2.ndf 3600M
Logically, we would use alter database to empty the file contents of the mdf file. SQL lets us to this. However, when we ask it to delete the file after it is emptied, it informs us that we cannot delete the primary file
The only solution is to empty the db_data2.ndf and then remove it
Is this the best solution? And is this process of moving data from one file to another highly reliable?This is a multi-part message in MIME format.
--=_NextPart_000_04D3_01C3DB85.8613E550
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
I tend to use the primary file - the .MDF file - just for system objects and
place al of my application objects in a separate file. Looks like you're
there already. :-)_
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Gil Darves" <gil.darves@.phtech> wrote in message
news:90CC2143-8DC0-4C9F-A8BC-37D3FE369899@.microsoft.com...
We just upgraded from 6.5 to 2000. The original database lived on two
devices and as a result the upgrade process created an mdf and ndf files for
the primary filegroup.
Here is the current allocation
db_data1.mdf 9MB
db_data2.ndf 3600MB
Logically, we would use alter database to empty the file contents of the mdf
file. SQL lets us to this. However, when we ask it to delete the file after
it is emptied, it informs us that we cannot delete the primary file.
The only solution is to empty the db_data2.ndf and then remove it.
Is this the best solution? And is this process of moving data from one file
to another highly reliable?
--=_NextPart_000_04D3_01C3DB85.8613E550
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

I tend to use the primary file - the =.MDF file - just for system objects and place al of my application objects in a =separate file. Looks like you're there already. :-)_
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Gil Darves" wrote in =message news:90C=C2143-8DC0-4C9F-A8BC-37D3FE369899@.microsoft.com...We just upgraded from 6.5 to 2000. The original database lived on two =devices and as a result the upgrade process created an mdf and ndf files for the =primary filegroup.Here is the current allocationdb_data1.mdf 9MBdb_data2.ndf 3600MBLogically, we would use alter database =to empty the file contents of the mdf file. SQL lets us to this. However, =when we ask it to delete the file after it is emptied, it informs us that we =cannot delete the primary file.The only solution is to empty the =db_data2.ndf and then remove it.Is this the best solution? And is this =process of moving data from one file to another highly reliable?

---

--=_NextPart_000_04D3_01C3DB85.8613E550--|||Thank you Thomas
Yes, I understand about splitting objects but this pre-supposes the secondary file is on another filegroup. It is not; it's still the primary filegroup
So I guess I am still back to square one and I am currently testing the process of moving the data from the ndf back to the mdf. Looks like it is a very slow process and with 36 GB will take over 4 hours to accomplish. So I am curious, can you perform this action if there is moderate activity on the db? We are thinking of doing this over the weekend
Thanks again
Gilles de Montreal|||This is a multi-part message in MIME format.
--=_NextPart_000_0049_01C3DB94.68C0E2A0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
Oh, OK. I'd still go with two filegroups. The big problem here is the
migration, which you can do in stages - as long as the users don't need
access during the migration. First, create a new, single-file filegroup.
Make sure it has lots of space. Then, for each table to be moved:
1. Drop all nonclustered indexes.
2. Drop the clustered index.
3. Create the clustered index on the new filegroup.
4. Create the nonclustered indexes.
Start with the largest table. It could be that you have to do this in a
series of outages. Ideally, you should take a backup of production and try
this out on a similar server.
You can still go the other way and refill the primary file. Consider the
following:
use Northwind
go
alter database Northwind
add file
(
name = Second
, filename = 'E:\temp\Second.ndf'
) to filegroup [PRIMARY]
go
sp_helpfile
go
dbcc shrinkfile (1, emptyfile)
go
dbcc shrinkfile (3, emptyfile)
go
alter database Test
remove file Second
This pushes all of the data back into the primary file. (I'd expand it
first.)
HTH
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Gil Darves" <anonymous@.discussions.microsoft.com> wrote in message
news:30EA8F1E-A019-4E6A-BEDB-AF2A6C57B6DB@.microsoft.com...
Thank you Thomas.
Yes, I understand about splitting objects but this pre-supposes the
secondary file is on another filegroup. It is not; it's still the primary
filegroup.
So I guess I am still back to square one and I am currently testing the
process of moving the data from the ndf back to the mdf. Looks like it is a
very slow process and with 36 GB will take over 4 hours to accomplish. So I
am curious, can you perform this action if there is moderate activity on the
db? We are thinking of doing this over the weekend.
Thanks again!
Gilles de Montreal
--=_NextPart_000_0049_01C3DB94.68C0E2A0
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Oh, OK. I'd still go with two filegroups. The big problem here is the migration, which you can =do in stages - as long as the users don't need access during the =migration. First, create a new, single-file filegroup. Make sure it has lots =of space. Then, for each table to =be moved:
1. Drop all =nonclustered indexes.
2. Drop the =clustered index.
3. Create =the clustered index on the new filegroup.
4. Create the =nonclustered indexes.
Start with the largest table. It =could be that you have to do this in a series of outages. Ideally, you =should take a backup of production and try this out on a similar =server.
You can still go the other way and =refill the primary file. Consider the following:
use Northwind
go
alter database Northwindadd =file( name =3D Second, filename =3D ='E:\temp\Second.ndf') to filegroup [PRIMARY]go
sp_helpfilego
dbcc shrinkfile (1, emptyfile)go
dbcc shrinkfile (3, emptyfile)go
alter database Testremove =file Second
This pushes all of the data back into =the primary file. (I'd expand it first.)
HTH
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Gil Darves" wrote in message news:30E=A8F1E-A019-4E6A-BEDB-AF2A6C57B6DB@.microsoft.com...Thank you Thomas.Yes, I understand about splitting objects but this pre-supposes the secondary file is on another filegroup. It is not; it's =still the primary filegroup.So I guess I am still back to square one =and I am currently testing the process of moving the data from the ndf back to =the mdf. Looks like it is a very slow process and with 36 GB will take over 4 =hours to accomplish. So I am curious, can you perform this action if there is =moderate activity on the db? We are thinking of doing this over the weekend.Thanks again!Gilles de Montreal

---

--=_NextPart_000_0049_01C3DB94.68C0E2A0--