Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a SQL Server 2005 table called BRITTNEY_SPEARS_MARRIAGES and it has the following columns:

MarrigeId tinyint, 
HusbandName varchar(500),
MarrigeLength int

Now I have another table BRITTNEY_SPEARS_MARRIAGE_STORIES

StoryId int, 
MarriageId tinyint, 
StoryText nvarchar(max)

The problem is we want to update MarrigeId column to an int from a tinyint. We just feel that Brittney is going to have lots of marriages before everything is said and done.

Now the BRITTNEY_SPEARS_MARRIAGE_STORIES table has 18 million rows in it (hey the girl has some issues) so when we go to do the update the transaction log fills up and our SQL Server box dies.

How can we get around this?

Is there anyway to say "Hey SQL Server I'm going to update this column and make it bigger. Trust me on this SQL Server. Please don't fill up the transaction log while you attempt to validate everything?"

share|improve this question

migrated from stackoverflow.com Jun 27 '12 at 20:30

This question came from our site for professional and enthusiast programmers.

8  
+1 for making it more interesting –  Aaron Bertrand Jun 27 '12 at 19:29

2 Answers 2

up vote 5 down vote accepted

There's no way to tell SQL Server not to use the transaction log.

What you can do is set the recovery model of the database to SIMPLE, which will overwrite old log entries as space is needed. You should not do this on your production server, however, because you won't be able to do certain types of restores, such as point-in-time restores.

Alternatively, you can set your transaction log file to be larger -- as an unscientific rule of thumb I'd make sure that either A) your transaction log has at least about 1.5x more free space than the size of your table or B) that your transaction log can auto-grow to a drive which has at least about this amount of disk space free.

You can free transaction log space by backing up the log. If you don't care about the log contents, throw the file away. A shortcut for this is BACKUP LOG <Your Database Name> TO DISK = 'NUL:'. Again, don't do this on a production server unless you are absolutely sure you understand the implications.

Another thing to be careful of (though it's not entirely germane to your question) is to make sure the table you're expanding has a clustered index defined on it. If it does not, the table could incur a very large amount of heap fragmentation, and potentially become needlessly large on a change like this.

share|improve this answer
  • Drop any foreign keys
  • Create new tables with int instead of tinyint
  • Move the rows over per batch of 1000 (insert them in the new table, delete them from the old one)
  • Drop the old tables
  • Rename the new tables to the old names using sp_rename
  • Recreate the foreign keys

p.S. If your transaction log is large... check your recovery model. If your recovery model is not simple, how long was it since you last backed up the log?

share|improve this answer
    
You mean since you backed up the log, backing up the database won't make the log smaller. –  HLGEM Jun 27 '12 at 19:57
    
@HLGEM: You're right, I just read an article from Paul Randal on that topic. Kind of unexpected though, if you'd only do full backups your log would keep growing. –  Andomar Jun 27 '12 at 20:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.