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!
LVL 2
Starr DuskkASP.NET VB.NET DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
What is you tsql to alter the table?
0
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
I dont' intend to use tsql to alter the table. I use the IDE.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
don't use the IDE, as that one uses unfortunately a "inefficient" technique to alter the table.
use ALTER TABLE yourtable ADD column ....  to get this done efficiently
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.