Forum Discussion
Easiest way to calculate buffer count for faster SQL server backup
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\AdventureWorks.BAK'
WITH STATS = 1, COMPRESSION, CHECKSUM, buffercount=20, maxtransfersize=2097152
GO buffercount = NumberofBackupDevices* 3 +...
Yes, I have access to VM and can check performance counters.
For example, if the data file .mdf is in drive D, the log file .ldf is in drive L and the single backup file .bak is created in drive B, so buffercount should be 10?
buffercount = NumberofBackupDevices* 3 + NumberofBackupDevices + (2 * NumberofVolumesInvolved)
buffercount = 1* 3 + 1+ (2 * 3) = 10
By default, MS SQL Server uses 7 as BufferCount checked for my many SQL servers.
For example, if the data file .mdf is in drive D, the log file .ldf is in drive L and the single backup file .bak is created in drive B, so buffercount should be 10?
buffercount = NumberofBackupDevices* 3 + NumberofBackupDevices + (2 * NumberofVolumesInvolved)
buffercount = 1* 3 + 1+ (2 * 3) = 10
By default, MS SQL Server uses 7 as BufferCount checked for my many SQL servers.
this is what we do when we want to perform a backup faster. we change buffercount from 1 to 10(yours will be 7 to 10) and performs backup and we watch a performance counter to check up server uses more storage throughput and so on.