I have 1.3 GB ASII file for each month to be tranferred to a table. I want to get 18 months( 18 files) so 18*1.3 GB will be my total file size. Now I am transferring one file at a time to the table using INSERT. I have an index on one column only ( NON CLUSTERED NON UNIQUE). My question is what should be my data file size and transaction log file size for this operation. I will only be doing SELECT and INSERT in this table. Any other SQL server parameters that I need to take care of then please let me know.
Without knowing the table datatypes used in the table columns, its difficult to know. It can be calculated but its time consuming and probably more hassle than you want.
Try this ( its easier and faster ) :
(1) Create the table you are importing data to.
(2) Switch the database into Bulk Insert or Simple recovery mode during the data load ( this will stop the transaction log becoming huge and is good practice anyway during a large data load into a database ).
(3) Once the data is loaded, re-create the index. This will ensure the database is "clean" and the tran logs is as small as possible. During normal database operation ( inserts & updates ), the tran log will grow of course, if the recovery mode is FULL or BULK LOGGED.
(4) Change the database back into FULL recovery mode ( assuming you want to be able to recover the database to apoint in time ) and start using it.