Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

size of db reduced after transfer

Posted on 1998-11-19
5
Medium Priority
?
220 Views
Last Modified: 2010-03-19
I got a Intel box and a Alpha box.
I transfered a database from Intel to Alpha and the size of the database on the Intel is and was 440 mb on Alpha it's only 90 mb.
Anyone knows why?
0
Comment
Question by:alesh
5 Comments
 
LVL 2

Expert Comment

by:aliciaam
ID: 1091874
How did you transfer the database? Using transfer manager. You cannot transfer databases from Alpha to Intel or viceversa, you will have to dump the database and restore. Create a device the same size on Intel , make sure you have the same sort order and case and then restore.

0
 
LVL 2

Expert Comment

by:aliciaam
ID: 1091875
Sorry my mistake is the other way around, disregard what I said.  Reject the answer
0
 
LVL 7

Expert Comment

by:tchalkov
ID: 1091876
There ara a few reasons I can think of:
1. Your database is very fragmented and when you transfer it your database gets defragmented - you can check this by transfering your database to the same computer and check the results.
2. Both transaction log and database are on the same device - it is possible then that your transaction log is very big and after the transfer it is empty.
3. You didnot select to transfer every object from the destination database.
0
 
LVL 9

Accepted Solution

by:
cymbolic earned 300 total points
ID: 1091877
SQL server keeps all the old trash around in your db, such as deleted records, etc.  When you transfer to another server, that trash is not carried over.  If you don't believe me try this:

Take a large table (Lotsa records) then do a delete from for all records, check the size of your db.  Then, trncate the table and cehck the size of your db again. voila! some things carry out the trash, some actions save it for future use (who knows why, ms won't tell you!)
0
 

Expert Comment

by:efendi
ID: 1091878
Inserting and deleting a larger number of pages has two consequences (keeping in mind that the transaction log is empty before a transfer):
1) There is a large number of data pages, that are near-empty
2) The ideal structure of the indexes is achieved on a newly-formed index tree, but cannot be maintained on a frequently updated table (update/insert/delete) unless a rebuild of indexes is done before a transfer. Therefore, many index pages are also not filled-up ideally, as is the case of data-pages.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question