1) A 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
An example of T-SQL code for restoring the AdventureWorks database to December 31st 2013 10:59 PM
RESTORE DATABASE AdventureWorks FROM DISK = 'D:\AdventureWorks.bak'
RESTORE LOG AdventureWorks FROM DISK = 'D:\AdventureWorks.bak'
STOPAT = 'Dec 31, 2013 10:59:00 PM'
Although the 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.
2) Can SQL Server database work without a transaction log?
No, that is not possible due to the SQL Server design and ACID compliance. ACID stands for atomicity, consistency, isolation, and durability. All the transactions must fulfil these characteristics
- An atomic transaction is either fully completed, or is not begun at all
- A transaction enforces consistency in the system state by ensuring that at the end of any transaction the system is in a valid state
- When a transaction runs in isolation, it appears to be the only action that the system is carrying out at one time
- A transaction is durable meaning that once it has been successfully completed, all of the changes it made to the system are permanent
3) Do I need SQL Server transaction log backups?
Yes, that is one of the most important resource when it comes to disaster recovery. They are not needed (and available) only if the Simple recovery model is used – but there is data loss exposure. Majority of Database Administrators uses 15 minutes interval or even less for a high traffic SQL Server databases. The transaction log backups are important because when taken they mark inactive VLFs that can be used for writing down new transactions.
4) A full or differential database backup clears the online transaction log
Not true, they don’t clear the online transaction log. The inactive parts of the online transaction log are marked for clearing only when a transaction log backup is created
Full and differential database backups don’t contain much 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
5) 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
6) 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
7) The transaction log shrinking will make free space in the online transaction log so I don’t need to take the transaction log backup
Not true, the transaction log will grow again. 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.
8) A transaction log backup will be the same size as the online transaction log itself
Not true, it will be smaller. 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 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 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 transaction log backup, therefore the transaction log backup will be smaller for that amount of space
9) The transaction log won’t grow if the database is in the Simple recovery model
Not true, it will. 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
10) Having multiple online transaction log files will increase performance
Not true, it will not increase performance. This myth is based on the belief that having multiple online transaction log files will result in parallel writing of the 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 transaction log can’t record more transactions due to a lack of free space on the disk
11) Regular log shrinking is a good maintenance practice?
FALSE. Log growth is very expensive because the new chunk must be zeroed-out. All write activity stops on that database until zeroing is finished, and if your disk write is slow or autogrowth size is big, that pause can be huge and users will notice. That’s one reason why you want to avoid growth. If you shrink the log, it will grow again and you are just wasting disk operation on needless shrink-and-grow-again game. You could shrink after some extraordinary event that blew-up log size, but never shrink to a size smaller than is normal operational size for that database
- Full/diff backup will clear the transaction log
FALSE. Only transaction log backup in full and bulk_logged recovery model, or checkpoint in simple recovery model will trigger the log clearing process. Full and diff backups will only defer it until the backup finishes. If you only do a full/diff backup in full recovery model, your log will grow until the disk is full.