Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    57

    Unanswered: 'Space Available' Issue

    I posted a question about 'space available' earlier to this board (how much should i shoot for) adn got some very helpful replies.

    Now a new 'Space Available' question.

    We are upgrading tables, stored procedures and views in a sql server database. One of the enhancements involves clearing almost all of the records in the database. The database contains healthcare claim data. The Deletes removed the claims and cleared tables with aggregated data.

    Prior to the upgrade teh database reported a 'Size' of 52 gigs with about 14 gigs of 'Space Available'.

    Post upgrade the database reports a 'Size' of 48 gigs with about 38 gigs of 'Space Available'.

    We're hoping to get the database to 'give back' some of the 38 gigs of space it is holding. In order to accomplish this we have tried:
    1) the maintence utility that 'gets back' space -- 1 or 2 gigs impact.
    2) reindex and run space utility -- no impact.

    Other Constraints. It is a production database for which we do not have an exact replica in test. It would be a significant effort to move the db to a secure test environment. Translation: We have to be careful what we 'try' in terms of fixes. We can certainly go to a backup but it is not a test environment.

    And advice or suggestions that folks might have in terms of how to manage 'Space Available' are appreciated.

    Regards


    rayko

  2. #2
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    dbcc shrinkfile or dbcc shrinkdatabase. I bet most of that unused space is in the transaction log, that has extended and been truncated.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The numbers you are looking at may be a tad off. Run DBCC UPDATEUSAGE(0) in the database to true up the size numbers. This may take a while, but I believe it will not hurt you as far as performance or locking tables. After that is done, I would expect you have less than 38GB free.

    If you happen to have the 38GB free, and absolutely need to reclaim space, then you may be stuck with having to bcp out the data, drop a table (or two), shrink the database, then import the data back into the database. This will require a bit of downtime, depending on how big/numerous the tables that are left are.

  4. #4
    Join Date
    Jun 2004
    Posts
    57
    A few questions.

    1) space in teh transaction log. How do i check that? That sounds like an 'easy' fix.
    2) dbcc shrinkfile and dbcc shrinkdatabase make me nervous. I have a, perhaps unfounded, perception that these are 'not smart' utilities and may not be the best way to manage 'space available'. This could very well be nothing more than my own ignorance.

    Thanks again.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Use this to see which files/filegroups are being used, or are just taking up space:
    Code:
    select fileproperty(f.name, 'spaceused')/128 as "UsedSpace MB", f.size/128 as "FileSize MB", isnull (g.groupname, 'LOG') as "FileGroup", f.name as "FileName"
    from sysfiles f left join sysfilegroups g on f.groupid = g.groupid
    Be careful about the transaction log, as the space used in it will grow until the next transaction log backup if the database is in full recovery mode.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •