Unused space - 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 2000 Forums

SQL Server Administration (2000)

Unused space

Author

Topic

iromo
Starting Member

3 Posts

Posted - 2004-06-03 : 06:26:05

I have some tables with n rows and when I run sp_spaceused procedure, the information that displays me is:

rows reserved data index_size unused
MI_TABLE 1725066 15624576 KB 5330904 KB 4567792 KB 5725880 KB

As you look us, unused space is double greater than data.

Could I free this space?

Thanks.
IR

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-06-03 : 06:48:10

Yes you can. Lookup DBCC SHRINKDATABASE and DBCC SHRINKFILE in BOL.

Raymond

iromo
Starting Member

3 Posts

Posted - 2004-06-03 : 08:17:18

The problem is that I had a DB with 6Gb, and with DTS I copied only data to other DB.
Later I ran create index & primary keys.
Last old DB's backup is 6Gb, and new DB's backup is 22Gb.
I have 8 files on primary:

File Current Size Space used
1 2048 1924
2 2048 1923
3 2048 1923
4 2048 1923
5 2048 1923
6 2048 1004
7 2048 934
8 13000 12431

I ran DBCC SHRINKDATABASE and DBCC SHRINKFILE, but the problem continue.

I want free unused pages on tables, is there any way?

Thank you, very much.
IR

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-06-03 : 08:24:25

SHRINKDATABASE should re-organise your data and reclaim unused space. What FILLFACTOR did you use on your indexes?

Raymond

iromo
Starting Member

3 Posts

Posted - 2004-06-03 : 08:46:26

I use fillfactor 10.

IR.

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-06-03 : 08:53:15

So you have asked that each index page be only 10% full which is why you have as much free space as used space. You probably meant a FILLFACTOR of 90.

Raymond

surefooted
Posting Yak Master

188 Posts

Posted - 2004-06-03 : 12:51:34

Have you ran DBCC Updateusage? If not run that and then sp_spaceused to get an accurate pictuer of the space used.

-Jon
Just a starting member.

MuadDBA

628 Posts

Posted - 2004-06-03 : 14:36:45

quote:
Originally posted by iromo

I use fillfactor 10.

IR.

yeah, that would do it. rerun it with fillfactor 90 and watch your database shrink away.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources