sql server: size of an empty database too large

I checked for nvarchar(max) columns in db1 and all fields are typical varchar or int. I am aware of the unused space, however, I want to know what causes the size of db1 be more than db2.

In this case, you should usually verify what the database is recovery model
(that is to say. select recovery_model_desc from sys.databases where database_id = DB_ID()) and database files (.mdf and .ldf) not the data type of the columns in the tables.

However, this is most likely to have happened:

  1. You created the database db1 with recovery model adjusted to full
  2. I did some DDL and DML activities on it
  3. Cleaning done all the data
  4. Asked, when looking db1 size, the tables have no data, but the size of the database shows 5 GB.

This causes due to LOG file, which maintains all the DDL / DML activities that occurred in the database, those activities were not truncated since LOG backup NOT made.

Here is some reference: Understand the recovery model

If that is not the case, you can obtain information about the storage allocation within the database through the following queries:


select  DB_NAME () AS DBName,
        SUM (CASE WHEN index_id = 0 -- HEAP (actual data)
                then (used_page_count * 8.00) / 1024 end
            ) as HeapData_Mb
        ,SUM (CASE WHEN index_id = 1 -- Clustered Indexes (actual data)
                then (used_page_count * 8.00) / 1024 end
            ) as Clustered_Index_Mb
        ,SUM (CASE WHEN index_id > 1 -- Clustered Indexes (actual data)
                then (used_page_count * 8.00) / 1024 end
            ) as NonClusterd_Index_Mb
        ,max (row_count) as (RowCount)
        ,sum( (t.reserved_page_count * 8.00) / 1024) as Total_Reserved_Mb
        ,sum( ((reserved_page_count - used_page_count) * 8.00) /1024 ) as NonUsed_Mb
from sys.dm_db_partition_stats as T
go



select  OBJECT_NAME (object_id) as ObjectName,
        --Index_id, partition_number,
        SUM (CASE WHEN index_id = 0 -- HEAP (actual data)
                then (used_page_count * 8.00) / 1024 end
            ) as RowData_Mb
        ,SUM (CASE WHEN index_id = 1 -- Clustered Indexes (actual data)
                then (used_page_count * 8.00) / 1024 end
            ) as CluIndex_Mb
        ,SUM (CASE WHEN index_id > 1 -- Clustered Indexes (actual data)
                then (used_page_count * 8.00) / 1024 end
            ) as NCluIndex_Mb
        ,max (row_count) as (RowCount)
        --,ROW_NUMBER () OVER (PARTITION BY OBJECT_ID, PARTITION_NUMBER order by OBJECT_ID)
from sys.dm_db_partition_stats
GROUP BY object_id
ORDER BY CluIndex_Mb desc, RowData_Mb desc, NCluIndex_Mb desc 
GO