Link to home
Start Free TrialLog in
Avatar of Starr Duskk
Starr DuskkFlag for United States of America

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:

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]

Open in new window


thanks!
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

What is you tsql to alter the table?
Avatar of Starr Duskk

ASKER

I dont' intend to use tsql to alter the table. I use the IDE.
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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!