Database size is huge

Question

I have the following problem. We have a database that stores binaries in the database. We know the size of the database can be big so we removed all the binaries from the database and used the task "shrink" on it. This way we hoped that the database would be much smaller. These are the results:

before removal size was: 20 gigabyte after removal size was: 25 gigabyte (incl log file) after shrink size was: 13 gigabyte

now I have no idea where that 13 gig is from, the largest table in the database is a logtable and it's 1.3 gig all the rest combined doesn't take 200 mb ...

could it be that there is still some data in the logfile that the shrink task cannot remove? is there a solution for this problem?

thx.

Answer 1

If your recovery model is "Full" and you haven't backed up and then shrunk your transaction log then it could still remain large.

Depending on your situation, one of th easiest ways of shrink the transaction log is to set your Recovery Model to simple, then shrink the transaction log files and then set recovery model back to full.. However, if this is a production system where you may need a point in time recovery then you should perform backups of your transaction log instead.

Answer 2

To get more information about space usage you can try:

EXEC sp_spaceused;

Answer 3

Please remember to set up transaction log backups (not just database backups as they will not keep the log small as you have found out) after you take Robin Day's advice and shrink the log or your log will just grow large again. Our transaction logs are backed up every 15 minutes. Your schedule may need to be more or less frequent depending on how much data you can afford to lose if you have a failure. At a minimum I'd do a daily log backup just to keep the log a reasonable size.

Answer 4

One possibility is that the table you've deleted the data from is a Heap (means there is no clustered index), and when you delete from a heap the space allocated for the table won't necessarily be freed. Check this article from MS: http://support.microsoft.com/kb/913399