April 20, 2009 at 6:38 am
I restored database after backing up the original on a different server and now i have 2 copies of identical databse but the size of the databse is not matching though counts on all the tables look same. Why is the size changed after restoring the same db?
April 20, 2009 at 7:02 am
Also I would like to verify if am rebuilding the indexes on the databases does the soze of the database go low.
Here is what i did...
Backed up databse Emp ( size = 300GB) ,
resotred on a 2nd server
Rebuild indexes on 2nd server
WHen chked db size on 2nd server it is 296GB.
Does it make any sense??
April 20, 2009 at 8:57 am
Restoring the database from one place to another should definitely not shrink the database in any way.
How are you checking the size of the database (file system, sp_spaceused, etc..)?
Rebuilding the indexes will certainly free up space within your database, since the indexes that were rebuilt will use fewer of the allocated data pages. However unless you shrink the database, it will still keep those pages, they will just be made available for other things within the database. So the only number that should change by rebuilding indexes should be the space available (unallocated space). The database size itself should not lower do to index rebuilds unless you shrank the database (not advised).
April 20, 2009 at 9:44 am
yes i am checking by sp_spaceused and i see the unallocated space is still the same only the db size has gone down.
April 20, 2009 at 6:20 pm
Please run sp_spaceused @updateusage 'true' on both the databases and compar the size thereafter.
MJ
April 22, 2009 at 7:01 am
Againa when i tried with another database of 250GB its the same. IS there any reason why database size is not same when i am restoring a sam copy from production and i see 100mb difference.
I backed up production and restored on a diffrent server and i see 100mb less in the second server where i restored.
Now i didnt do any kind of activity after restoring a database but still the difference, whats the logic here.
April 22, 2009 at 7:08 am
Original Databse:
database_size=259318.88 MB
unallocated space=385.41 MB
reserved=259032280 KB
data=114904320 KB
index_size=143331392 KB
unused=796568 KB
After Backedup and restored on another server:
database_size=259218.88 MB
unallocated space=292.14 MB
reserved=259025392 KB
data=114904024 KB
index_size=143320576 KB
Also i would like to tell you that after the db was backed up there are some reads and activities on the db but i am sure there is no writes, i mean not even a single record has been inserted.
unused=800792 KB
April 22, 2009 at 9:51 am
What type of storage is being used on each instance? Are they identicial?
April 22, 2009 at 9:09 pm
Tara, Rebuilding indexes will compact the databases and reduces fragmentation, probably that might be the reason you see difference in the DB size. In your original Database what is the percent of fragmentation? both OS level and table level?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply