Asked By
Franklin
280 points
N/A
Posted on - 05/16/2011
I am attempting to create an Index on one of my tables. I keep getting "operation timed out". Why is this?
Answered By
Stella
0 points
N/A
#98033
Getting a “operation time out” when indexing tables in MS SQL Server
Are you running the index creation on a remote database or is it on the local laptop? If you are accessing a remote database then the error message may relate to a network congestion.
Can you try logging on to the server and executing the query ?
Answered By
Franklin
280 points
N/A
#98034
Getting a “operation time out” when indexing tables in MS SQL Server
I am actually logged on to the database server via Microsoft Remote Desktop.
I am using the Microsoft SQL Server Management Studio to run the query. I am surprised that index creation is timing out when I am executing it directly on the server!
Answered By
Stella
0 points
N/A
#98035
Getting a “operation time out” when indexing tables in MS SQL Server
Hmmm, ok. Can you let me know how many records are there in the table you are referring to? If possible, can you paste the Create table statement as well?
Answered By
Franklin
280 points
N/A
#98036
Getting a “operation time out” when indexing tables in MS SQL Server
The table contains 23,774,277 records. That is about 23 million records.
CREATE TABLE [mobileRegistry](
[registryId] [int] IDENTITY(1,1) NOT NULL,
[registryDate] [datetime] NOT NULL,
[wardName] [nvarchar](20) NOT NULL,
[createdDate] [datetime] NULL,
[status] [int] NULL CONSTRAINT [DF_status] DEFAULT ((1)),
CONSTRAINT [PK_1] PRIMARY KEY CLUSTERED
(
[registryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Answered By
Stella
0 points
N/A
#98038
Getting a “operation time out” when indexing tables in MS SQL Server
23 Million records is actually a whopping big number of records!
What I can suggest is that:
-
Rename the current table
-
Create the table
-
Create the required Index
-
Use Microsoft Data Transfer Service to transfer data from the old table to the new table.
-
Use a custom query and ORDER the data on the data transfer.
This way the index will be adjusted as and when the data is added. Feeding an ordered set of data will speed up the index allocation as it will minimize the index from being shifted.
Answered By
Franklin
280 points
N/A
#98039
Getting a “operation time out” when indexing tables in MS SQL Server
Thank you Stella,
I will try your solution. It will take a while though! I will post the findings here. Wish me luck!
Answered By
Franklin
280 points
N/A
#98040
Getting a “operation time out” when indexing tables in MS SQL Server
Stella,
Your suggestion worked beautifully. I created the table and transferred the data! No timeouts!
Answered By
Stella
0 points
N/A
#98041
Getting a “operation time out” when indexing tables in MS SQL Server
Glad to be of help! Have a nice day!
