我想为它创建一个新索引. 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.
