Problem with updating column in table - transaction log full

Hello,
I had trouble performing an update of one column of a table.
The column is of Bit data type and the table has 60350 rows.
When I run the following query in the Query Analyzer:
update [Specimen CV] set HPV = 0
I get a message "The log file for database DBNAME is full. Back up the
transaction log for the database to free up space"
I backed up both the DB and the log, truncated as well as increased the size
of the log file, first to 100MB.
I even set the DB to simple recovery mode. Still, I got the same message.
Finally, when I set the size to 1 GB did the update run.
After the update ran I checked the Log Space used and it was 12.6%
Does really a simple query like that on a table with only 60K records
require 126 MB of log space?
Any comment would be appreciated.
RagnarOn Mar 7, 7:54 pm, "Ragnar Midtskogen" <ragnar...@.newsgroups.com>
wrote:
> Hello,
> I had trouble performing an update of one column of a table.
> The column is of Bit data type and the table has 60350 rows.
> When I run the following query in the Query Analyzer:
> update [Specimen CV] set HPV = 0
> I get a message "The log file for database DBNAME is full. Back up the
> transaction log for the database to free up space"
> I backed up both the DB and the log, truncated as well as increased the size
> of the log file, first to 100MB.
> I even set the DB to simple recovery mode. Still, I got the same message.
> Finally, when I set the size to 1 GB did the update run.
> After the update ran I checked the Log Space used and it was 12.6%
> Does really a simple query like that on a table with only 60K records
> require 126 MB of log space?
> Any comment would be appreciated.
> Ragnar
Are there triggers on this table? Your single update statement, while
seemingly simple, is creating an implicit transaction, and any change
that the update produces, directly or indirectly, is captured within
that transaction. The log file, which stores the "undo" information
for all of these changes, must be large enough to hold everything that
the update statement is changing, within the table being updated, AND
any other tables that are being affected by triggers on that table.|||Thank you Tracy,
There are no triggers on any of the tables.
I guess this was not an indication of anything wrong.
By the way, I seem to remember that, depending on some settings, the log
will grow if needed, but I have been digging through the SQL Server Books
Online without finding anything about this.
I have seen a few cases in our company where someone has set up an SQL
Server DB without setting up regular backups, where the log just kept
growing. In one case the log used up all the available space on the disk and
effectively shut down the computer, not enough room for the swap file.
I think there is a way to control the growth of the log file, but I have not
found out how to do this.
Any pointers would be appreciated.
Ragnar|||How much space is used for the log is dictated by how much log records you generate and how often
you empty the log. To empty the log, you have basically two options:
Have the database in simple recovery mode. Now SQL Server will empty the log automatically (every
time a checkpoint occurs).
Have it in full recovery mode and do regular transaction log backups.
I suggest you read a bit about recovery models and backup to get more insight into this. Also, you
might want to check out (related reading):
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:e8F6%23RaYHHA.3824@.TK2MSFTNGP02.phx.gbl...
> Thank you Tracy,
> There are no triggers on any of the tables.
> I guess this was not an indication of anything wrong.
> By the way, I seem to remember that, depending on some settings, the log will grow if needed, but
> I have been digging through the SQL Server Books Online without finding anything about this.
> I have seen a few cases in our company where someone has set up an SQL Server DB without setting
> up regular backups, where the log just kept growing. In one case the log used up all the available
> space on the disk and effectively shut down the computer, not enough room for the swap file.
> I think there is a way to control the growth of the log file, but I have not found out how to do
> this.
> Any pointers would be appreciated.
> Ragnar
>