unused space in sql table - 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)

unused space in sql table

Author

Topic

gavis
Starting Member

39 Posts

Posted - 2008-10-23 : 07:17:30

hi.
I got a scripts which determines unused space in a table.
is there a way to recover this space.
in the properties window of the DB shows the space available is much less then the space availbe on the table.
i did try shrinking the db but it did not make a difference

gavis
Starting Member

39 Posts

Posted - 2008-10-23 : 07:24:08

is there a way to shrink a specific table?

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-10-23 : 07:47:10

You can use dbcc shrinkdatabase or dbcc shrinkfile but you cannot shrink table unless you have it stored in a file group of its own.
The free space reserved can be used for new data being added without SQL Server having to go and reserve more space which will slow transaction. Unless there is a vast amount of unused space or you are short of hard-disk space I would leave it alone. If you are short of disk space - better to get more than having to constantly tweak space used.
You could try defragmenting / reindexing which should use space more efficiently.

ghemant
Starting Member

22 Posts

Posted - 2008-10-23 : 08:05:10

Hi,
You should run DBCC SHOWCONTIG against that particular table to see if there is a fragmentation and then accordingly you could take further action to reduce fragmentation if there is any..

Regards
Hemantgiri S. Goswami
MS SQL Server MVP
http://hemantgirisgoswami.blogspot.com/

gavis
Starting Member

39 Posts

Posted - 2008-10-23 : 08:10:58

the current database size is about 30 gbs the unused space is around 15 gbs but the problem is sql doesnt use this free space as the table still grows, so i had to enable auto growth. the db is on a 80 gb HDD.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-23 : 08:14:02

Agreed with darkdusky.Good Explanation. You shouldn't shrink your database unless you are short of disk as it will disorganize index and data pages resulting to fragmentation

Run sp_spaceuused and post the result.

gavis
Starting Member

39 Posts

Posted - 2008-10-23 : 09:22:25

after running sp_usedspace,
the 2nd row has a valuue of 17GB unused space.
so by defragging the db, would it fix it.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-23 : 10:03:47

What do you get when you run:
sp_spaceused 'tablename'

Post full result as it is.

gavis
Starting Member

39 Posts

Posted - 2008-10-23 : 10:14:36

Database DB SIZE unallocated space
testDB 23698.00 MB 413.65 MB

reserved data index_size unused
23826792 KB 3222440 KB 3497888 KB 17106464 KB

gavis
Starting Member

39 Posts

Posted - 2008-10-23 : 10:16:22

the unused : 17106464KB
index_size : 3497888 KB
data:3222440 KB
reserved : 23826792 KB

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-23 : 10:28:11

Looks like you have lots of heap table(without clustered index) in your database wasting lots of pages causing internal fragmentation.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources