SPEEDING UP DATABASE BACKUPS IN SQL SERVER

You might be dealing with very large databases whose size is in hundreds of GB’s but still you will be  having a same maintenance window where backup job may be taking much time to complete,for this type of situations we can consider using data transfer options available in the backup command,

before I go onto the available options let me explain how backup works internally.

During a backup, SQL Server creates one reader thread for each volume that the database files reside on.  The reader thread simply reads the contents of the files.  Each time that it reads a portion of the file, it stores them in a buffer.  There are multiple buffers in use, so the reader thread will keep on reading as long as there are free buffers to write to.  SQL Server also creates one writer thread for each backup device to write the contents of the buffers out to disk or tape.  The writer thread writes the data from the buffer to the disk or tape.  Once the data has been written, the buffer can be reused by the reader thread.

Let us consider below example.

BACKUP DATABASE AdventureWorks TO
DISK = 'D:\backups\AdventureWorks1.bak',
DISK = 'E:\backups\AdventureWorks2.bak'
WITH BUFFERCOUNT = 10, MAXTRANSFERSIZE = 1048576
Let us assume that adventureworks has spread across 4 volumes then the backup will create 4 reader threads to read from 4 volumes into 10 buffer files(buffercount=10) each of size 1 MB(maxtransfersize=1MB) and two writer threads will be created to write to two files(it depends on how many files you are writing into) from buffers.

If buffercount and maxtransfersize parameters are not used,sql server will dynamically determine the numbers to use and size of each buffers and the total memory used by the backup buffers will be buffercount*maxtransfersize

In our example it will be 10* 1 MB=10 MB

Above memory will be allocated from non buffer pool memory also known as memtoleave memory.If you specify the values larger than the available memory,sql server will raise an insufficient memory error.

Coming back to our actual issue, we were trying to backup a DB of size 100 GB over the network with the below sql  command

BACKUP DATABASE [DBNAME] TO DISK = N'\\XXXXX\sql_backups\daily\DBNAMe_FULL_20131113_213000.BAK' WITH INIT, SKIP, NOREWIND, NOUNLOAD,  STATS = 10, COMPRESSION

After running for several hours(7 hrs) the command used to fail with the below error.

BackupIoRequest::ReportIoError: write failure on backup device '\\XXXXX\sql_backups\ daily\Dbanme_FULL_20131113_213000.BAK'. Operating system error 64(The specified network name is no longer available.).

After which we had changed the command to below:

BACKUP DATABASE [DBName] TO

DISK = N'\\XXXXXXX\sql_backups\daily\DBNAME_FULL1_20131113_213000.BAK',

DISK = N'\\XXXXXXX\sql_backups\daily\DBANME_FULL2_20131113_213000.BAK',

DISK = N'\\XXXXXX\sql_backups\ daily\DBANAME_FULL3_20131113_213000.BAK',

DISK = N'\\XXXXXXX\sql_backups\daily\DBNAME_FULL4_20131113_213000.BAK'

WITH INIT,FORMAT, SKIP, NOREWIND, NOUNLOAD,  STATS = 1, COMPRESSION,BUFFERCOUNT=12,

 MAXTRANSFERSIZE=4194304

With the above values backup time has decreased to almost half and I will be trying with further different values and see how quick the backup completes and will share that info in future.

And lastly if you want to know how much memory that your backup command is using with these values specified in your backup command you can enable below trace flags and the information will be written into sql server log file.

DBCC TRACEON(3605,3213,-1)

Hope above information helps you all…