SQL Server 2008 timeout when adding new column

Posted on 2012-08-31
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

    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

    I killed two processes and that helped my problem.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    The steps for moving the system databases to a new location are documented in the following technical article: However sometimes after the moving process is finished, though SQL i…
    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now