BULK INSERT Performance

一直以為BULK INSERT就不會記Log，結果今天跌了一跤...

我下的語法如下(錯誤示範，小朋友不要學)

TRUNCATE TABLE RESD

BULK INSERT RESDE
FROM 'C:\DataProc\Output\RESD.txt'
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
)

RESD.txt約1.4G，結果BULK INSERT花了30分鐘還沒做完，Log檔就長到20G，把HD空間給吃光。急忙Cancel，但依DB的運作，此時SQL會將剛才未做完的INSERT Rollback回去，20G的Log! 等了40分鐘還等不到Cancel完成。

請教了DBA，DBA認為剛才有HD空間不足的變數，SQL Server 2005可能已經不正常，建議我重新啟動SQL試試。重新啟動SQL後，順道見識了SQL 2005的新功能，SQL還是會將剛才沒做完的Rollback繼續做完，此時正在Rollback的DB會顯示(in recovery)而暫時不能用，但其他的DB則Ready了，花了約兩分鐘，in recovery的DB也回到可用狀態。(SQL 2000需要等Rollback做完，整台Server才能用)

在苦等DB Rollback的過程裡，我Google了一下，發現幾件事:

Nonlogged BULK INSERT只有在一些條件下才成立(我一直以為BULK INSERT==No Log):
- DB選項必須開啟SELECT INTO/BULK INSERT
- 不可設Index；如果有，開始BULK INSERT時，TABLE必須是空的
- 需加上TABLOCK提示
- 資料表沒有設定複寫(Replication)
http://www.mssqlcity.com/Tips/bulk_copy_optimization.htm
由於誤認BULK INSERT不會有Log Issue、加上將DB設成Simple Recovery Mode(Truncate Log At Checkpoint)，我沒意會到讓2.5億筆資料變成一個Transaction是多可怕的事。因此才會爆出可怕的Log量，其實我只要透過BATCHSIZE=1000，讓BULK INSERT過程中每1000筆Commit一次，Log量就會小很多了。不過資料匯入後，不過2G，Index也只有一組，我還是無法理解為什麼可以產生10倍大小的Log?

綜合以上，BULK INSERT SQL可以改成:

BULK INSERT RESDE
FROM 'C:\DataProc\Output\RESD.txt'
WITH
(
    BATCHSIZE = 1000,
    FIELDTERMINATOR = '\t',
    ROWTERMINATOR = '\n',
    TABLOCK
)