Home / server / Questions / 247535
Accepted
marc_s
Asked: 2011-03-16 02:05:07 +0800 CST

SQL Server - Recovery models and amount of log file disk space needed

  • 772

I'm tasked to import a database from PROGRESS to SQL Server. I'm about to establish a "Linked Server" using PROGRESS' ODBC driver.

I need to go grab all tables and all their contents - some quite large. Overall, the current PROGRESS database stands at about 135 GB of disk space needed.

Any idea / guesstimate how much the SQL Server database will be??

Also: what's the optimal recovery model for this bulk operation - bulk_logged or simple?? Any guess how much log file space I will be needing? I don't have any row counts for the PROGRESS database tables just yet, unfortunately - but the data is mostly text and numbers - no blobs or anything of that nature

sql-server

1 Answers

  1. Best Answer

    A few thoughts here to help -

    1.) If you have the PROGRESS database backed up and your solution for issues during import into SQL Server would just be to restart, I would stay in Simple mode. This will allow the transaction log to essentially be reused when the existing records in the file are not needed for crash recovery/restart recovery. You won't be able to recover to a point in time but you will just restart your import process if you have an issue. Should help save transaction log space.

    2.) The transaction log will continue to grow during a transaction. So if you are inserting an entire large table at once with one statement, the log can grow by that much (though using a minimally logged operations could help here also, especially in simple recovery model - http://msdn.microsoft.com/en-us/library/ms191244.aspx) The point here is you may want to batch your inserts for larger tables and potentially even leave a bit of a delay factor in between batches to see that log growth minimized somewhat.

    3.) I have not imported from PROGRESS before but you might consider using an SSIS package or even the import wizard to do this. You may find it faster than a Linked Server query, plus there are ways of handling batch sizes and dealing with large data loads.

    • 2