All geek questions in one place

Database size is huge

I have the following problem. We have a database that stores binary files in the database. We know that the database size can be large, so we deleted all the binary files from the database and used the “compress” task on it. Thus, we hoped that the database would be much smaller. Here are the results:

before the deletion the size was: 20 gigabytes after the deletion the size was: 25 gigabytes (including the log file) after shrinking the size: 13 gigabytes

now I have no idea where this 13-gigabyte comes from, the largest table in the database is the coffee table, and 1.3-gigabytes all the rest taken together do not accept 200 mb ...

Could it be that the log file still contains data that cannot be deleted from the compression task? is there a solution to this problem?

+3
source share
4 answers

If your recovery model is Full and you did not back up and then reduced your transaction log, it can still remain large.

Depending on your situation, one of the easiest ways to reduce the transaction log is to simplify your recovery model, and then compress the transaction log files and then restore the recovery model. However, if this is a production system in which you may need a time recovery point, then you should back up your transaction log.

+3
source

, :

EXEC sp_spaceused;
+3

( , , ) , , . 15 . , , , . , , .

0

One of the possibilities is that the table where you deleted the data is a heap (it means that there is no clustered index), and when you remove from the heap, the space allocated for the table will not necessarily be freed. Check out this article from MS: http://support.microsoft.com/kb/913399

0
source

Source: https://habr.com/ru/post/1714852/

More articles:


All Articles