SQL Server Performance

Data file refuses to release free space to OS

Discussion in 'General DBA Questions' started by mhweiss2, Aug 18, 2006.

  1. mhweiss2 New Member

    I have a data file in a SQL Server 2k database that is one of three files in the PRIMARY filegroup. This file is approx 130gb in size and - after repeated shrinkings and trying to empty the file of data by moving to the other two files in the filegroup, SQL Server refuses to release the free space (approx 117gb is free according to EM) to the operating system. I am wondering if anyone has experienced something similar and what the resolution is? This 117GB of free space is sorely needed on the drive that holds that file...
    Thank you,
    Michael
  2. waqar Member

    Hi,

    I got similar problem if i try to shrink file via EM but if i use TSQL then it is ok.
    I use

    DBCC ShrinkFile(filename,1)


    ________________________________________________
    ~* Opinions are like a$$holes, everyone got one. *~
  3. Luis Martin Moderator

    Also confirm the information with:

    Use YourDataBase
    sp_spaceused @updateusage = 'TRUE'

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  4. babusamuel New Member

    Hi,


    Try Dbcc Shrinkfile with truncateonly option.



    N.Babu

    Don't try to do different things; Do things differently.
  5. satya Moderator

    I have seen such question in the forums, as the user baffled to see the information from flaky Enterprise Manager and I suggest what LUis is referring to get it.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  6. FrankKalis Moderator

    Did you happen to have BLOB data deleted in that file?

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  7. mhweiss2 New Member

    Thank you to all who have replied and offered suggestions - your help is greatly appreciated. Frank, no I did not have BLOB data deleted in that file but thank you for mentioning it - I read where that does create an issue. I have tried all suggestions and still have not been successful in recuperating the space. I now have the same issue on a second database and one commonality I noticed is that the file in each database that refuses to relinquish the free space is marked as file #1 in the PRIMARY filegroup. Does SQL Server treat the first file in a filegroup differently than the others? Any other suggestions on how to recapture this storage space would be very much appreciated.
    Thank you,
    Michael
  8. Luis Martin Moderator

    What settings (grow in datafiles) do you have for each filegroup.

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  9. SQL_Guess New Member

    What is the base size for the .mdf file? (properties on the db)

    I'm trying top recall - I found an sp that contiuously tried to shrink, but I think it was for the transaction log, trying to get rid of virtual logs. If I find the code, and it seems relevant, I'll post it...

    Panic, Chaos, Disorder ... my work here is done --unknown
  10. mhweiss2 New Member

    Luis, I am not sure if this is what you are asking or not but the files in the filegroup are set to grow unrestricted 50MB at a time. In both DBs in question, the PRIMARY filegroup is set as the default filegroup. Is it okay to change the default to another filegroup? Seems like it should be. Would you advise trying that to see if SQL Server will then release the space?
    The base size for each file is 20GB.
    Thank you,
    Michael
  11. Luis Martin Moderator

    Ok.
    So, all filegroups are setting with unrestricted 50MB?
    Also, there is any settins with minum size in any filegroup?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  12. mhweiss2 New Member

    Thank you, Luis. Please forgive my ignorance - I see in EM the current file size in the file properties but nothing about the minimum size. Is there a DBCC functon for finding this?
    Thank you,
    Michael
  13. sonnysingh Member

    Michael

    I had the same situation.. and I have taken following steps.. try if it's help you any which way. Truncate the log file (you might do not require to run this step) and shrink the log or data files.

    BACKUP LOG databasename with NO_LOG (or TRUNCATE_ONLY)
    DBCC SHRINKFILE (LOG FILE NAME,70,TRUNCATEONLY)
    DBCC SHRINKFILE (Data FILE NAME,20,TRUNCATEONLY)

    if still space is not released then use this
    DBCC SHRINKDATABASE (databasename,20)
    this will reduce 20 percentage of space

    it's work for me.. I have recovery model on 'BULK-LOGGED' mode
  14. mhweiss2 New Member

    Thank you. I tried running the commands you suggested and in addition I ran DBCC CHECKALLOC with REPAIR_REBUILD option. All of the files for the database look great now except for the one I am trying to shrink. It still remains at 135GB with (supposedly) 117GB of free space. I tried again shrinking the datafile in EM then shrinking the database [in EM] and still no luck. I am sure this issue has to do with this being file 1 in the PRIMARY group - I am thinking SQL Server must treat this file differently for some reason? I know it won't let me drop this file (having good backups I tried this earlier after moving the data to the other files in the file group and received an error message stating I cannot drop the first file in a filegroup). If anyone has any further thoughts or ideas, I would certainly appreciate hearing them.
    Thank you,
    Michael
  15. satya Moderator

    Can you check the free space by running SP_SPACEUSED @UPDATEUSAGE='TRUE' from Query Analyzer than looking at EM for information.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  16. mhweiss2 New Member

    Yes, Satya - I ran that in QA, went into EM, did a refresh on the database in question and EM tells me that there is 136gb of free space in the database. The file in question is reported as having 135GB of free space and 204 MB of used space. I am thinking this is left over stats, whatever from my emptying the file into the other three files in the file group.
    Any thoughts?
    Thank you,
    Michael
  17. mhweiss2 New Member

    I thought I should mention that I am running SP4 on SQL Server 2000, Windows Server 2003 SP1, PAE enabled, with a RAID 0 disk configuration.
    Thanks,
    Michael
  18. Luis Martin Moderator

    Could you post the result of:

    sp_helpfile

    Thanks.

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  19. joshuayip New Member

    This is my first posting =)

    I used the SHRINKFILE to shrink my 10 GB db( 6 GB data and 4 GB log) on both my data and log file. However, only the log file was shrunk successfully.

    Then I tried to use the SHRINKDATABASE command, my 6GB data file was shrunk to 70 MB.

    Does this command cause any data lost ? I am not a DBA ,as my work revolves mostly on coding, so I am not sure if I lost any data [?]
  20. FrankKalis Moderator

    quote:Originally posted by joshuayip

    This is my first posting =)

    I used the SHRINKFILE to shrink my 10 GB db( 6 GB data and 4 GB log) on both my data and log file. However, only the log file was shrunk successfully.

    Then I tried to use the SHRINKDATABASE command, my 6GB data file was shrunk to 70 MB.

    Does this command cause any data lost ? I am not a DBA ,as my work revolves mostly on coding, so I am not sure if I lost any data [?]
    No, it is very unlikely that your SHRINKDATABASE command caused a data loss.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  21. satya Moderator

    It will not touch the data and only perform the process to take out the free space in the data file.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.

Share This Page