*****
If your transaction log backups are negatively affecting your users because they take too long to run, consider backing up the logs more often so they are smaller. The smaller they are, then the less impact there will be on the server when the backup occurs. It is not uncommon to perform transaction log backups as often as 5 to 15 minutes, depending on transaction activity and on how much data you are willing to loose, assuming the database needs to be restored. [2000, 2005, 2008] Updated 2-3-2009
*****
SQL Server offer three database recovery models: Simple Recovery, Full Recovery, and Bulk-Logged Recovery. The database recovery model you choose can affect its performance during these operations: BULK INSERT, SELECT INTO, and CREATE INDEX (including indexed views), WRITETEXT, and UPDATETEXT.
For example, if you choose the Full Recovery model, BULK INSERT, SELECT INTO, and CREATE INDEX (including indexed views), WRITETEXT, and UPDATETEXT operations are all fully logged. While this ensures the best recoverability, it is not the fastest or most efficient way to perform these operations.
If you want to speed these operations, you can choose the Bulk-Logged Recovery model, where these operations are minimally logged. This option still provides a good level of recoverability, but is faster than using the Full Recovery model. [2000, 2005, 2008] Updated 2-3-2009
*****
If you find that your backup window is too short to perform your backups, or you find that you are running out of disk space in order to store your backups, consider compressed backups. SQL Server 2008 Enterprise Edition includes this feature built-in. For other versions of SQL Server, consider a third-party tool, such as SQL Backup, that both greatly reduces backup time, and at the same time, reduces the amount of disk space required to store it. [2000, 2005, 2008] Updated 2-3-2009
*****
If you are backing up to more than one backup device (using parallel backup threads) to boost backup or restore performance, you may need to increase the SQL Server “max worker threads” setting if your SQL Server is heavily used and has exceeded the default value of 255 worker threads.
If the “max worker thread” setting is set to it default value of 255, this means that if more than 255 connections are made to SQL Server, that SQL Server will no longer be able to devote a single worker thread per connection. In some cases, SQL Server will be forced to share worker threads between connections. If this is happening, and you attempt to perform a backup to more than one backup device, SQL Server will no longer be able to backup to these multiple backup devices in parallel because the backup process won’t be able to get access to all of the worker threads it needs to perform the backup in parallel to multiple devices. This, of course, defeats the purpose of doing this.
To overcome this problem, you will need to increase the “max worker thread” setting in SQL Server to a value high enough to not only cover all of the connections being made to SQL Server, but to also have enough extra to cover all of the parallel backup threads needed to backup to multiple backup devices. [2000, 2005, 2008] Updated 2-3-2009
]]>