Database – At which threshold does Autogrow Happen in SQL

No way to estimate how long it will take - so many different things would have an impact. Similar to what Paul writes about the length of a CHECKDB, very similar things would come into play with indexing and more. The best answer would be how long has it taken in the past? If you can't determine that, next best option would probably be to try it on a similar non-production environment, but even that won't necessarily match (i.e. concurrency concerns, etc. would be different).

As a side note, you may want to look into ONLINE INDEX OPERATIONS in Sql 2005 and above...I can't add a second hyperlink, but google "online index operations sql server" and click the top link.

Sounds like a timeout setting. So your SSMS thinks it takes too long and cancels the connection for you. The SQL server roles back. But there is help. You are not the first person to encounter this.

See here.

For everybody who doesn't want to click the link. Here is the price winning answer:

After hitting the same error, I stumbled upon the corrent setting.

In the Management Studio, from the Tools menu, select Options, then click "Designers". There is an option called "Override connection string time-out value for table designer updates:" In the "Transaction time-out after:" box, you will see the magic 30 seconds