Resizing a full database

[Michael Reisman]

I have a database which is quickly filling up its allotted data space (the database is 500MB, and the device is also 500MB). I wanted to know whether it was an acceptable procedure to:

-Dump the database
-Create a new 600MB (or more) device
-Create a 600MB database for load on that device
-Restore the dump to it.

Are there data integrity/corruption/fragmenting issues with this method? The alternatives are simply expanding the database across another device (or on the same device using DISK RESIZE), or using scripts and bcp to recreate the database from scratch. These do not seem ideal. Any opinions or comments would be greatly appreciated.

MR

[Don Romano]

The DUMP and then LOAD should work fine. Expanding the database across another device may cause some fragmentation. The DISK RESIZE should also work fine. I`m assuming that would not fragment data. Unloading and reloading the database tables would also work and would probably help performance by rebuilding all of the database pages if they are fragmented.

In summary... pick one... they all look reasonable. If you have time and the desire the bcp will reorganize/rebuild the data on the tables but if things are fine right now than the DISK RESIZE or DUMP/LOAD options should be fine. Just make sure you DBCC and backup your database carefully before ou do anything. :-)

Don

On 9/2/98 2:20:46 PM, Michael Reisman wrote:
> I have a database which is quickly filling up its allotted data space (the
> database is 500MB, and the device is also 500MB). I wanted to know whether
> it was an acceptable procedure to:

-Dump the database
-Create a new
> 600MB (or more) device
-Create a 600MB database for load on that
> device
-Restore the dump to it.

Are there data
> integrity/corruption/fragmenting issues with this method? The alternatives
> are simply expanding the database across another device (or on the same
> device using DISK RESIZE), or using scripts and bcp to recreate the
> database from scratch. These do not seem ideal. Any opinions or comments
> would be greatly appreciated.

MR

[Michael Reisman]

Thanks, don!