SQLTeam.com | Weblogs | Forums

Table Size issue


#1

Hi,

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


#2

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?