Reducing size of .ndf file in Ms Sql 2005


I am having some space problems here, would appreciate if anyone can help.

I have a huge table (table A) with 20 million records in filegroup FILE1 (having one .ndf file). I had to create a copy of this table (table B), move data from table A to table B and then delete Table A. After deleting Table A I see that the .ndf file size has grown to more than double its original size, and I am not able to free up space from it.

dbcc shrinkfile (FILE1, truncateonly)
dbcc shrinkfile (FILE1, notruncate)

HI,

Did you do them in that order?
Did the notruncate take a long time?
If so try the truncate one again.

How much free space do you have in the file?

Hi,

no I ran it in this order
1. dbcc shrinkfile (FILE1, notruncate) (took 15 secs to run)
2. dbcc shrinkfile (FILE1, truncateonly)

i tried doing 1. a couple of times and everytime it takes about 15 decs

how do I find out free space in the file?

Hi,

Easiest way is in management studio.
Right click on database, tasks, shrink, files
Select the file and it will shhow the free space.

Also run dbcc opentran to see if there are open transactions

Also check sysprocesses to make sure the checkpoint task isn't stuck.

Hi,

no open transactions
checkpoint task is not stuck
The ID i'm using does not have access to see the files in enterprise manager...could that be a reason?

Hi,

>> The ID i'm using does not have access to see the files in enterprise manager...could that be a reason?
It could be but I would expect an error.

You will at least need to be dbo.

try sp_spaceused

HI,

is it true that for shrinkfile to work, the table should have a clustered index?

Hi,

No - you're shrinking a database file not a table