Rajendra Gupta
SQL FILESTREAM demo backup

Transaction log backups in a SQL FILESTREAM database

March 5, 2019 by

In the continuation of our SQL FILESTREAM article series, we’ll be covering transaction log backups

In SQL Server, we take transaction log backups regularly to have a point-in-time recovery of a database. It is essential to define the backup policy with the combination of full, differential and transaction log backups. A standard configuration of backups for large databases in the production database environment is as follows.

  • Weekly Full backup
  • Daily differential backup
  • Transaction log backup every 30 minutes

We need to consider the transaction log backup size while designing a backup and recovery solution. In SQL FILESTREAM database, we store the objects into FILESTREAM container in the file system. SQL Server requires both metadata stored in relational tables and corresponding objects into the file system to recover a database. A Transaction log backup in FILESTREAM databases copies the relevant file from the FILESTREAM container as well into a backup file. It copies the files from the FILESTREAM container in the transaction log backup that is created, modified after the last transaction log backup. It is important to understand the behaviour of the log backup for FILESTREAM database. In this article, we will explore the transaction log backup for the FILESTREAM database backup.

Prerequisites

  • SQL Server instance with FILESTREAM feature
  • FILESTREAM database
  • FILESTREAM table

In this article, we are using the [DemoSQL] FILESTREAM database with FILESTREAM table [DemoSQL].[dbo].[SQLShack]

SQL FILESTREAM demo

We do not have any database backups for this database as of now. You can verify it by right click on the database and Properties.

SQL FILESTREAM demo backup

Before we proceed further, take a full backup and log backup for this SQ Server FILESTREAM database.

Execute Full backup using the following script

Execute Log backup using the following script

SQL FILESTREAM demo log backup

In SQL Server 2017 and onwards, we can use DMV sys.dm_db_log_stats to get information about the transaction log. Execute the following query in the FILESTREAM database.

At this point, we are having 0.085937 MB active transaction log size.

SQL FILESTREAM demo transaction log

Let us insert one record in FILESTREAM table.

In the following screenshot, we can see a minimal change in the active log size from 0.085937 MB to 0.117187 MB

SQL FILESTREAM active log change

Execute the transaction log backup again. Log backup size is 9.332 KB now. We inserted only 1 record in the FILESTREAM table.

SQL FILESTREAM log backup size

Insert one more record and monitor the transaction log size.

SQL FILESTREAM demo monitor transaction log size

Update the object in FILESTREAM table as well using the following query.

After the update also, we have a small active log of 0.296875 MB

SQL FILESTREAM small active log

Execute the log backup again and view the transaction log size. In the following image, you can see log backup size increased to 135,452 KB.

SQL FILESTREAM demo - re-execute backup

I performed a few more DML command on SQL FILESTREAM table and took the log backup. It was surprising to see the log backup size grown to 23.2 GB.

SQL FILESTREAM demo - transaction log growth

In the above examples, we observed, using the active log size, that we did not have much transaction log growth but still we have a huge size of log backups. SQL Server does not store the objects for the FILESTREAM database into its relational table. Therefore, it does not require more log size to perform the transactions. SQL Server copies the FILESTREAM files as well in the transaction log backup. It is the reason you might see a huge transaction log backup size. We need to consider below points in transaction log backup for SQL FILESTREAM databases.

  • Insert statement: Suppose we are only inserting the objects into the FILESTREAM table, log backup contains the copy of the inserted object
  • Update Statement: In case of update objects into FILESTREAM table, log backup contains the copy of the old object and new object as well. If we perform multiple update queries on FILESTREAM table, log backup size might increase significantly
  • Delete Statement: In case of delete activity, log backup contains the copy of the deleted object as well

SQL Server removes the files from the FILESTREAM container once the transaction log backup does not require these files. Once we take the log backup, it takes backups of all required files from the FILESTREAM container, and then garbage collector process removes these files from the container.

You can notice a considerable time in the log backup progress even after 100 percent processed status. It indicates that SQL Server is copying the files from the FILESTREAM container to log backup file.

SQL Server FILESTREAM demo - copying files from FILESTREAM container

Let us examine the log backup file. We can use the RESTORE FILELISTONLY command to get a list of database files included in the backup file.

In this example, I am using the below 1.81 GB log backup file.

SQL Server FILESTREAM demo - backup flie

Execute the following query and observe the output.

We can observe the following things from the output.

SQL Server FILESTREAM demo - - output

Log backup file contains all DB files

  1. Primary database file ( Type D)
  2. Transaction log file ( Type L)
  3. FILESTREAM container ( Type S)

Look on highlighted column BackupSizeinBytes in the output. We have following files size in this 1.81 GB log backup.

  1. DemoSQL.mdf backup size 0 bytes
  2. DemoSQL_log.ldf backup size 0 bytes
  3. DemoSQL FILESTREAM files backup size 1943732224 bytes i.e. 1.81 GB.

In this case, we have the log backup size(1.81 GB) equivalent to FILESTREAM container size( 1.81 GB) . It proves our point stated earlier that log backup copies the objects from FILESTREAM container into log backup.

We need to note here that SQL Server does not copy the entire files from the FILESTREAM container. You could see a difference in overall FILESTREAM container size and log backup size because it copies only those files that are required for the transaction log backup. In our case, we had FILESTREAM container size 1.93 GB however log backup size 1.81 GB.

SQL Server FILESTREAM demo - backup size

Let us explore more on log backups for SQL FILESTREAM database using extended events in SQL Server.

Create an extended event using the following query. In this extended event session, we need to capture the SQL Server backup and restore progress trace sqlserver.backup_restore_progress_trace.

Once we have created the extended event session, right click on it under the Management and Extended Events. Click on Watch Live Data.

SQL Server FILESTREAM demo - watch live data

Execute the transaction log backup for FILESTREAM database now and view data in Live Data session window. In the following section, we will explore the output of the extended event session output.

Once we start the log backup process, in the extended event session, we can see a message Backup Log started.

SQL Server FILESTREAM demo - backup log started

It acquires the required locks on the databases and opens the backup media set to write.

SQL Server performs the estimation of the backup size. If you do not have sufficient free space in the backup drive, you get an error based on this estimated size. You can note the estimated total size is 2416640 bytes, i.e. 2.30 MB. It scans SQL FILESTREAM data after the work estimation. The work estimation size does not include the FILESTREAM container files size required to be backed up.

SQL Server FILESTREAM demo - work estimation

SQL Server starts copying the FILESTREAM data into the log backup. We need to note here that at 100 percent stage, we still have backup size 2416640 bytes, i.e. 2.30 MB.

SQL Server FILESTREAM demo - 100% stage

In the following screenshot, we can look at the timings for the FILESTREAM data copy.

  • Copying FILESTREAM data: 2019-03-02 10:12:01.5490612
  • Finish copying FILESTREAM data: 2019-03-02 10:13:30.9988646

SQL Server FILESTREAM demo - data copy timing

It is the reason you can notice that transaction log backup takes time even after we receive 100 percent processed message. If SQL Server needs to copy large size of FILESTREAM container into log backup, it might take longer to finish the backup.

Once it finishes copying FILESTREAM data, it copies the required transaction log and completes the log backup.

SQL Server FILESTREAM demo - copying transaction log

Considerations for log backups in SQL FILESTREAM databases

  • Monitor the FILESTREAM database activity in your environment to plan transaction log backups
  • You should have sufficient free disk space to accommodate FILESTREAM database log backup
  • Keep a watch on the log backup size for FILESTREAM database and take actions accordingly
  • Schedule the log backup frequency to a minimum duration to keep log backups size to a minimum size

Table of contents

FILESTREAM in SQL Server
Managing data with SQL Server FILESTREAM tables
SQL Server FILESTREAM Database backup overview
Restoring a SQL Server FILESTREAM enabled database
SQL Server FILESTREAM database recovery scenarios
Working with SQL Server FILESTREAM – Adding columns and moving databases
SQL Server FILESTREAM internals overview
Importing SQL Server FILESTREAM data with SSIS packages
SQL Server FILESTREAM queries and Filegroups
Viewing SQL Server FILESTREAM data with SSRS
SQL Server FILESTREAM Database Corruption and Remediation
Export SQL Server FILESTREAM Objects with PowerShell and SSIS
SQL FILESTREAM and SQL Server Full Text search
SQL Server FILESTREAM and Replication
SQL Server FILESTREAM with Change Data Capture
Transaction log backups in a SQL FILESTREAM database
SQL FILESTREAM Compatibility with Database Snapshot, Mirroring, TDE and Log Shipping
SQL Server FILETABLE – the next generation of SQL FILESTREAM
Managing Data in SQL Server FILETABLEs
SQL Server FILETABLE Use Cases

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
221 Views