SQL Server 2005 - Unable to modify table structure - Transaction log full???

Posted on 2012-09-04
Last Modified: 2012-09-12
Hi Experts,

I have a database hosted on Network Solutions. I am trying to add new columns to a really large table, but I am getting an error stating "unable to modify table - transaction log full...."

I ran "BACKUP LOG mydb WITH TRUNCATE ONLY" on the database using ms sql server 2005 express, but no luck....

Is there something I can do or try on my end using ms sql server express to fix this error, or does Network Solutions have to fix this on the database settings end?

Thanks in advance
Question by:rmartes
    LVL 37

    Expert Comment

    just make sure that you add a column with a default value (for example null)
    otherwise, SQL Server might have to update that value to all existing records and that might cause you the log work

    Author Comment

    I am doing that. I am trying to add a new column named Age - varchar(10) - default value null
    LVL 68

    Accepted Solution

    Are you adding the column using the gui "Design" view in SSMS, or by running an ALTER TABLE command?

    The Design view adds huge overhead to making table changes.

    Author Closing Comment

    By GUI, I tried Alter column and it worked just fine. Thanks so much.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Suggested Solutions

    So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    759 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

    10 Experts available now in Live!

    Get 1:1 Help Now