SQL Server unused, but allocated table space

Question

I have ms sql databases that grow very large. Upon examination I find that there is a bunch of unused space in certain tables. I don't do many physical deletes, so I don't think that its just deleted records. DBCC SHRINK doesn't make the file smaller. But, if I dump the table to a new, empty database, the size goes down about 80%. Instead of the 7gb I have in this table in the current database, I end up with about 1.5gb in the fresh database. Its as if sql server is allocating too much memory. Anyone encountered this before? I'd like to be able to shrink the table by removing unused allocated space without having to create a whole new database.

tia Don

Answer 1

have you tried rebuilding the index?

Answer 2

In the options, you can specify how much you want to grow by. By default i believe it's 10%, so given a 200MB database, when you fill your last page, it will allocate another 20MB of page space. At 7GB it would allocate 700MB.

I don't know exactly where you can modify it after you create a db, but i know it has it when you create the db. a little google work will most likely reveal the answer to you.

NOTE: my answer is not how to fix it, but maybe how to prevent / explain why you might see all this unallocated space.

Answer 3

I have found that if you do not take care to backup your transistion log file (the LDF) you will get something like this behavior. I can not stress enough the importance of having good backup "hygiene". Not only will it save your bacon if something goes wrong but I will also help maintain a nice tight database.

Answer 4

I don't do many physical deletes

what about updates of the table, what is the fragmentation level. run DBCC SHOWCONTIG, then rebuild the index if it is highly fragmented. After that do a BACKUP LOG WITH TRUNCATE_ONLY followed by a SHRINK command

Answer 5

Have a look at this Knowledge Base article and see if it applies:

http://support.microsoft.com/kb/913399

Answer 6

full recovery model used. i'll try rebuilding the indices, i think its been a while. ldf's are shrunk daily using some wacky stored proc that truncates them.

thanks don

Answer 7

ok, i think i needed more research before posting the question. perhaps the results of the sp_spaceused stored proc will help.

on a database that seems to be "out of control" (17gig), i have the following results on a large table: rows = 3155923 reserved = ~ 7.1gig data = ~ 1.1gig index = 1.7gig unused = 4.3 gig

compare that with a database whose size seems ok (6gb): rows = 2057031 reserved = ~ 1.6gig data = ~ .6gig index = ~.6gig unused = ~.3gig

so, with the first database there is 4.3 space used and on the second .3 gig. that leaves me with 2 questions: 1. how do i control the growth of the unused space 2. how do i get rid of it once its there without dumping the data to an empty table.

tia, don

Answer 8

This has worked for me in the past

USE [DBNAME] GO

DBCC SHRINKFILE (N'FILENAME' , 0, TRUNCATEONLY)

GO

Answer 9

I had a similar problem once and I believe that I found that reindex/shrinking didn't reclaim all of the unused space if there was no clustered index on a given table.

Answer 10

It is possible that the table was built with padding turned on for the index. The reason that people build a padded index is to prevent page splits.

Right click on the table in SQL Manager and select SCRIPT TABLE. Then look to see if PAD_INDEX=OFF. If PAD_INDEX is in use, that's probably where the table is taking up space.

Answer 11

Is this pertinent? KB article 924027 - SQL Server significantly increases the unused space for some tables