February 18, 2004 at 7:07 am
I have a table with about a half million records in it. I run an update on a column for all records to set it to 0 as follows.
Update MyTable Set GroupID = 0
By doing this, it adds about 40MB to my transaction log. I thought transaction logs only held the transaction information, meaning if I changed all the records or filtered it to update only one, it would put the same thing in the log. I am obviously wrong here.
Update MyTable Set GroupID = 0
Update MyTable Set GroupID = 0 Where PK_ID = 1
Shouldn't these two statements add about the same information to the transaction log?
Can anyone explain this behavior?
February 18, 2004 at 8:31 am
The transaction log doesn't just hold the data manipulation statement (I'm not sure actually if it does at all). Among other things, it contains a before and after image of every row that was updated by a statement, so your update that updated 1/2 million rows would definitely fill up the transaction log. - apf
February 19, 2004 at 1:56 pm
Is the problem that the log just keeps getting bigger? If you do not need transaction log backups for recovery, then you can set the recovery model to simple so that the log is truncated on checkpoint. However, if you are using a new database that never has had a backup, the log still might not get truncated. If you still have a problem after a backup, then check for open transactions.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
February 19, 2004 at 2:00 pm
I guess I was mainly interested why it was getting bigger, as in what gets put into a transaction log. I havn't found much documentation that really tells what gets stuffed in there. I understand that backups and the type of recovery model have to do with truncating it.
If anyone has any ideas or can point me to some documentation I would be interested.
Thanks for the comments though.
February 19, 2004 at 2:21 pm
From BOL.
"Transaction Log Architecture
Every Microsoft® SQL Server™ 2000 database has a transaction log that records all transactions and the database modifications made by each transaction. This record of transactions and their modifications supports three operations:
If an application issues a ROLLBACK statement, or if SQL Server detects an error such as the loss of communication with a client, the log records are used to roll back the modifications made by an incomplete transaction.
If a server running SQL Server fails, the databases may be left in a state where some modifications were never written from the buffer cache to the data files, and there may be some modifications from incomplete transactions in the data files. When a copy of SQL Server is started, it runs a recovery of each database. Every modification recorded in the log which may not have been written to the data files is rolled forward. Every incomplete transaction found in the transaction log is then rolled back to ensure the integrity of the database is preserved.
After the loss of a database, as is possible if a hard drive fails on a server that does not have RAID drives, you can restore the database to the point of failure. You first restore the last full or differential database backup, and then restore the sequence of transaction log backups to the point of failure. As you restore each log backup, SQL Server reapplies all the modifications recorded in the log to roll forward all the transactions. When the last log backup is restored, SQL Server then uses the log information to roll back all transactions that were not complete at that point."
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply