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

Asked By 280 points N/A Posted on -
qa-featured

I am attempting to create an Index on one of my tables. I keep getting "operation timed out". Why is this?

SHARE
Best Answer by Stella
Answered By 0 points N/A #98033

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

qa-featured

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 280 points N/A #98034

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

qa-featured

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 0 points N/A #98035

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

qa-featured

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 280 points N/A #98036

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

qa-featured

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]

Best Answer
Best Answer
Answered By 0 points N/A #98038

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

qa-featured

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 280 points N/A #98039

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

qa-featured

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 280 points N/A #98040

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

qa-featured

Stella,

Your suggestion worked beautifully. I created the table and transferred the data! No timeouts!

Answered By 0 points N/A #98041

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

qa-featured

Glad to be of help! Have a nice day!

Related Questions