Hi, I have a database with two data files, both belonging
to the PRIMARY filegroup. I've emptied one of the files
and would like to modify that file's filegroup to
SECONDARY so that I can remove it from my database.
Using the alter database modify filegroup would impact my
entire database and not to a specific file.
Any suggestions. Thanks.Why not just remove it using ALTER DATABASE... REMOVE FILE...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:974b01c48618$64d38f60$a601
280a@.phx.gbl...
> Hi, I have a database with two data files, both belonging
> to the PRIMARY filegroup. I've emptied one of the files
> and would like to modify that file's filegroup to
> SECONDARY so that I can remove it from my database.
> Using the alter database modify filegroup would impact my
> entire database and not to a specific file.
> Any suggestions. Thanks.|||That doesn't work because it already belongs to in the
PRIMARY filegroup and get the following msg when attempted
to do so:
The primary data or log file cannot be removed from a
database.
Thanks.
>--Original Message--
>Why not just remove it using ALTER DATABASE... REMOVE
FILE...
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message news:974b01c48618$64d38f60$a601280a@.phx.gbl...
belonging[vbcol=seagreen]
my[vbcol=seagreen]
>
>.
>|||The filegroup named "PRIMARY" is not the same thing as the primary file (whi
ch generally has .mdf as
extension).
The error message states that you cannot remove the primary file. You can, h
owever remove secondary file
(.ndf) from the primary filegroup (as well as other filegroups).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:926801c48621$4c39ddc0$a501
280a@.phx.gbl...[vbcol=seagreen]
> That doesn't work because it already belongs to in the
> PRIMARY filegroup and get the following msg when attempted
> to do so:
> The primary data or log file cannot be removed from a
> database.
> Thanks.
>
> FILE...
> message news:974b01c48618$64d38f60$a601280a@.phx.gbl...
> belonging
> my|||Both these files have the .mdf extension. I wonder if
detaching the db, renaming the .mdf file in question
to .ndf, re-attaching the db, and then attempt to remove
this file again would work.
Thanks again.
>--Original Message--
>The filegroup named "PRIMARY" is not the same thing as
the primary file (which generally has .mdf as
>extension).
>The error message states that you cannot remove the
primary file. You can, however remove secondary file
>(.ndf) from the primary filegroup (as well as other
filegroups).
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...
attempted[vbcol=seagreen]
files[vbcol=seagreen]
impact[vbcol=seagreen]
>
>.
>|||Nope that didn't work... still get the same error message
after I had detached, renamed, and re-attached the db.
Any other thoughts? Thanks.
>--Original Message--
>Both these files have the .mdf extension. I wonder if
>detaching the db, renaming the .mdf file in question
>to .ndf, re-attaching the db, and then attempt to remove
>this file again would work.
>Thanks again.
>
>the primary file (which generally has .mdf as
>primary file. You can, however remove secondary file
>filegroups).
>message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...
>attempted
>files
>impact
>.
>|||You can name the files anything you want, that doesn't affect whether a file
is a primary or secondary
database file. I.e., seems like someone didn't follow the naming conventions
when creating your database.
(There can be only one primary file for each database.)
You can try sp_helpfile. I have a feeling that the one with fileid 1 is the
primary. OTOH, you already know
which is the primary, the one you tried to remove. Empty the other file (the
one which isn't the primary) and
then you can remove that. You cannot remove the primary database file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:92c001c48624$027652b0$a501
280a@.phx.gbl...[vbcol=seagreen]
> Both these files have the .mdf extension. I wonder if
> detaching the db, renaming the .mdf file in question
> to .ndf, re-attaching the db, and then attempt to remove
> this file again would work.
> Thanks again.
>
> the primary file (which generally has .mdf as
> primary file. You can, however remove secondary file
> filegroups).
> message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...
> attempted
> files
> impact
Showing posts with label filegroup. Show all posts
Showing posts with label filegroup. Show all posts
Thursday, February 16, 2012
changing file's filegroup
Hi, I have a database with two data files, both belonging
to the PRIMARY filegroup. I've emptied one of the files
and would like to modify that file's filegroup to
SECONDARY so that I can remove it from my database.
Using the alter database modify filegroup would impact my
entire database and not to a specific file.
Any suggestions. Thanks.
Why not just remove it using ALTER DATABASE... REMOVE FILE...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:974b01c48618$64d38f60$a601280a@.phx.gbl...
> Hi, I have a database with two data files, both belonging
> to the PRIMARY filegroup. I've emptied one of the files
> and would like to modify that file's filegroup to
> SECONDARY so that I can remove it from my database.
> Using the alter database modify filegroup would impact my
> entire database and not to a specific file.
> Any suggestions. Thanks.
|||That doesn't work because it already belongs to in the
PRIMARY filegroup and get the following msg when attempted
to do so:
The primary data or log file cannot be removed from a
database.
Thanks.
>--Original Message--
>Why not just remove it using ALTER DATABASE... REMOVE
FILE...
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message news:974b01c48618$64d38f60$a601280a@.phx.gbl...[vbcol=seagreen]
belonging[vbcol=seagreen]
my
>
>.
>
|||The filegroup named "PRIMARY" is not the same thing as the primary file (which generally has .mdf as
extension).
The error message states that you cannot remove the primary file. You can, however remove secondary file
(.ndf) from the primary filegroup (as well as other filegroups).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...[vbcol=seagreen]
> That doesn't work because it already belongs to in the
> PRIMARY filegroup and get the following msg when attempted
> to do so:
> The primary data or log file cannot be removed from a
> database.
> Thanks.
> FILE...
> message news:974b01c48618$64d38f60$a601280a@.phx.gbl...
> belonging
> my
|||Both these files have the .mdf extension. I wonder if
detaching the db, renaming the .mdf file in question
to .ndf, re-attaching the db, and then attempt to remove
this file again would work.
Thanks again.
>--Original Message--
>The filegroup named "PRIMARY" is not the same thing as
the primary file (which generally has .mdf as
>extension).
>The error message states that you cannot remove the
primary file. You can, however remove secondary file
>(.ndf) from the primary filegroup (as well as other
filegroups).
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...[vbcol=seagreen]
attempted[vbcol=seagreen]
files[vbcol=seagreen]
impact
>
>.
>
|||Nope that didn't work... still get the same error message
after I had detached, renamed, and re-attached the db.
Any other thoughts? Thanks.
>--Original Message--
>Both these files have the .mdf extension. I wonder if
>detaching the db, renaming the .mdf file in question
>to .ndf, re-attaching the db, and then attempt to remove
>this file again would work.
>Thanks again.
>the primary file (which generally has .mdf as
>primary file. You can, however remove secondary file
>filegroups).
>message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...
>attempted
>files
>impact
>.
>
|||You can name the files anything you want, that doesn't affect whether a file is a primary or secondary
database file. I.e., seems like someone didn't follow the naming conventions when creating your database.
(There can be only one primary file for each database.)
You can try sp_helpfile. I have a feeling that the one with fileid 1 is the primary. OTOH, you already know
which is the primary, the one you tried to remove. Empty the other file (the one which isn't the primary) and
then you can remove that. You cannot remove the primary database file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:92c001c48624$027652b0$a501280a@.phx.gbl...[vbcol=seagreen]
> Both these files have the .mdf extension. I wonder if
> detaching the db, renaming the .mdf file in question
> to .ndf, re-attaching the db, and then attempt to remove
> this file again would work.
> Thanks again.
> the primary file (which generally has .mdf as
> primary file. You can, however remove secondary file
> filegroups).
> message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...
> attempted
> files
> impact
to the PRIMARY filegroup. I've emptied one of the files
and would like to modify that file's filegroup to
SECONDARY so that I can remove it from my database.
Using the alter database modify filegroup would impact my
entire database and not to a specific file.
Any suggestions. Thanks.
Why not just remove it using ALTER DATABASE... REMOVE FILE...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:974b01c48618$64d38f60$a601280a@.phx.gbl...
> Hi, I have a database with two data files, both belonging
> to the PRIMARY filegroup. I've emptied one of the files
> and would like to modify that file's filegroup to
> SECONDARY so that I can remove it from my database.
> Using the alter database modify filegroup would impact my
> entire database and not to a specific file.
> Any suggestions. Thanks.
|||That doesn't work because it already belongs to in the
PRIMARY filegroup and get the following msg when attempted
to do so:
The primary data or log file cannot be removed from a
database.
Thanks.
>--Original Message--
>Why not just remove it using ALTER DATABASE... REMOVE
FILE...
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message news:974b01c48618$64d38f60$a601280a@.phx.gbl...[vbcol=seagreen]
belonging[vbcol=seagreen]
my
>
>.
>
|||The filegroup named "PRIMARY" is not the same thing as the primary file (which generally has .mdf as
extension).
The error message states that you cannot remove the primary file. You can, however remove secondary file
(.ndf) from the primary filegroup (as well as other filegroups).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...[vbcol=seagreen]
> That doesn't work because it already belongs to in the
> PRIMARY filegroup and get the following msg when attempted
> to do so:
> The primary data or log file cannot be removed from a
> database.
> Thanks.
> FILE...
> message news:974b01c48618$64d38f60$a601280a@.phx.gbl...
> belonging
> my
|||Both these files have the .mdf extension. I wonder if
detaching the db, renaming the .mdf file in question
to .ndf, re-attaching the db, and then attempt to remove
this file again would work.
Thanks again.
>--Original Message--
>The filegroup named "PRIMARY" is not the same thing as
the primary file (which generally has .mdf as
>extension).
>The error message states that you cannot remove the
primary file. You can, however remove secondary file
>(.ndf) from the primary filegroup (as well as other
filegroups).
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...[vbcol=seagreen]
attempted[vbcol=seagreen]
files[vbcol=seagreen]
impact
>
>.
>
|||Nope that didn't work... still get the same error message
after I had detached, renamed, and re-attached the db.
Any other thoughts? Thanks.
>--Original Message--
>Both these files have the .mdf extension. I wonder if
>detaching the db, renaming the .mdf file in question
>to .ndf, re-attaching the db, and then attempt to remove
>this file again would work.
>Thanks again.
>the primary file (which generally has .mdf as
>primary file. You can, however remove secondary file
>filegroups).
>message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...
>attempted
>files
>impact
>.
>
|||You can name the files anything you want, that doesn't affect whether a file is a primary or secondary
database file. I.e., seems like someone didn't follow the naming conventions when creating your database.
(There can be only one primary file for each database.)
You can try sp_helpfile. I have a feeling that the one with fileid 1 is the primary. OTOH, you already know
which is the primary, the one you tried to remove. Empty the other file (the one which isn't the primary) and
then you can remove that. You cannot remove the primary database file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:92c001c48624$027652b0$a501280a@.phx.gbl...[vbcol=seagreen]
> Both these files have the .mdf extension. I wonder if
> detaching the db, renaming the .mdf file in question
> to .ndf, re-attaching the db, and then attempt to remove
> this file again would work.
> Thanks again.
> the primary file (which generally has .mdf as
> primary file. You can, however remove secondary file
> filegroups).
> message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...
> attempted
> files
> impact
changing file's filegroup
Hi, I have a database with two data files, both belonging
to the PRIMARY filegroup. I've emptied one of the files
and would like to modify that file's filegroup to
SECONDARY so that I can remove it from my database.
Using the alter database modify filegroup would impact my
entire database and not to a specific file.
Any suggestions. Thanks.Why not just remove it using ALTER DATABASE... REMOVE FILE...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:974b01c48618$64d38f60$a601280a@.phx.gbl...
> Hi, I have a database with two data files, both belonging
> to the PRIMARY filegroup. I've emptied one of the files
> and would like to modify that file's filegroup to
> SECONDARY so that I can remove it from my database.
> Using the alter database modify filegroup would impact my
> entire database and not to a specific file.
> Any suggestions. Thanks.|||That doesn't work because it already belongs to in the
PRIMARY filegroup and get the following msg when attempted
to do so:
The primary data or log file cannot be removed from a
database.
Thanks.
>--Original Message--
>Why not just remove it using ALTER DATABASE... REMOVE
FILE...
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message news:974b01c48618$64d38f60$a601280a@.phx.gbl...
>> Hi, I have a database with two data files, both
belonging
>> to the PRIMARY filegroup. I've emptied one of the files
>> and would like to modify that file's filegroup to
>> SECONDARY so that I can remove it from my database.
>> Using the alter database modify filegroup would impact
my
>> entire database and not to a specific file.
>> Any suggestions. Thanks.
>
>.
>|||The filegroup named "PRIMARY" is not the same thing as the primary file (which generally has .mdf as
extension).
The error message states that you cannot remove the primary file. You can, however remove secondary file
(.ndf) from the primary filegroup (as well as other filegroups).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...
> That doesn't work because it already belongs to in the
> PRIMARY filegroup and get the following msg when attempted
> to do so:
> The primary data or log file cannot be removed from a
> database.
> Thanks.
> >--Original Message--
> >Why not just remove it using ALTER DATABASE... REMOVE
> FILE...
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
> message news:974b01c48618$64d38f60$a601280a@.phx.gbl...
> >> Hi, I have a database with two data files, both
> belonging
> >> to the PRIMARY filegroup. I've emptied one of the files
> >> and would like to modify that file's filegroup to
> >> SECONDARY so that I can remove it from my database.
> >>
> >> Using the alter database modify filegroup would impact
> my
> >> entire database and not to a specific file.
> >>
> >> Any suggestions. Thanks.
> >
> >
> >.
> >|||Both these files have the .mdf extension. I wonder if
detaching the db, renaming the .mdf file in question
to .ndf, re-attaching the db, and then attempt to remove
this file again would work.
Thanks again.
>--Original Message--
>The filegroup named "PRIMARY" is not the same thing as
the primary file (which generally has .mdf as
>extension).
>The error message states that you cannot remove the
primary file. You can, however remove secondary file
>(.ndf) from the primary filegroup (as well as other
filegroups).
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...
>> That doesn't work because it already belongs to in the
>> PRIMARY filegroup and get the following msg when
attempted
>> to do so:
>> The primary data or log file cannot be removed from a
>> database.
>> Thanks.
>> >--Original Message--
>> >Why not just remove it using ALTER DATABASE... REMOVE
>> FILE...
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >http://www.karaszi.com/sqlserver/default.asp
>> >http://www.solidqualitylearning.com/
>> >
>> >
>> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
>> message news:974b01c48618$64d38f60$a601280a@.phx.gbl...
>> >> Hi, I have a database with two data files, both
>> belonging
>> >> to the PRIMARY filegroup. I've emptied one of the
files
>> >> and would like to modify that file's filegroup to
>> >> SECONDARY so that I can remove it from my database.
>> >>
>> >> Using the alter database modify filegroup would
impact
>> my
>> >> entire database and not to a specific file.
>> >>
>> >> Any suggestions. Thanks.
>> >
>> >
>> >.
>> >
>
>.
>|||Nope that didn't work... still get the same error message
after I had detached, renamed, and re-attached the db.
Any other thoughts? Thanks.
>--Original Message--
>Both these files have the .mdf extension. I wonder if
>detaching the db, renaming the .mdf file in question
>to .ndf, re-attaching the db, and then attempt to remove
>this file again would work.
>Thanks again.
>>--Original Message--
>>The filegroup named "PRIMARY" is not the same thing as
>the primary file (which generally has .mdf as
>>extension).
>>The error message states that you cannot remove the
>primary file. You can, however remove secondary file
>>(.ndf) from the primary filegroup (as well as other
>filegroups).
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>
>>"Rob" <anonymous@.discussions.microsoft.com> wrote in
>message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...
>> That doesn't work because it already belongs to in the
>> PRIMARY filegroup and get the following msg when
>attempted
>> to do so:
>> The primary data or log file cannot be removed from a
>> database.
>> Thanks.
>> >--Original Message--
>> >Why not just remove it using ALTER DATABASE... REMOVE
>> FILE...
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >http://www.karaszi.com/sqlserver/default.asp
>> >http://www.solidqualitylearning.com/
>> >
>> >
>> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
>> message news:974b01c48618$64d38f60$a601280a@.phx.gbl...
>> >> Hi, I have a database with two data files, both
>> belonging
>> >> to the PRIMARY filegroup. I've emptied one of the
>files
>> >> and would like to modify that file's filegroup to
>> >> SECONDARY so that I can remove it from my database.
>> >>
>> >> Using the alter database modify filegroup would
>impact
>> my
>> >> entire database and not to a specific file.
>> >>
>> >> Any suggestions. Thanks.
>> >
>> >
>> >.
>> >
>>
>>.
>.
>|||You can name the files anything you want, that doesn't affect whether a file is a primary or secondary
database file. I.e., seems like someone didn't follow the naming conventions when creating your database.
(There can be only one primary file for each database.)
You can try sp_helpfile. I have a feeling that the one with fileid 1 is the primary. OTOH, you already know
which is the primary, the one you tried to remove. Empty the other file (the one which isn't the primary) and
then you can remove that. You cannot remove the primary database file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:92c001c48624$027652b0$a501280a@.phx.gbl...
> Both these files have the .mdf extension. I wonder if
> detaching the db, renaming the .mdf file in question
> to .ndf, re-attaching the db, and then attempt to remove
> this file again would work.
> Thanks again.
> >--Original Message--
> >The filegroup named "PRIMARY" is not the same thing as
> the primary file (which generally has .mdf as
> >extension).
> >
> >The error message states that you cannot remove the
> primary file. You can, however remove secondary file
> >(.ndf) from the primary filegroup (as well as other
> filegroups).
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
> message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...
> >> That doesn't work because it already belongs to in the
> >> PRIMARY filegroup and get the following msg when
> attempted
> >> to do so:
> >>
> >> The primary data or log file cannot be removed from a
> >> database.
> >>
> >> Thanks.
> >>
> >> >--Original Message--
> >> >Why not just remove it using ALTER DATABASE... REMOVE
> >> FILE...
> >> >
> >> >--
> >> >Tibor Karaszi, SQL Server MVP
> >> >http://www.karaszi.com/sqlserver/default.asp
> >> >http://www.solidqualitylearning.com/
> >> >
> >> >
> >> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
> >> message news:974b01c48618$64d38f60$a601280a@.phx.gbl...
> >> >> Hi, I have a database with two data files, both
> >> belonging
> >> >> to the PRIMARY filegroup. I've emptied one of the
> files
> >> >> and would like to modify that file's filegroup to
> >> >> SECONDARY so that I can remove it from my database.
> >> >>
> >> >> Using the alter database modify filegroup would
> impact
> >> my
> >> >> entire database and not to a specific file.
> >> >>
> >> >> Any suggestions. Thanks.
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >
to the PRIMARY filegroup. I've emptied one of the files
and would like to modify that file's filegroup to
SECONDARY so that I can remove it from my database.
Using the alter database modify filegroup would impact my
entire database and not to a specific file.
Any suggestions. Thanks.Why not just remove it using ALTER DATABASE... REMOVE FILE...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:974b01c48618$64d38f60$a601280a@.phx.gbl...
> Hi, I have a database with two data files, both belonging
> to the PRIMARY filegroup. I've emptied one of the files
> and would like to modify that file's filegroup to
> SECONDARY so that I can remove it from my database.
> Using the alter database modify filegroup would impact my
> entire database and not to a specific file.
> Any suggestions. Thanks.|||That doesn't work because it already belongs to in the
PRIMARY filegroup and get the following msg when attempted
to do so:
The primary data or log file cannot be removed from a
database.
Thanks.
>--Original Message--
>Why not just remove it using ALTER DATABASE... REMOVE
FILE...
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message news:974b01c48618$64d38f60$a601280a@.phx.gbl...
>> Hi, I have a database with two data files, both
belonging
>> to the PRIMARY filegroup. I've emptied one of the files
>> and would like to modify that file's filegroup to
>> SECONDARY so that I can remove it from my database.
>> Using the alter database modify filegroup would impact
my
>> entire database and not to a specific file.
>> Any suggestions. Thanks.
>
>.
>|||The filegroup named "PRIMARY" is not the same thing as the primary file (which generally has .mdf as
extension).
The error message states that you cannot remove the primary file. You can, however remove secondary file
(.ndf) from the primary filegroup (as well as other filegroups).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...
> That doesn't work because it already belongs to in the
> PRIMARY filegroup and get the following msg when attempted
> to do so:
> The primary data or log file cannot be removed from a
> database.
> Thanks.
> >--Original Message--
> >Why not just remove it using ALTER DATABASE... REMOVE
> FILE...
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
> message news:974b01c48618$64d38f60$a601280a@.phx.gbl...
> >> Hi, I have a database with two data files, both
> belonging
> >> to the PRIMARY filegroup. I've emptied one of the files
> >> and would like to modify that file's filegroup to
> >> SECONDARY so that I can remove it from my database.
> >>
> >> Using the alter database modify filegroup would impact
> my
> >> entire database and not to a specific file.
> >>
> >> Any suggestions. Thanks.
> >
> >
> >.
> >|||Both these files have the .mdf extension. I wonder if
detaching the db, renaming the .mdf file in question
to .ndf, re-attaching the db, and then attempt to remove
this file again would work.
Thanks again.
>--Original Message--
>The filegroup named "PRIMARY" is not the same thing as
the primary file (which generally has .mdf as
>extension).
>The error message states that you cannot remove the
primary file. You can, however remove secondary file
>(.ndf) from the primary filegroup (as well as other
filegroups).
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...
>> That doesn't work because it already belongs to in the
>> PRIMARY filegroup and get the following msg when
attempted
>> to do so:
>> The primary data or log file cannot be removed from a
>> database.
>> Thanks.
>> >--Original Message--
>> >Why not just remove it using ALTER DATABASE... REMOVE
>> FILE...
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >http://www.karaszi.com/sqlserver/default.asp
>> >http://www.solidqualitylearning.com/
>> >
>> >
>> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
>> message news:974b01c48618$64d38f60$a601280a@.phx.gbl...
>> >> Hi, I have a database with two data files, both
>> belonging
>> >> to the PRIMARY filegroup. I've emptied one of the
files
>> >> and would like to modify that file's filegroup to
>> >> SECONDARY so that I can remove it from my database.
>> >>
>> >> Using the alter database modify filegroup would
impact
>> my
>> >> entire database and not to a specific file.
>> >>
>> >> Any suggestions. Thanks.
>> >
>> >
>> >.
>> >
>
>.
>|||Nope that didn't work... still get the same error message
after I had detached, renamed, and re-attached the db.
Any other thoughts? Thanks.
>--Original Message--
>Both these files have the .mdf extension. I wonder if
>detaching the db, renaming the .mdf file in question
>to .ndf, re-attaching the db, and then attempt to remove
>this file again would work.
>Thanks again.
>>--Original Message--
>>The filegroup named "PRIMARY" is not the same thing as
>the primary file (which generally has .mdf as
>>extension).
>>The error message states that you cannot remove the
>primary file. You can, however remove secondary file
>>(.ndf) from the primary filegroup (as well as other
>filegroups).
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>
>>"Rob" <anonymous@.discussions.microsoft.com> wrote in
>message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...
>> That doesn't work because it already belongs to in the
>> PRIMARY filegroup and get the following msg when
>attempted
>> to do so:
>> The primary data or log file cannot be removed from a
>> database.
>> Thanks.
>> >--Original Message--
>> >Why not just remove it using ALTER DATABASE... REMOVE
>> FILE...
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >http://www.karaszi.com/sqlserver/default.asp
>> >http://www.solidqualitylearning.com/
>> >
>> >
>> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
>> message news:974b01c48618$64d38f60$a601280a@.phx.gbl...
>> >> Hi, I have a database with two data files, both
>> belonging
>> >> to the PRIMARY filegroup. I've emptied one of the
>files
>> >> and would like to modify that file's filegroup to
>> >> SECONDARY so that I can remove it from my database.
>> >>
>> >> Using the alter database modify filegroup would
>impact
>> my
>> >> entire database and not to a specific file.
>> >>
>> >> Any suggestions. Thanks.
>> >
>> >
>> >.
>> >
>>
>>.
>.
>|||You can name the files anything you want, that doesn't affect whether a file is a primary or secondary
database file. I.e., seems like someone didn't follow the naming conventions when creating your database.
(There can be only one primary file for each database.)
You can try sp_helpfile. I have a feeling that the one with fileid 1 is the primary. OTOH, you already know
which is the primary, the one you tried to remove. Empty the other file (the one which isn't the primary) and
then you can remove that. You cannot remove the primary database file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:92c001c48624$027652b0$a501280a@.phx.gbl...
> Both these files have the .mdf extension. I wonder if
> detaching the db, renaming the .mdf file in question
> to .ndf, re-attaching the db, and then attempt to remove
> this file again would work.
> Thanks again.
> >--Original Message--
> >The filegroup named "PRIMARY" is not the same thing as
> the primary file (which generally has .mdf as
> >extension).
> >
> >The error message states that you cannot remove the
> primary file. You can, however remove secondary file
> >(.ndf) from the primary filegroup (as well as other
> filegroups).
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
> message news:926801c48621$4c39ddc0$a501280a@.phx.gbl...
> >> That doesn't work because it already belongs to in the
> >> PRIMARY filegroup and get the following msg when
> attempted
> >> to do so:
> >>
> >> The primary data or log file cannot be removed from a
> >> database.
> >>
> >> Thanks.
> >>
> >> >--Original Message--
> >> >Why not just remove it using ALTER DATABASE... REMOVE
> >> FILE...
> >> >
> >> >--
> >> >Tibor Karaszi, SQL Server MVP
> >> >http://www.karaszi.com/sqlserver/default.asp
> >> >http://www.solidqualitylearning.com/
> >> >
> >> >
> >> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
> >> message news:974b01c48618$64d38f60$a601280a@.phx.gbl...
> >> >> Hi, I have a database with two data files, both
> >> belonging
> >> >> to the PRIMARY filegroup. I've emptied one of the
> files
> >> >> and would like to modify that file's filegroup to
> >> >> SECONDARY so that I can remove it from my database.
> >> >>
> >> >> Using the alter database modify filegroup would
> impact
> >> my
> >> >> entire database and not to a specific file.
> >> >>
> >> >> Any suggestions. Thanks.
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >
Changing Filegroup for Text/Image Data SQL 2005
I have created a new filegroup to store text/image data for a couple of
tables, but when I try to change the filegroup in management studio it always
changes back to the default filegroup. Any suggestions?
Thanks,
AdrewCan you be more specific on what you mean by try to change it? In order for
the LOBs to be stored on a specific Filegroup you need to specify that when
you create the table, not afterwards. And if you are in SQL2005 you should
be looking at using the new MAX datatypes and not Text & Image.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"chabotwvu" <chabotwvu@.discussions.microsoft.com> wrote in message
news:2688797F-1B5D-447D-AC5E-E79AF175ADF5@.microsoft.com...
>I have created a new filegroup to store text/image data for a couple of
> tables, but when I try to change the filegroup in management studio it
> always
> changes back to the default filegroup. Any suggestions?
> Thanks,
> Adrew
tables, but when I try to change the filegroup in management studio it always
changes back to the default filegroup. Any suggestions?
Thanks,
AdrewCan you be more specific on what you mean by try to change it? In order for
the LOBs to be stored on a specific Filegroup you need to specify that when
you create the table, not afterwards. And if you are in SQL2005 you should
be looking at using the new MAX datatypes and not Text & Image.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"chabotwvu" <chabotwvu@.discussions.microsoft.com> wrote in message
news:2688797F-1B5D-447D-AC5E-E79AF175ADF5@.microsoft.com...
>I have created a new filegroup to store text/image data for a couple of
> tables, but when I try to change the filegroup in management studio it
> always
> changes back to the default filegroup. Any suggestions?
> Thanks,
> Adrew
Subscribe to:
Posts (Atom)