SQLTeam.com | Weblogs | Forums

Table Size issue



Here I am just need your help to understand the problems.
Scenario is like this, With one of my production server, a DB is near by 1.5 TB in size, among tables just a single table is near by 1.35 TB in size,
having 2 images column. It was some business needs, i have to move this huge table to another DB of a different server. So what i did, steps are below,

  1. Generate Create Table scripts with all index/constraints from existing server
  2. Just Execute Create table scripts with all indexes/constraints on new server, Table created with same structure.
  3. Created a SSIS package for import/export
  4. Executed SSIS package, all data transferred
  5. Also cross verified all data/images are ok, system is working fine

But when i looked on DB sizes on both server, OLD server's DB size is near by 1.5 TB ( Transferred Table size is 1.35 TB)
while at new server's DB table size is just 15 GB, Now i am not able to understand why its size is so less?
How i can cross verify at system level where is problem with OLD server table structure/Data and why its size showing so huge ( Backup is also nearby 1.4 TB).

Sir looking for your valuable suggestions/problem details with this scenario.

Thanking You
Virendra Yaduvanshi


You could do this on both DBs to check that the Row Count is the same, and how much space is being used:

EXEC sp_spaceused @objname=N'TheImageTableName', @updateusage = N'TRUE';  

Might be that you have slack/unused space in the table (in which case, if you rebuild the table to remove that, maybe there is no need to move the table to another server?