0

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.

Goran
  • 6,328
  • 6
  • 41
  • 86

4 Answers4

2

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
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • The 3d option (modifying the column to be not null) will result in table recreation, if I am not mistaken. I have already tried that and it did not work (same error on step 3). Also, I did not change manually anything regarding checkpoints. The log file is on web server that we do not own, so I do not believe I can change its size? – Goran May 24 '13 at 11:26
  • Try to contact the firm that owns the webserver. As GBN answered, 50k rows is not a lot! As another alternative, create a new table with the proper definition and move rows over a few at a time. At the end, drop the old table and rename the new one (see `sp_rename`.) – Andomar May 24 '13 at 11:47
  • WHat you suggested the last is what I did, but it took me like 6 hours for 2 tables! First I did was Tasks -> Generate script for two tables (with data), then I deleted all relationships they contain, then deleted 2000 TOP rows in a one batch. Then I changed column in question to NOT NULL. That was the quick part. Copying... omg. In batch 2000 rows, each operation taking approximately little less than 5 minutes. Now multiply that with number of batches I need to execute (140,000 / 2, 000) = 70, than gives sum of 70 * 5 = 350 minutes. For this reason I asked for some quicker method... – Goran May 24 '13 at 16:55
  • Five minutes for deleting 2000 rows is not normal. It really sounds like there's something wrong with the server. – Andomar May 24 '13 at 17:14
  • Not deleting, adding. Deleting is immediate. I am voting your answer as helpful, for those who do not have the rights to change size of transaction log file. – Goran May 24 '13 at 17:25
1

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'
Community
  • 1
  • 1
Deepak.Aggrawal
  • 1,249
  • 11
  • 24
  • Can you please explain how can I change the size of log file, since I do not see where exactly is it mentioned in the link. Please remember that we are not hosting the database file, we only have access to a patabase file through SSMS (using credentials that provider gave us). – Goran May 24 '13 at 11:31
  • ALTER DATABASE YourDatabaseName MODIFY FILE (NAME = YourDatbase_log, SIZE = 1500MB); – Deepak.Aggrawal May 24 '13 at 11:38
  • Hi Deepak, before I execute that command, can you please tell me how can I see the current log size, so I can return it back as it was? – Goran May 24 '13 at 11:41
  • Added in Comments in Answer – Deepak.Aggrawal May 24 '13 at 12:08
1

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?

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • From the link I read, it says that I can increase the log file size through ALTER DATABASE MODIFY FILES. If I do not have permissions to access database properties through SSMS, does that also applies to executing ALTER DATABASE? – Goran May 24 '13 at 11:39
  • @Goran raw SQL checks permissions differently. It may be OK – gbn May 24 '13 at 12:21
1

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

log

Devart
  • 119,203
  • 23
  • 166
  • 186
  • When I right click on the database to get to its properties, I get an error that I do not have permissions to perform that action (Error 297). I was the one that created the database, through provider's control panel. Then after I created the databse, I used SSMS to create whole table. – Goran May 24 '13 at 11:35
  • No, when I create a database through web provider control panel, I create a user for this database that I will use to connect to it. With this user I am trying to achieve this task, I really do not know what permissions does this user have. – Goran May 24 '13 at 16:56
  • Thanks for showing the GUI way, I am voting your answer as helpful for someone else. – Goran May 24 '13 at 17:27