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.
Full recovery model used. I'll try rebuilding the indexes, i think its been a while. ldf's are shrunk daily using some wacky stored proc that truncates them.
have you tried rebuilding the index?
Have a look at this Knowledge Base article and see if it applies: support.microsoft.com/kb/913399
Is this pertinent? KB article 924027 - SQL Server significantly increases the unused space for some tables
There is absolutely NO POINT at all in using full recovery model, if all you're doing to the LDFs is truncating them! The proper way to make them shrink is to take a backup of the log files, at which time they will be shrunk automatically. Someone who understands log chains and proper backup strategy is desperately needed to step in and help you get it right. Anything less puts you at dire risk of losing data.
Agreed, never forget to backup transaction logs as well as database backups. The log will grow until it eats your whole hard drive.