Solved

size of db reduced after transfer

Posted on 1998-11-19
5
193 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 100 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

821 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