Starr Duskk
asked on
SQL Server adding field to table generates error timeout expired
I have a table with 12 million records in it.
I want to add a field. I made a backup of the database to try it first.
When I used the interface to modify the table and then added the field (an int), it said it would affect about 7 other tables and do I want to proceed. I said yes.
The response was that it updated all the other tables but when it got to the table I am altering, it errored with this:
Unable to modify table.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Am I never going to be able to add a field to that table? How do I do it?
Here is the script to create the original table from scratch if that's useful:
thanks!
I want to add a field. I made a backup of the database to try it first.
When I used the interface to modify the table and then added the field (an int), it said it would affect about 7 other tables and do I want to proceed. I said yes.
The response was that it updated all the other tables but when it got to the table I am altering, it errored with this:
Unable to modify table.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Am I never going to be able to add a field to that table? How do I do it?
Here is the script to create the original table from scratch if that's useful:
CREATE TABLE [dbo].[Log](
[LogId] [int] IDENTITY(1,1) NOT NULL,
[IPAddress] [nvarchar](50) NULL,
[ClientId] [int] NULL,
[UnitId] [int] NULL,
[UserProfileId] [int] NULL,
[SignOffUserProfileId] [int] NULL,
[TrainingCourseId] [int] NULL,
[TrainingWorkshopId] [int] NULL,
[TrainingModuleId] [int] NULL,
[TrainingModuleStepId] [int] NULL,
[EmployeeTrainingModuleAttemptId] [int] NULL,
[LogEvent] [nvarchar](50) NULL,
[Note] [nvarchar](4000) NULL,
[CreatedUserId] [int] NULL,
[DateCreated] [datetime] NULL,
[UpdatedUserId] [int] NULL,
[DateUpdated] [datetime] NULL,
CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED
(
[LogId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
thanks!
What is you tsql to alter the table?
ASKER
I dont' intend to use tsql to alter the table. I use the IDE.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect! that did it in a few seconds with no errors.
I don't like that the column is not in the right order like all my other tables I have my updated/created fields last, but oh well. Right?
thanks!
I don't like that the column is not in the right order like all my other tables I have my updated/created fields last, but oh well. Right?
thanks!