Full Backup Does Not Truncate Transaction Log

This discussion assumes a database Recovery Model of “Full,” which is the normal recommendation for production databases.  This setting allows the backup of the transaction log so that you can recover up to the point of failure by restoring log backups in order.  A Recovery Model of “Simple” will automatically truncate the transaction log periodically, but you are not allowed to backup the log in this mode, so you can only recover using the last database backup, which implies potential loss of transactions.  This setting is usually reserved for test or read-only databases.

Backing up the transaction log will not only backup the latest committed transactions within the log but will also truncate the log file. Truncation means that the transactions that were backed up are removed from the log file, freeing up space within the log file for new transactions.  The truth is, if you don’t backup the transaction log, it will continue to grow forever, until you run out of disk space.

New DBAs to SQL Server assume that the Full Database Backup truncates the transaction log – it doesn’t.  It does take a snapshot of the transaction log at the very end, so that transactions committed during a long-running full backup are actually backed up, too (which is quite clever), but it does not truncate the log.  Well, the long-term solution is to backup the transaction log frequently. This will keep the transaction log “lean and mean.”  But how frequent is frequent?  Well, it depends.  Generally, we try to keep the transaction log under 50% of the size of the data files.  If it grows beyond this, then we back it up more frequently. This is why, for some very active databases, we may backup the log every 15 minutes.   As I said, it depends.

How to: Get the Default Backup Directory

The following query provides you the default backup directory of your SQL Server instance.

-- get the default backup directory
DECLARE    @BackupDirectory varchar(1000)

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory',@BackupDirectory OUTPUT;

SELECT  @BackupDirectory;

Log Shipping Error “Could not find a log backup file that could be applied to secondary database”

A simple explanation on how log shipping works could be “Backup-Transfer-Restore”. Transaction log will backup on the primary database and transfer the log backup file to the secondary database server, eventually restore it to the database. Thus, any changes on the primary database will reflect on the secondary database.

How to trouble shoot when the log shipping failed and the below error message found in the job history?

Could not find a log backup file that could be applied to secondary database '<DATABASE_NAME>'.

This means the current log backup file which being restored is too recent. A backup from a prior time need to be applied first. Since the log being backed up in sequence on the primary server, the log need to restore according to the sequence as well on the secondary database.

Use the following query to check when is the last log backup file that was copied:

SELECT * FROM [msdb].[dbo].[log_shipping_secondary]

And, also check what was the last log backup file was restore.

SELECT * FROM [msdb].[dbo].[log_shipping_secondary_databases]

Sometime larger transaction log file may takes longer time to copy over to the secondary server while the newer file already copied over, thus it looks unsorted on the secondary database. So wait for the big file to be copied and restart the log shipping job again. Second case, you may need to check the retention time on backup job that run on primary server.

If you see the file on primary server that was not yet copied, you may need to manually copy that over and then start the log shipping again. If the file is not there, you may need to reconfigure the log shipping process again. You can refer to this site on how to configure log shipping for your database.

How to: Get Backup Status in SQL Server 2005 & 2008

When someone asks how long will that backup job runs or how much work it has already done, the best option in SQL Server 2000 was to check the output given by the stats parameter in the Backup script. But if someone forgot to include the stats parameter in the backup script, there is no way to check the backup completion status in SQL Server 2000.

But starting SQL Server 2005, this behavior has changed. One of the many wonderful DMVs to be included in SQL Server 2005 is sys.dm_exec_requests. With this DMV, we can easily track the status, percentage completed etc. of the Backup process.

Here is the script:

	A.NAME,B.TOTAL_ELAPSED_TIME/60000 AS [Running Time],
		sys.dm_exec_sql_text(B.SQL_HANDLE))AS COMMAND
	sys.dm_exec_requests B
	percent_complete DESC,