SQL Server is not used, but table space is allocated

Question

I have ms sql databases that grow very large. After checking, I found that some tables have a bunch of unused space. I do not do many physical deletions, so I do not think that these are just deleted records. DBCC SHRINK does not make the file smaller. But if I drop the table into a new, empty database, the size will decrease by about 80%. Instead of the 7gb that I have in this table in the current database, I get about 1.5 GB in the new database. It is as if the sql server is allocating too much memory. Has anyone come across this before? I would like to be able to compress the table by deleting unused allocated space without creating a whole new database.

Additional Information:

The full recovery model is used. I will try to restore the indexes, I think it has been a while. ldfs are shortened daily, using some wacky stored proc that clip them.

Answer 1

I found that if you do not take a backup of your transistor log file (LDF), you will get something like this. I cannot stress the importance of having good backup hygiene. Not only will it save your bacon if something goes wrong, but I will also help keep a good, solid database.

Answer 2

I do not do many physical deletions

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

Answer 3

In the parameters you can specify how much you want to increase. By default, I think that it is 10%, so given the 200 MB database, when you fill out your last page, it will allocate another 20 MB of page space. At 7 GB, he would allocate 700 MB.

I do not know exactly where you can change it after creating db, but I know that it has it when creating db. a little google work is likely to show you the answer.

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

Answer 4

This has worked for me in the past.

USE [DBNAME] GO DBCC SHRINKFILE (N'FILENAME' , 0, TRUNCATEONLY) GO 

Answer 5

I had a similar problem, and I believe that I found that reindexing / compression did not return all the unused space if there was no clustered index in this table.

Answer 6

It is possible that the table was built with the addition of an index included. The reason people create the added index is to prevent page breaks.

Right-click on the table in SQL Manager and select SCRIPT TABLE. Then see if PAD_INDEX=OFF . If PAD_INDEX used, it is likely where the table is taking up space.