What happens if there is no transaction log backup?
Recall that only the Transaction Log backup, but NOT the database Full backup, will truncate the Transaction Logs from the Transaction Log file and makes it available for reuse. If no Transaction Log backup is taken from the database, the Transaction Log file will grow continuously, without truncation, until it runs out of free space.
When to truncate SQL Server transaction log file?
If no Transaction Log backup is taken from the database, the Transaction Log file will grow continuously, without truncation, until it runs out of free space. The SQL Server Transaction Log backup can be taken only from the database when the recovery model of that database is Full or Bulk-Logged.
What is the backup chain in SQL Server?
The Full backup and all following Transaction Log backup until a new Full backup is taken is called Backup Chain. This backup chain is important to recover the database to a specific point in time, in the case of any mistakenly performed change or database corruption.
When to use shrink in SQL Server transaction log?
The Transaction Log file shrink operation can be performed only if there is free space on the Transaction Log file, that can be available most of the time after truncating the inactive part of the Transaction Log. A shrink operation will be useful after performing an operation that creates a large number of Transaction Logs.
How big is a SQL Server transaction log?
We use SQL Server 2012 (SP2) running in Full recovery mode. We take half-hourly transaction log backups. Our DB size is 20GB. Every night we perform DB maintenance tasks – full backup, rebuilding some indexes, update statistics, clearing some old data. The half-hourly transaction log backups save to an external HDD.
When to back up transaction log in SQL Server?
This topic discusses backing up the transaction log of a SQL Server database. Minimally, you must have created at least one full backup before you can create any log backups. After that, the transaction log can be backed up at any time unless the log is already being backed up.
How big is a transaction log LDF file?
Our transaction log LDF file consistently grows to around 13GB. The transaction log backup .trn files are around 20-30MB (depending on activity of DB of course), but the one immediately after the nightly maintenance tasks is 13GB.
When does SQL Server transaction log go inactive?
When the database is configured with Full recovery model, the SQL Server Transaction Log in the Transaction Log file will be marked as inactive after committing the transaction, without being truncated automatically, as it will be waiting for a Transaction Log backup to be performed.
What to do if your transaction logs are on a different disk array?
If your transaction logs are on a different disk array than your data file, you perform a ‘ tail of the log backup’in case of a data array failure. This would ensure minimal data loss so you wouldn’t even lose that hour.