SQL Server Performance

What happens behind the log file screen??

Discussion in 'SQL Server 2005 General DBA Questions' started by jinugeorge05, Sep 12, 2007.

  1. jinugeorge05 New Member

    I have a table,X of size 13GB(Reserved space=Data+Index+Unused Space)(44,245,625 rows)
    My Transcation Log file is restricted to a growth of 15GB
    I try to do a delete from this table.There are 23,929,146 rows to be deleted
    Now what happens is that the log file grows and finally the delete operation fails as the log file is full.
    When I break up the result set from 23,929,146 to batches of 11,864,828 and 12,064,318 rows,the deletion goes through.
    I know that the log file will grow huge when a delete is issued,and,until the transaction is completed and check point is issued,the log file will be full.
    My question is if the table is 13GB and my log file has a size of 15GB why is it getting full?What excatly is happening in the log file during a deletion?Does it take a snapshot of the table two times(13+13GB)?---One snapshot for use if a rollback happens and one for committing to the datafiles once the deletion is completed?
  2. satya Moderator

    It depends on the transaction size you are running, say if you are deleting 23,929,146 in one shot then the transaction log file needs to accomodate 23,929,146 lines of entries in the file. So looking at the size of log file it may not accomodate that as other aspects of database activities must be stored, where it loses the plot and get you log file error 9002 full in this case.
    In order to achieve the task you could run the delete task with 50,000 rows per transactions and then perform the log backup, then continue the task to delete. Refer to the books online for ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/8054303c-d5f2-4033-adfc-ce15ce0978ff.htm transaction log architecture and topics such as:
    /msdn.microsoft.com/mshelp" />Transaction Log Logical Architecture Transaction Log Physical Architecture
    Checkpoints and the Active Portion of the Log
    Write-Ahead Transaction Log -
    To understand how the write-ahead log works, it is important for you to know how modified data is written to disk. SQL Server maintains a buffer cache into which it reads data pages when data must be retrieved. Data modifications are not made directly to disk, but are made to the copy of the page in the buffer cache. The modification is not written to disk until a checkpoint occurs in the database, or the modification must be written to disk so the buffer can be used to hold a new page. Writing a modified data page from the buffer cache to disk is called flushing the page. A page modified in the cache, but not yet written to disk, is called a dirty page.
    At the time a modification is made to a page in the buffer, a log record is built in the log cache that records the modification. This log record must be written to disk before the associated dirty page is flushed from the buffer cache to disk. If the dirty page is flushed before the log record is written, the dirty page creates a modification on the disk that cannot be rolled back if the server fails before the log record is written to disk. SQL Server has logic that prevents a dirty page from being flushed before the associated log record is written. Log records are written to disk when the transactions are committed.
  3. jinugeorge05 New Member

    Hi Sathya..That was a fast response [:D]Thank you!!
    I get the point that you are trying to convey,but my question is.. I am not even deleting the entire table(13GB) I am deleting just over half of the rows in it..So mathematically I see no issues in saying that I am deleting 7GB of data.In that case my log file has ample space to hold the log records of the modifications that are taking place in the pages in the buffer cache.I have no other operation going on at that time..No jobs or backups scheduled at that time.The moment that the deletion is commited,the log file size can be trucated to 0MB.
    I have noticed this on the 2000 and 2005 test servers that I am working on.In 2005,I can see that the 'log_reuse_wait_desc' column in sys.databases table says that there is an ACTIVE_TRANSACTION in there.
    Would you by any chance know what exactly is written into the log file when the deletion happens..why is a internal logic of delete so different from an updation?An update wouldnt cause the log file to grow this huge....But a delete does..Which makes me wonder what is written into the log file?
  4. jinugeorge05 New Member

    I got an explanation for this:Rollback statements are also logged. Each transaction reserves space on the transaction log to ensure enough log space exists to support a rollback if an error is encountered. This reserve space is freed when the transaction completes. The amount of space reserved depends on the operations performed in the transaction, but is generally equal to the amount of space used to log each operation.
  5. satya Moderator

    True and because of this operation you would see same amount of time while performing ROLLBACK operations, hence at this time if log file bombs out then the corresonding SPID will be in hung state.

Share This Page