Sql Server Time Out

Question

Hi i was trying to create an index on a table in SQL Server 2008. And there are billions of records in the table.

So while creating the index and saving the design of the table i am getting the following error.

"Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."

Any ideas why is that happening

Things i tried ...

1, in sql server database ... I have gone to TOOLS -> OPTIONS -> Designer ->  Unchecked ( Override connection string time-out value for table designer updates.

Still no luck

Please help .

Thanks

Jenni

Answer 1

Hi,

Have you tried doing it by TSQL instead?  Sometimes the GUI plays tricks on us

---

Thanks,

Andrew Bainbridge
SQL Server DBA

Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

Answer 2

I'd suggest you to run it as CREATE INDEX statement from the query windows in management studio. You can generate that script from Table Designer/Generate Change Script menu in management studio.

As the side note, if your table has billions of records, it would not be fast. And if you have other users in the system, there would be locking involved. If you have Enterprise Edition of SQL Server you can consider to use Online index creation (add WITH (ONLINE=ON) to create index statement). This will add some load to tempdb because it uses version store.

---

Thank you!

My blog: http://aboutsqlserver.com

Answer 3

Andrew, i am trying it out in TSQL itself and showing the same error

Thanks

Answer 4

Are there any users connected?

Is it possible to stick the database into single user mode and try to create the index?

Is the index very wide and what are the database key(s) data types?

I've had no issues with indexes on a table of 2.5bn rows previously (integer keys).

---

Clive
www.sqlsvrdba.com