6.5 transaction logs filling up

[mikej336]

I am trying to run an update on a table in 6.5. It is a large table with 250k records.

update bla
set field2=field1

It never finishes or gives an error. Eventually I stop the query and the transaction log is full. It is small business version of 6.5 which I just learned has a 1gb limit. I can't expand the trans log because of the licnse limit.

What can I do?

Thanks

Uncle Mike

 

[mikej336]

Anybody know if it is possible to upgrade the license from business to standard in version 6.5?

Thanks Uncle Mike

 

[mrdenny]

That would require first buying a copy of 6.5. I'm not sure if you can use downgrade rights from SQL 2000 or SQL 2005 all the way back to SQL 6.5. Then you would need to find some media for 6.5 Standard. Then you would need to uninstall and reinstall.

You can batch it to process a thousand records at a time.

set ROWCOUNT 1000
update bla
set field2=field1
where field2 <> field1
dump transaction {DatabaseName} with truncate_only

Run that over and over again until you get 0 rows processed. After each 1000 rows are done it will dump the transaction log.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[mikej336]

Thanks MRDENNY

Yesterday I attempted to expand the database to 1.5 gig. That's when I discovered that it is was business edition.

Now when I run "dump transaction {DatabaseName} with truncate_only" there are no errors but does not ruduce the size of the file. Nothing happens.


Enterprize manager reports
data size 850 with 712 available
log size 117 with 0 available

Can I reduce the data size and increase the log size?

Thanks

Uncle Mike

 

[mrdenny]

As there is space available in the database you should be able to shrink the data file via the DBCC SHRINKFILE command. You can then expand the log file via the ALTER DATABASE command. There is more info in Books OnLine on both topics.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com