SQL Server Performance

How can I reduce Data file size following bulk delete ?

Discussion in 'Performance Tuning for DBAs' started by Chris Tunstead, Sep 17, 2007.

  1. Chris Tunstead New Member

    I've recently performed a delete on 3 huge tables in a 100Gb database, which believed would have freed up approx 40Gb in the Data file of the database. Instead it only freed around 4Gb. How can I recover further disk space ? Will a DBCC REINDEX of these tables potentially free up a large amount of space following the deletes I performed ?
    I know the DBCC SHRINKDATABASE can be used to make some of the free space in the data file available to the server, but before I can run that I need to recover more disk space from the data file itself.
    Chris Tunstead, Sep 17, 2007
    #2

  2. satya Moderator

    Are you looking at the Enterprise Manager for the size of database?
    Do you have any text/image columns within this database?
    If so then it may not be reporting correctly, you could run SP_SPACEUSED @UPDATEUSAGE='TRUE' from the query analyzer to see the available space on this database.
    satya, Sep 17, 2007
    #3

  3. Chris Tunstead New Member

    Yes there are text columns in those tables.
    I had been using Enterprise Manager for the database / table sizes, but I tried updateusage after the deletes to see if that corrected the figures. Below is the output from SP_SPACEUSED @UPDATEUSAGE='TRUE'
    database_name database_size unallocated space
    ABC 110055.75 MB 2675.13 MB
    reserved data index_size unused
    104915840 KB 82118768 KB 9055624 KB 13741448 KB
    The unallocated space size is much lower than I expected. I would have hoped / expected the figure to be closer to 20Gb, based on the number of rows I have deleted so far and the average size (in Kb) of each row in that table.
    I've noticed the second portion of the output above has an unused value of 13Gb...what's the difference between that and the unallocated space ? What I need is to be able to free 5 - 10Gb back to the disk for other purposes on the server, as well as having as much unallocated space as possible within the database data file for it to use for new records.
    Could a reindex help or any other ideas why I'm not seeing the free space I expected ?

    Chris Tunstead, Sep 17, 2007
    #4

Share This Page