Getting a “operation time out” when indexing tables in MS SQL Server

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?

• Status: Open
• Question Views: 1811
• Answer Count: 8
• Vote Up 23 Vote Down

• Answer Accepted: Yes
• Question Category: MS SQL Server

Best Answer by Stella

Go To Solution

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 ?

About Stella

Questions
0

Answers
48

Best Answers
10

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS SQL Server

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!

About Franklin

Questions
2

Answers
4

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS SQL 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?

About Stella

Questions
0

Answers
48

Best Answers
10

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS SQL Server

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]

About Franklin

Questions
2

Answers
4

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS SQL Server

Best Answer

Best Answer

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.

About Stella

Questions
0

Answers
48

Best Answers
10

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS SQL Server

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!

About Franklin

Questions
2

Answers
4

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS SQL Server

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!

About Franklin

Questions
2

Answers
4

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS SQL Server

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!

About Stella

Questions
0

Answers
48

Best Answers
10

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS SQL Server