?
Solved

size of db reduced after transfer

Posted on 1998-11-19
5
Medium Priority
?
221 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
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
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

601 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