Reducing size of .ndf file - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Reducing size of .ndf file

Author

Topic

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-04-22 : 12:32:55

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)

doesn't do anything, pl. help.

nr
SQLTeam MVY

12543 Posts

Posted - 2009-04-22 : 12:47:46

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?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-04-22 : 13:15:38

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?

nr
SQLTeam MVY

12543 Posts

Posted - 2009-04-22 : 13:36:56

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.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-04-22 : 14:04:13

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?

nr
SQLTeam MVY

12543 Posts

Posted - 2009-04-22 : 14:51:48

>> 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

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-04-22 : 16:08:14

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

nr
SQLTeam MVY

12543 Posts

Posted - 2009-04-22 : 16:15:31

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

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources