Most common Transaction log myths

1.       A Truncate stamen can’t be rolled back

TRUNCATE can’t be Rolled Back Using Log Files after Transaction Session Is Closed.

While database is in full recovery mode, it can rollback any changes done by DELETE using Log files. TRUNCATE cannot be rolled back using log files in full recovery mode if data files have been overwritten.

DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION if the current session is not closed. If TRUNCATE is written in Query Editor surrounded by TRANSACTION and if session is closed, it cannot be rolled back but DELETE can be rolled back.

In case of DELETE, SQL Server removes all the rows from table and records them in Log file in case it is needed to rollback in future. Due to that reason it is slow.

In case of TRUNCATE, The exact deleted values are not logged in the online SQL Server transaction log, only the IDs of the pages that held the truncated records are logged. These pages are marked for overwriting in the database data file and the truncated data will be gone for good when the new transactions are written to these pages However, while using T-SQL code TRUNCATE can be rolled back for that particular session.

Point in time restore using log file cannot roll back TRUNCATE if the data file which is deallocate is over-written. That is the difference. That is “TRUNCATE may or may not be recovered always from log files.”

2.       The truncation process reduce the size of a physical log file

During the truncation process, only the active portion of the online SQL Server transaction log file is scanned. Some parts of the scanned portion are marked as inactive and they will be used as free space to write down new transactions. There isn’t a change in the online transaction log size because the inactive parts remain intact, nothing is deleted or removed

Every SQL Server transaction log is made of Virtual Log Files (VLFs). During the truncation process, only the Logical log is scanned. A Logical log is made of active VLFs. A Log Sequence Number (LSN) is used to uniquely identify every transaction in the online transaction log. The MinLSN is the starting point of the oldest active transaction in the online transaction log

The online SQL Server transaction log file is circular by internal organization. When logging reaches the end of the transaction log, it starts again from the beginning by overwriting the parts marked as inactive

3.       Having multiple online SQL Server transaction log files will increase performance

This myth is based on the belief that having multiple online transaction log files will result in parallel writing of transactions into the files and therefore result in performance gain. SQL Server can’t operate with more than one online transaction log file at the time, so any kind of parallel I/O is not possible

Having multiple transaction log files is needed only in the situations where the initial SQL Server transaction log can’t record more transactions due to a lack of free space on the disk

4.       The SQL Server transaction log won’t grow if the database is in the Simple recovery model.

             However, it happens just in some specific situations – when there is a long running transaction or                    transaction that creates many changes

            In the Simple recovery model, the online transaction log is cleared automatically. SQL Server                       automatically reclaims log space to keep space requirements small – but that doesn’t mean it won’t               grow. The online transaction log must provide enough information for a database rollback, therefore               it must provide enough space for all necessary information. As all transactions must be written into                 the online transaction log, in case of a large number of changes in a transaction, there might not be                 enough space in the log, so it must be expanded.

5.       A SQL Server transaction log backup will be the same size as the online transaction log itself.

The online transaction log must have enough information to rollback active transactions, so some space is reserved for eventual rollbacks. If a rollback occurs, SQL Server doesn’t want to expand the online transaction log because if the expanding fails, the SQL Server database can become inconsistent or go into the Suspect mode. That’s why the online transaction log has some reserved space and is usually bigger than the SQL Server transaction log backup

Moreover, a transaction log backup contains only the transactions made after the last transaction log backup. If the online transaction log contains the transactions that have already been backed up, they will not be present in the new SQL Server transaction log backup, therefore the transaction log backup will be smaller for that amount of space

6.       A full or differential database backup clears the online transaction log.

The inactive parts of the online SQL Server transaction log are marked for clearing only when a transaction log backup is created

Full and differential database backups don’t contain much SQL Server transaction log information, only the transactions necessary to recover the database into a consistent state. These transactions are not a backup of the online transaction log, therefore these transactions are not marked for overwriting in the online transaction log

7.       The TRUNCATE TABLE and DROP TABLE commands are not logged into the online transaction log.

The exact deleted values are not logged in the online SQL Server transaction log, only the IDs of the pages that held the truncated records are logged. These pages are marked for overwriting in the database data file and the truncated data will be gone for good when the new transactions are written to these pages

This myth is also based on the fact that these commands take little time to execute, they are almost instantaneous

8.       My SQL Server is too busy, I don’t want to make SQL Server transaction log backups.

One of the biggest performance intensive operations in SQL Server is an auto-grow event of the online transaction log file. By not making transaction log backups often enough, the online transaction log will become full and will have to grow. The default growth size is 10%. The busier the database is, the quicker the online transaction log will grow if transaction log backups are not created

Creating a SQL Server transaction log backup doesn’t block the online transaction log, but an auto-growth event does. It can block all activity in the online transaction log

9.       A SQL Server transaction log backup isn’t needed for a point in time restore. A full database backup is enough.

This myth comes from using the RESTORE command with STOPAT clause to restore from a full database backup. The STOPAT clause specifies a point in time for the RESTORE LOG command, and it works well when it’s used with a transaction log backup. The fact that it can be used with a full database backup makes you believe that transaction log backups are not needed to recover to a specific point in time

RESTORE DATABASE AdventureWorks FROM DISK = 'D:\AdventureWorks.bak' WITH NORECOVERY

GO

RESTORE LOG AdventureWorks FROM DISK = 'D:\AdventureWorks.bak' WITH RECOVERY, STOPAT = 'Dec 31, 2013 10:59:00 PM'

GO

Although the SQL Server database cannot be restored to a point in time, SQL Server doesn’t clearly identify the problem, and it allows you to use the STOPAT clause without a transaction log backup specified

10.   SQL Server transaction log backups are not needed for successful disaster recovery if the full database backup is taken daily.

It also depends on how much data you can lose. If you can afford to lose up to 24 hours of data, then you don’t need transaction log backups and you should use the Simple recovery model

If the information you can lose is measured in minutes and hours, regular transaction log backups are necessary, as the maximum you will lose is the time between to transaction log backups

11.   The SQL Server transaction log shrinking will make free space in the online transaction log so I don’t need to create transaction log backups.

The shrink operation is not a good maintenance practice because it doesn’t solve the transaction log size issue permanently. After the initial shrinking, the transaction log will grow again. As the auto-growth event is one of the most intensive SQL Server operations, it should be avoided. The recommended method to keep the size of the online transaction log is to create transaction log backups regularly. Or, switching to the Simple recovery model, if you can tolerate data loss.

12.   Log records are written to disk immediately.

FALSE. There is a 60KB log cache in the RAM that log records are written to, not the disk. That cache is written to the disk, to the transaction log file, as soon as all 60KB is filled, any session of that database issues a commit (a real commit, not the nested one that just decreases @@TRANCOUNT), and on some other events. All log records flushed together to the disk will have the same block offset (LSN consists of VLF number, block offset, and log record number). Therefore, log block cannot be larger than 60KB (cache’s size).