Unable to reduce SQL DB size on Disk

Deep Raj- DG IT 20 Reputation points
2023-01-26T09:15:12.7566667+00:00

Hi I have SQL DB whose size is 20 GB if i add tables sizes. But despite of shrinking of DB it says size on Disk allocated is 90GB approx. I see no reason why it will take that much space on Disk. When i backup it is of 12 GB only but while restoring it ask for more space something 90 GB. I am not sure what all causing this. I need some help urgently as i need to restore it on VPS which have less space but enough for DB if it show correct size based on Table Rows Sise.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,710 questions
0 comments
Accepted answer
  1. Olaf Helper 40,816 Reputation points
    2023-01-26T09:27:40.61+00:00

    How did you figure out the size of the database? What are the file sizes of MDF and LDF files?

    What do

    exec sp_spaceused

    report?

    Backup files can be compressed and so much smaler in size then the database.

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101K Reputation points MVP
    2023-01-27T19:05:55.4933333+00:00

    So it is the log file that is big, not the data file. The log file has no relation to the size of the data in the tables. The size of the log file depends on what operations you run. Let's say that you do:

    BEGIN TRANSACTION
UPDATE tbl SET ...
DELETE tbl ...
INSERT tbl ..
COMMIT TRANSACTION

    And all these operations affect the better part of your 20 GB table, these rows has to be logged in the log file, and the log file can be truncated beyond the oldest active transaction. And for that matter, it cannot be truncated if you are in full recovery and you don't take transaction log backs.

    It is a bit unusual, though, that the log file exceeds the size of the data file, and even more so with a factor of six. So you could try to shrink it with DBCC SHRINKFILE. But if you have not backed up your transaction log (and you are in full recovery), it will have no effect.

    In any case, don't delete the LDF file - that is likely to render the database unsuable..

    1 person found this answer helpful.
    0 comments
  2. Deep Raj- DG IT 20 Reputation points
    2023-01-26T23:32:08.5533333+00:00

    Hi i deleted LDF file which was 78 GB it self and attached new one. But i don't think this is right.

    This is size i am getting

    M1_F2 12951.56 MB 80.92 MB

    But i dont want to do on production server as ifi detached DB it can cause issue.

    0 comments
  3. Olaf Helper 40,816 Reputation points
    2023-01-27T07:05:28.2966667+00:00

    But i don't think this is right.

    You are right, it isn't right and can cause issues. It should be possible to shrink a database/log file with DBCC SHRINKFILE (Transact-SQL)