Posted: 4/26/2012
Hi,
I have about 6GB database and almost 5.9GB is in one data table and growing everyday by almost 50MB. I need to migrate the entire database to a new server. I have tried migration with a truncated datatable and then use SSIS task to import datatable from old to new server....with the 8GB RAM in new server it is only able to migrate 16 Million records...looks like there are about 22 Million total records....and it takes a long time (several hours).
How can I migrate the entire database or import the datatable to the new server? and later how can I manage this evergrowing datatable? Need help!
Thanks,
Paul G.
There's a lot of things in play here that may be affecting you.
The database you're migrating to, is it pre-sized large enough to handle the data or is it auto-growing? If auto growing, what do you have auto grow settings at? Ideally, you do NOT want database/log files to grow. Auto growth is there as a fall back. For performance reasons you want to size your databases out large enough to handle existing data along with growth.
Following up on data file growth, do you have instant file initialization enabled? If you do not then any data file growth (note: log files do not take advantage of instant file initialization) may take a long time especially if the growth is large chunk (i.e. multi-gig growth). Not familiar with instant file initialization? Read this: http://www.bradmcgehee.com/2010/07/instant-file-initialization-speeds-sql-server/
Not sure how to check if your database is autogrowing? Right click on the database in SSMS, go to Reports, Standard Reports and look at Disk Usage report. It will show you a section of any autogrowth events. This information is pulled from your default trace.
How are you loading data in SSIS? Just an execute SQL task that does SELECT * from source? If so, that's probably not ideal. If you're just doing a straight one-time migration, it might actually be faster to restore a copy of the database to the new server.
Please note, if you migrate to new server you'll want to do the following procedures, ESPECIALLY if you're moving up in versions:
Run 'DBCC CHECKDB with data_purity;' against databases
Run 'DBCC updateusage;' -- this is more important if you're going from 2000 to higher version
Update all stats -- You can quickly do this by running 'USE [dbname]; exec sp_updatestats;'
Rebuild indexes -- this ensures all indexes are up to date. Please be aware there is possiblity your indexes may grow/shrink substantially depending on how often you maintain your indexes. If you're not currently doing any maintenance on them (or databases) I HIGHLY recommend you take a look at Ola Hallengren's maintenance scripts http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
These are just some things for you to consider. Please post any additional details if you need guidance.