[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 681
  • Last Modified:

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

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
0
rmartes
Asked:
rmartes
  • 2
1 Solution
 
momi_sabagCommented:
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
0
 
rmartesAuthor Commented:
I am doing that. I am trying to add a new column named Age - varchar(10) - default value null
0
 
Scott PletcherSenior DBACommented:
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.
0
 
rmartesAuthor Commented:
By GUI, I tried Alter column and it worked just fine. Thanks so much.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now