SQL Server Performance

How to reduce reserved size of a table

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by and.zanette, Feb 18, 2008.

  1. and.zanette New Member

    Hello, I need to reduce the size of my database. I've done a DBCC DBREINDEX and than a shrink of the database, but I noticed that there is a table that have a reservedSize of 2GB ! This table have only 4 columns and not many rows. How can I reduce the reserve size of this table ?
  2. satya Moderator

    Welcome to the forum!
    How often you perform the DBCC DBREINDEX and what is the table growth in a month or so?
    If the free disk space is not a problem then why to shrink and leave the current settings as is, in this case you can take advantage of DBCC SHRINKDATABASE or SHRINKDB (see updated SQL books online for code example) to shrink the database.
    Overall this will have disadvantage or negative performance in shrinking and othe process such as reindexing on the database, carefully consider the shrink operation.
  3. and.zanette New Member

    I've to reduce database size because I need it for demos. Than I've to obtain the minimum db size possible. Do you know how can I directly reduce the reserved size of a table without using DBCC SHRINKDATABASE (I've just done it). I've done DBCC DBREINDEX one time before DBCC SHRINKDATABASE
  4. satya Moderator

    You say this is for demos, that means every time you need to get the databsae to this size and if you can afford just few mins downtime why not simply script the current database's logins, packages or any jobs along with data then drop the database with a recreation of the same using that script.
    BTW if you need to drop the number of rows on that table, then you could use DELETE statement or for all the rows TRUNCATE table.
  5. and.zanette New Member

    But I have to use an existing db for demos. Than we have a db, we delete for example all the data before '1/1/2007', and we want to use this db. But it is too large and we don't know why. There is this table that have a reserved space ( you con see this using sp_spaceused ) of 2 GB ! Do you know what is this space and how can I decrease that ?
  6. satya Moderator

    The size of database includes both .LDF log file and .MDF data file, because of the action such as DBCC DBREINDEX the log file will be growing that will show as huge database at the end of your tasks. BEtter to perform frequent log backups in order to reduce such a confusion.

Share This Page