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 am getting this error.

Msg 9002, Level 17, State 4, Line 2
The transaction log for database 'SalesDWH' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

When I look at this column log_reuse_wait_desc it just says NOTHING

I use this code and got the error:

use SalesDWH
go

ALTER TABLE npidata
  ALTER COLUMN npi varchar(20)

I am just changing NPI column from varchar(80) to varchar(20)

The following code gives me the same error message:

insert into npidata1 select * from npidata

The recovery mode is SIMPLE

autogrowth is set to NONE

autoshrink is set to true

What else can I do?

To my understanding the logs should just be truncating every time they are getting too big.

What am I doing wrong?

share|improve this question

migrated from stackoverflow.com Jan 3 '12 at 13:07

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

add comment

3 Answers 3

up vote 1 down vote accepted

Try to set your log file to autogrow or temporarily add some extra log files which can handle such an operation

BUT

it may be simplier and faster to do something like this:

use SalesDWH
go
ALTER TABLE npidata ADD npiNew varchar(20)
GO
UPDATE npidata SET
  npiNew = SUBSTRING(npi, 1, 20)
GO
ALTER TABLE npidata DROP COLUMN npi
GO
EXEC sp_rename 'npidata.npiNew', 'npidata.npi', 'COLUMN'
GO
DBCC CLEANTABLE(0, 'npidata', 100)

In this case log file usage can be controlled by UPDATE statement, you may perform this by small chunks like:

use SalesDWH
go
ALTER TABLE npidata ADD npiNew varchar(20), ready bit
GO
WHILE 1 = 1
BEGIN
  UPDATE TOP (100) npidata SET
    npiNew = SUBSTRING(npi, 1, 20),
    ready=1
  WHERE ready is null

  IF @@ROWCOUNT = 0 BREAK
END
GO
ALTER TABLE npidata DROP COLUMN npi
GO
ALTER TABLE npidata DROP COLUMN ready
GO
EXEC sp_rename 'npidata.npiNew', 'npidata.npi', 'COLUMN'
GO
DBCC CLEANTABLE(0, 'npidata', 100)

For better performance, in case when the table is big - you may apply temporary index on column ready

OH, YES!!!

Like @RemusRusanu said - TURN OFF THE AUTOSHRINK

share|improve this answer
    
sposibo!!1111111111111111111 –  Yuck Jan 4 '12 at 19:09
    
Speak Russian? 8-) –  Oleg Dok Jan 5 '12 at 5:15
add comment

the very first thing you have to do is to change AUTOSHRINK to false. There is absolutely no reason to ever have it true. See AUTOSHRINK: Turn it OFF!.

Both operations you are attempting require a size-of-data update in a single transaction. A single transaction requires that much log, irrelevant of the recovery model. You must increase the log size to accommodate your transaction.

share|improve this answer
1  
+1 for "autoshrink is evil" –  Oleg Dok Jan 3 '12 at 4:52
add comment

Even with SIMPLE recovery mode, the log still needs enough room to write the changes related to the transactions that happen between checkpoints.

Running with a fixed-size log is always risky, for this very reason.

The fix is to increase the size of the log, and/or enable auto-growth.

You can try forcing a checkpoint before you run the other commands, but it's a very hit-or-miss thing. The command is:

CHECKPOINT

Also, turn off autoshrink. Shrinking your log except under very unusual circumstances is a bad, bad idea -- much less doing it on a regular basis.

share|improve this answer
    
thanks so much. when should i run checkpoint? –  I__ Jan 3 '12 at 4:29
    
right before the commands that are likely to fill up the log (update, insert or delete many rows or make schema changes). How do you know which ones those are? Your guess is as good as mine. –  RickNZ Jan 3 '12 at 4:54
add comment

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.