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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

db - 18 Mar 2008 21:26 GMT

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.

Signature

ontario, canada

Reply to this Message

Tibor Karaszi - 18 Mar 2008 21:41 GMT

> 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

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> SQL server 2000 sp3
> Hi
[quoted text clipped - 9 lines]
> with 450 gb). What is the best method to delete the secondary datafile and
> add that much space to primary datafile.

Reply to this Message

bass_player - 19 Mar 2008 09:08 GMT

I'm just curious as to what happens when you empty the database files

>> What is the best method to delete the secondary datafile and
>> add that much space to primary datafile.
[quoted text clipped - 29 lines]
>> and
>> add that much space to primary datafile.

Reply to this Message

Tibor Karaszi - 19 Mar 2008 10:31 GMT

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.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> I'm just curious as to what happens when you empty the database files
>
[quoted text clipped - 22 lines]
>>> with 450 gb). What is the best method to delete the secondary datafile and
>>> add that much space to primary datafile.

Reply to this Message

db - 19 Mar 2008 16:41 GMT

Thanks Tibor.
Bass: In my case pages were not moved as the secondary file was empty, and
file was deleted.

Signature

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
[quoted text clipped - 26 lines]
> >>> with 450 gb). What is the best method to delete the secondary datafile and
> >>> add that much space to primary datafile.

Reply to this Message