Unable to shrink datafiles

  • Hello,

    We have a 600 GB 2008 database in production. Some tables were removed from the database and we have around 240 GB empty space in the database. We need to utilize the space for other database.

    The database has two data files of sized 320 and 275 GB respectively.

    I tried to shrink the data files with truncate but the space was not released.

    Can you please help me out in releasing the space from the database?

    Appreciate your quick help.

    Thanks

  • What command did you run?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • truncating or deleting never returns file space to the server; it merely clears the internal tables so SQL can reuse the same space. SQL continues to reserve all that space for itself, as it's perfectly normal to delete data and add new data in it's place.

    shrinking a database should be a super rare event...shrinking introduces fragmentation, and other woes; see this article for details:

    http://www.sqlskills.com/blogs/paul/post/why-you-should-not-shrink-your-data-files.aspx

    if you really want to shrink the database, and you are sure you don't need the freed space for new data taht will be added to your database soon, the easiest way is to use the Task in SSMS:

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks for your reply.

    I am trying to shrink the datafiles using SSMS.

    I am trying the same thing that lowel has suggessted. Still it is not working.

    The datafiles are showing that there is 55% free space but when I shrink it it remains the same.

  • Please note that we can take the database down for 1 dayif required.

    Is there any other way to achieve the solution?

  • What exact options did you chose? If you don't tell us that, we can't help you.

    Shrinkdatabase/shrinkfile is how you reduce the size of the data file.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Soory for the inconvenience.

    I have followed the following steps:

    Tasks-> Shrink -> Files

    Select a data file(.NDF) of Primary file group.

    Select radio button for :

    Reorganize pages before relaesing unused space

    Shrink file to

    I have provided shrink upto 1 GB more than the actual data size.

    Still the size of the datafile continues to be the same.

    Please let me know if you need any other details.

  • Could be there's something still in use, held active at the end of the file. Try shrinking in smaller chunks, that often works better than one large chunk.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks Gail for your help.

    Is there anyway to take a backup, eliminate the space while restoring the database?

  • No.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I restored a copy of Prod (3 TB) on my Dev server, ran a purge routine to remove patient data and now trying to shrink. My initial pass at running the shrink database option in SSMS only reclaimed 1 TB. I then tried using the shrink datafile option on all the data files (Reorganize pages and shrink file to the minimum required) and reclaimed about another 800 GB - several passes at it and also shrinking in smaller chunks. However, I still see about 300 GB of free space.

    I've checked for ghost records...None

    The source DB has index fragmentation...I'm wondering if an index rebuild of a few of the larger tables will help...Any other ideas?

    Thanks...

    KU

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply