Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


SQL Server 2008 timeout when adding new column

Posted on 2012-08-31
Medium Priority
Last Modified: 2012-08-31
I am trying to add a column to any table in my DB but it is takes longer than expected. The total DB size is 16mb. I have tried through Management Studio and it times-out with error below. I know you can increase the timeout threshold within Management Studio but have not done so yet because I tried the route of TSQL "Alter Table tablename ADD columnname int". This takes beyond 10 minutes which does not seem right to me. I understand the general process of adding a new column behind the scenes and the small size of my tables which leads me to think there is a larger problem and this is a symptom.

The error I receive from MSSMS is:

'Prod' table
- Unable to modify table.  
Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Any suggestions?
Question by:jrobinsontx
LVL 75

Accepted Solution

Aneesh Retnakaran earned 1600 total points
ID: 38354832
seems like some other process is locked your table. Run  'sp_Who2' and check who is accessing your database,  if it is not a production database, you can kill the process using the Kill statement

KILL <process id > 

an alternative option, in case it is not a production db is to restart your sql server service and run the ALTER TABLE Statement

Author Closing Comment

ID: 38354904
I killed two processes and that helped my problem.

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question