Data file refuses to release free space to OS

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

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. *~

Hi,


Try Dbcc Shrinkfile with truncateonly option.



N.Babu

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

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

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

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

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

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

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

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

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

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 [?]