我在Sql Azure中有一个表包含大约6M行.
我想为它创建一个新索引. 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分钟并成功返回.
查询在同一个Sql Azure DB中执行.我不知道这里发生了什么.有人可以帮忙吗？谢谢！
解决方法
我在包含100M行的表中遇到了同样的问题并联系了Microsoft支持.这是我得到的答复：
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.
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
