CODEWITHSUNDEEP
sql-serversql-server-2008
Goran
Goran

Reputation: 6518

Inserting not null column with default value

I have a database that is used for development and testing purpose on a web server. What I would like to do is add one column to the table that contains approximately 50,000 records (please note that I am not a database administrator). I am connecting through SSMS with credentials provided by our provider and executing next query

ALTER TABLE MyTable
ADD MyCol BIT
CONSTRAINT MyConstraint DEFAULT 1 NOT NULL

The error I get is:

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

If I execute

SELECT log_reuse_wait_desc, * 
FROM sys.databases WHERE name ='my_db'
GO

in a batch together with Add MyCol, log_reuse_wait_desc will be ACTIVE_TRANSACTION. If I execute it as a separate command, it will be CHECKPOINT

Recovery mode for database is set to simple. I have searched for a solution on internet, and people are either suggesting to set recovery mode to simple (which already is), or to backup a log file and then shrink it, which some experts consider very bad practice.

So what I need to do to add simple NOT NULL column to a table with data?

Edit: Here is the solution, if someone doesn't want to read through all answers:

to get the size of the Transaction log, this is how I did it:

sp_helpdb 'YouDatabaseName'

In result window you will get the size of the log file (mine was 8MB). To increase it to larger value, use this query:

ALTER DATABASE YourDatabaseName
MODIFY FILE(NAME = YourDatabaseLogName, SIZE=128MB);
GO

And the error will be gone.

Upvotes: 0

Views: 325

Answers (4)

Deepak.Aggrawal
Deepak.Aggrawal

Reputation: 1277

You can specify the size of log bigger, that this message won´t come. You can increase the log to autogrowth (or both) that SQL Server can get more space doing the transactions.

Please use this link for reference: The transaction log for database is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

You can Grow the size using the following command:

ALTER DATABASE YourDatabaseName

MODIFY FILE

(NAME = YourDatbase_log,

SIZE = 1500MB);

You can see the size of Log file:

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'DBName'

Upvotes: 1

Andomar
Andomar

Reputation: 238048

A description for CHECKPOINT can be found on MSDN:

No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond a virtual log file (all recovery models). This is a routine reason for delaying log truncation.

Unless you manually changed the checkpoint settings, this means your log file is too small. If possible, allow the log file to grow, or remove the maximum size altogether.

If you can't increase the log file size, you can try to work around it. One workaround is to split your change in smaller transactions. For example:

  • Add the column as nullable (Adding a nullable column is a much smaller transaction)
  • Update the column in small chunks (say, 1000 rows at a time.)
  • Modify the column to be not null

Upvotes: 2

Devart
Devart

Reputation: 121902

Try to remove the restriction on the size of the log-file or increase log file:

log

Upvotes: 1

gbn
gbn

Reputation: 432180

This means your log file is both too small and can not grow.

Adding this column (in this case, not always for bit columns) requires the on-disk structure to be shuffled around which generates log entries so it can be rolled back. Backing up the log file and shrinking it is almost always never a good solution: it sounds like it has already been done here, hence it is too small. And I mean really small because 50,000 rows is small.

You can increase the space and/or growth using SQL or the GUI. This answer describes more: SQL Server: How do I increase the size of the transaction log?

Upvotes: 1

Related Questions