> ---Original Message-----
> From: Davis Ralph [mailto:mssqldba-ezmlmshield-<snip>
> I prefer using SHRINKFILE and do my database files independently. Also,
> you should expect performance to degrade while this is going on because it
> is shifting all the data in the database file. Only do it during off
> hours. The device size won't change until the run is completely finished
> with the database file.

I have measured the performance degradation and it is bearable. The problem is that we're talking about a central enterprise database that cannot go off-line for more than one day without serious revenue repercussions, that is well over 100GB, and that needs to be partitioned. My main goal in this phase of the project is to separate out some historical & aggregated portions of the database making the transactional portion smaller and more agile, but the benefit of horizontal table partitioning is reduced if I cannot reduce the size of the transactional database, and therefore the time window required to recover from backup in the event of device problems.

My understanding is that shrinkfile will only work for trimming air from devices, so that if there is heavy fragmentation in the objects inside the device, shrinkfile does not necessarily confer any reduction in disk-space without the reorganization in shrinkdatabase.

My last attempt at dbcc shrinkdatabase has been running for 2 days now - I have little reason to interrupt it, but also little faith that it is doing anything!

What troubles me is that I don't see how DBCC SHRINKDB can possibly succeed while there's transactional activity against the database - I would assume the addition of new rows would just keep fragmenting what the parallel DBCC process has just finished de-fragmenting.... so is it possible to shrink a database that cannot be down as long as it might take a shrinkdatabase operation to complete?

Best,

Juan Cristián


MS Sql Server LazyDBA home page