Menu
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.
Additional information:
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.
809
Export all the rows to a new table and move the rows back. This reorganizes the LOB data and release the unused space. Use DBCC SHRINKFILE with EMPTYFILE option to move all the data to a newly added data file and then remove old data file. This reorganizes the LOB data there by releasing the unused space.
To my knowledge, unallocated space is storage that has been allocated to the database, but not yet to a specific object. Unused space is reserved for existing tables(indexes too?) to grow in.
In Azure SQL Database, to shrink files you can use either DBCC SHRINKDATABASE or DBCC SHRINKFILE commands: DBCC SHRINKDATABASE shrinks all data and log files in a database using a single command. The command shrinks one data file at a time, which can take a long time for larger databases.
drop command will free disk space automatically. Note: Assuming you are using innodb_file_per_table as you are able to free space by optimize table syntax.
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.
95
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
44
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
