我想为它创建一个新索引. cmd就像:
CREATE NONCLUSTERED INDEX [INDEX1] ON [dbo].Table1 ( [Column1] ASC,[Column2] ASC,[Column3] ASC,[Column4] ASC ) INCLUDE ( [Column5],[Column6])
大约15分钟后,发生错误
“Msg 10054,Level 20,State 0,Line 0
A transport-level error has occurred when receiving results from the
server. (provider: TCP Provider,error: 0 – An existing connection was
forcibly closed by the remote host.)”
我试了好几次,得到了同样的错误.
但我已经执行了其他耗时的查询,例如:
Insert into table1(Col1,Col2,Col3) select Col1,Col3 from table2
这耗时20分钟并成功返回.
解决方法
The reason why you can’t create the index on your table is that you
are facing a limitation on the platform that prevents to have
transactions larger than 2GB.The creation of an index is a transactional operation that relies on
the transaction log to execute the move of the table pages. More rows
in a table means more pages to put in the T-Log. Since your table
contains 100 million of records (which is quite a big number),it is
easy for you to hit this limit.In order to create the index we need to change the approach.
Basically we are going to use a temporary(staging) table to store the
data while you create the index on the source table,that you would
have prevIoUsly cleared from data.Action Plan:
- Create a staging table identical to the original table but without
any index (this makes the staging table a heap)- move the data from the original table to a staging table (the insert
is faster because the staging table is a heap)- empty the original table
- create the index on the original table (this time the transaction should be almost empty)
- move back data from staging table to original table (this would take some time,as the table contains indexes)
- delete the staging table
他们建议使用BCP在登台表和原始表之间移动数据.
查看event_log表时…
select * from sys.event_log where database_name ='<DBName>' and event_type <> 'connection_successful' order by start_time desc
..我发现此错误消息:
The session has been terminated because of excessive transaction log space usage. Try modifying fewer rows in a single transaction.