delete one secondary data file and increase size of primary data f

SQL server 2000 sp3
Hi
I have a sql server 2000 database. In one application initial database size
was 13 gb. We created primary data file for required size. Application later
required 430 gb more space. We added another datafile (secondary) with 430
gb. The problem is the application does not allow us to specify where to put
the file. Looks like it just load the data in the primary datafile and then
does not try to load files in secondary datafile and secondary datafile is
not being used.
Application developer wants me to just have one datafile (Primary data file
with 450 gb). What is the best method to delete the secondary datafile and
add that much space to primary datafile.
--
ontario, canada> What is the best method to delete the secondary datafile and
> add that much space to primary datafile.
1. Make sure the primary file can accomodate the data. Expand if neccesary. Something like
ALTER DATATBASE dbname MODIFY FILE (name = logical_filename, size = xGB)
2. Empty the secondary file. Use DBCC SHRINKFILE and the EMPTYFILE option.
3. Remove that now empty file from the database:
ALTER DATABASE dbname REMOVE FILE logical_file_name
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"db" <db@.discussions.microsoft.com> wrote in message
news:042E6067-EB2D-4AF9-9A2A-8998172D16B8@.microsoft.com...
> SQL server 2000 sp3
> Hi
> I have a sql server 2000 database. In one application initial database size
> was 13 gb. We created primary data file for required size. Application later
> required 430 gb more space. We added another datafile (secondary) with 430
> gb. The problem is the application does not allow us to specify where to put
> the file. Looks like it just load the data in the primary datafile and then
> does not try to load files in secondary datafile and secondary datafile is
> not being used.
> Application developer wants me to just have one datafile (Primary data file
> with 450 gb). What is the best method to delete the secondary datafile and
> add that much space to primary datafile.
>
> --
> ontario, canada|||I'm just curious as to what happens when you empty the database files
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:F2A7EDC3-DBD2-46BB-854E-B1E27BAB2651@.microsoft.com...
>> What is the best method to delete the secondary datafile and
>> add that much space to primary datafile.
>
> 1. Make sure the primary file can accomodate the data. Expand if
> neccesary. Something like
> ALTER DATATBASE dbname MODIFY FILE (name = logical_filename, size = xGB)
> 2. Empty the secondary file. Use DBCC SHRINKFILE and the EMPTYFILE option.
> 3. Remove that now empty file from the database:
> ALTER DATABASE dbname REMOVE FILE logical_file_name
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:042E6067-EB2D-4AF9-9A2A-8998172D16B8@.microsoft.com...
>> SQL server 2000 sp3
>> Hi
>> I have a sql server 2000 database. In one application initial database
>> size
>> was 13 gb. We created primary data file for required size. Application
>> later
>> required 430 gb more space. We added another datafile (secondary) with
>> 430
>> gb. The problem is the application does not allow us to specify where to
>> put
>> the file. Looks like it just load the data in the primary datafile and
>> then
>> does not try to load files in secondary datafile and secondary datafile
>> is
>> not being used.
>> Application developer wants me to just have one datafile (Primary data
>> file
>> with 450 gb). What is the best method to delete the secondary datafile
>> and
>> add that much space to primary datafile.
>>
>> --
>> ontario, canada
>|||DBCC SHRINKFILE using the EMPTYFILE option will "push" pages over to other file, causing them to
autogrow if they can't accommodate this. These operation is logged so one need to be very cautious
with the transaction log if lots of pages will be "moved" to other files.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bass_player" <bass_player@.mvps.org> wrote in message
news:%235OUzhZiIHA.5820@.TK2MSFTNGP04.phx.gbl...
> I'm just curious as to what happens when you empty the database files
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:F2A7EDC3-DBD2-46BB-854E-B1E27BAB2651@.microsoft.com...
>> What is the best method to delete the secondary datafile and
>> add that much space to primary datafile.
>>
>> 1. Make sure the primary file can accomodate the data. Expand if neccesary. Something like
>> ALTER DATATBASE dbname MODIFY FILE (name = logical_filename, size = xGB)
>> 2. Empty the secondary file. Use DBCC SHRINKFILE and the EMPTYFILE option.
>> 3. Remove that now empty file from the database:
>> ALTER DATABASE dbname REMOVE FILE logical_file_name
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "db" <db@.discussions.microsoft.com> wrote in message
>> news:042E6067-EB2D-4AF9-9A2A-8998172D16B8@.microsoft.com...
>> SQL server 2000 sp3
>> Hi
>> I have a sql server 2000 database. In one application initial database size
>> was 13 gb. We created primary data file for required size. Application later
>> required 430 gb more space. We added another datafile (secondary) with 430
>> gb. The problem is the application does not allow us to specify where to put
>> the file. Looks like it just load the data in the primary datafile and then
>> does not try to load files in secondary datafile and secondary datafile is
>> not being used.
>> Application developer wants me to just have one datafile (Primary data file
>> with 450 gb). What is the best method to delete the secondary datafile and
>> add that much space to primary datafile.
>>
>> --
>> ontario, canada|||Thanks Tibor.
Bass: In my case pages were not moved as the secondary file was empty, and
file was deleted.
--
ontario, canada
"Tibor Karaszi" wrote:
> DBCC SHRINKFILE using the EMPTYFILE option will "push" pages over to other file, causing them to
> autogrow if they can't accommodate this. These operation is logged so one need to be very cautious
> with the transaction log if lots of pages will be "moved" to other files.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "bass_player" <bass_player@.mvps.org> wrote in message
> news:%235OUzhZiIHA.5820@.TK2MSFTNGP04.phx.gbl...
> > I'm just curious as to what happens when you empty the database files
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> > news:F2A7EDC3-DBD2-46BB-854E-B1E27BAB2651@.microsoft.com...
> >> What is the best method to delete the secondary datafile and
> >> add that much space to primary datafile.
> >>
> >>
> >> 1. Make sure the primary file can accomodate the data. Expand if neccesary. Something like
> >> ALTER DATATBASE dbname MODIFY FILE (name = logical_filename, size = xGB)
> >>
> >> 2. Empty the secondary file. Use DBCC SHRINKFILE and the EMPTYFILE option.
> >>
> >> 3. Remove that now empty file from the database:
> >> ALTER DATABASE dbname REMOVE FILE logical_file_name
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "db" <db@.discussions.microsoft.com> wrote in message
> >> news:042E6067-EB2D-4AF9-9A2A-8998172D16B8@.microsoft.com...
> >> SQL server 2000 sp3
> >> Hi
> >> I have a sql server 2000 database. In one application initial database size
> >> was 13 gb. We created primary data file for required size. Application later
> >> required 430 gb more space. We added another datafile (secondary) with 430
> >> gb. The problem is the application does not allow us to specify where to put
> >> the file. Looks like it just load the data in the primary datafile and then
> >> does not try to load files in secondary datafile and secondary datafile is
> >> not being used.
> >>
> >> Application developer wants me to just have one datafile (Primary data file
> >> with 450 gb). What is the best method to delete the secondary datafile and
> >> add that much space to primary datafile.
> >>
> >>
> >>
> >> --
> >> ontario, canada
> >>
>
>