Logs plays an important role in SQL Server Database. Transaction log records all transaction activities done in SQL Server database. After every modification in transaction, a log record is written to the transaction log file.
Here we will be discussing on various transaction log myths which various DBAs and users think that they are true. Let us discuss misconception around transaction logs.
#Myth1- Transaction Log truncation make the log file smaller.
Reality – No, Truncation does not reduce the size of physical log file. At the time of truncation, only the active portion is scanned. Some parts are marked as inactive and they are used as free space to write down new transaction. There is no change done in the size of transaction log as the parts which are inactive remain intact and nothing is removed or deleted.
#Myth2- No need to take log backup for disaster recovery, if you are taking full backup daily.
Reality – This is wrong! Full backup does not mark log file as reusable. It depends on the amount of data you can afford to loose. If you don’t worry to loose your transaction log data, then you should use Simple recovery model. Regular taking transaction log backup does not allow you to afford lost transaction log data.
#Myth3- SQL Server is too busy. I think, I don’t need to take backup of SQL Server Transaction log
Reality – This is completely untrue! If your SQL Server is too busy, you should take more frequent backups. If you are not taking regular SQL transaction log backup, the transaction log will become full resulting in growth of transaction logs. Busier the SQL Server, more frequent you should take log backups. Taking regular log backup does not block transaction log, but result in an Auto growth event.
#Myth4 – If I perform full backup, I Don’t need to perform log backup for point-in-time restore.
Reality- This is one of the common misconception every user is believing. The reason for this myth is RESTORE command used with STOPAT clause. STOPAT clause specifies point in time for RESTORE LOG command. This command works well when it is used with log backups. Hence it can also be used with full database backup, it is clear that transaction log backup are not needed to recover at specific point in time.
#Myth5 – Shrinking frees space in SQL transaction log so taking transaction log backup is not necessary.
Reality – Shrinking operation is not a good practice. It does not resolve the log size issue. The transaction log will grow again, after performing initial shrinking operation.Auto-growth event must be avoided. You can maintain the size of transaction log by performing regular log backup. Or if you afford data loss, you can set recovery Model as Simple Recovery Model.
Want to read Transaction Logs Quickly?
You can read transaction log in SQL Server by using SQL Server Transaction log reader. It previews all the log activities like Transaction , Time, table name , query. The software fetch & preview records from Live database and it works both as online and offline SQL database environment.
Conclusion:
Transaction logs plays crucial role in SQL Server Database. The blog covers misconception around transaction logs in SQL Server. It also discusses a quick way to read transaction log activities in SQL Server.
SQL Canada (SQLCAN) 