Friday, February 24, 2012 9:20 PM
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 .
Friday, February 24, 2012 10:25 PM
Have you tried doing it by TSQL instead? Sometimes the GUI plays tricks on us
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
Friday, February 24, 2012 10:27 PM
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.
My blog: http://aboutsqlserver.com
- Marked As Answer by Jennidoll Monday, February 27, 2012 4:47 PM
Friday, February 24, 2012 10:36 PM
Andrew, i am trying it out in TSQL itself and showing the same error
Sunday, February 26, 2012 11:22 AM
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).