SQL Server Performance Forum – Threads Archive

Log file appears to grow excessively

My SQL Server is configured to only truncate the log during a transaction log backup.
The log file autogrow option is enabled (true)
I recently performed an operation whereby i deleted approximately 27 million records from a table. (‘Truncate table’ was not an option as 10 million other records needed to remain there.) I estimate the total data size affected by this delete to be no more than 10GB.
My log file, however, grew in size by approx. 40GB. I am almost completely certain that this was caused by the delete operation, as no other large process or task was scheduled for that time. I am puzzled why the log file would grow so execessivley in relation to comparitively much less data targeted by the operation. Does anyone have suggestions as to how this occurs ?

One of the Technet article refers:
For SQL Server 7.0 and 2000 class servers, the transaction log has the capability to expand as needed. The amount of growth can be governed by the user or allowed to utilize all available disk capacity. A log file is composed of a number of Virtual Log files. The number and size of these virtual log files are determined by SQL Server and cannot be configured. When a database is first created, each physical log file has a minimum of 2 Virtual Log files. Sometimes the database administrator will enable the "truncate log on checkpoint" option of a database in an effort to avoid log space exhaustion. The intent of this option is to provide an automatic method of truncating the log, mainly for development or test databases which do not rely on log dumps for backup. This option does not disable logging or transactional integrity. It merely causes the checkpoint handler to attempt a log truncation approximately every 60 seconds. Note that the log will not be truncated when issuing a manual checkpoint command in a database with "truncate log on checkpoint" on. This option is always on for the tempdb database, even though this is not indicated in the status column of the sp_help stored procedure output.
Truncation of the transaction log in SQL Server 7.0 and 2000 class servers is accomplished by truncating Virtual Log Files. If any portion of the active log is resident on a given VLF, that Virtual Log File cannot be truncated. If the active log is resident on all Virtual Log Files, the log cannot be truncated. If autogrowth is enabled and there is space on the volume where the transaction log resides and the maximum file size has not been reached, the transaction log grows by the amount specified in the log file properties. For further information on Tlog refer to this KBAhttp://support.microsoft.com/kb/317375/EN-US/ HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hi
Try to shrink it it should help
I had BIG problem with that log file!
My hard disk was full and could not work ! make it faster

I guess on top of row deletes, emptied data and index page deallocations are logged too.

]]>