About cookies on this site Our websites require some cookies to function properly (required). In addition, other cookies may be used with your consent to analyze site usage, improve the user experience and for advertising. For more information, please review your options. By visiting our website, you agree to our processing of information as described in IBM’sprivacy statement. To provide a smooth navigation, your cookie preferences will be shared across the IBM web domains listed here.
Troubleshooting
Problem
Backups using multiple stripes may hang with Data Protection for Microsoft SQL Server
Cause
For the backup with Tivoli Storage Manager Data Protection for Microsoft SQL Server, output mount points must be available for all stripes at the same time in order for SQL Server to start backup processing.
If the value of the stripes option is larger than the number of devices available in Tivoli Storage Manager Server, some sessions can acquire output mount points but go into IdleWait status. Other sessions that are waiting for a mount point will enter MediaWait status until additional output resources can be obtained by the Server. Backup operations for the node will hang until all of the mount points are obtained, or until the backup fails for exceeding the IDLETimeout setting. In other words, as long as there are backup sessions for the node in MediaWait status, the SQL backup will not start sending data to the Server.
Diagnosing The Problem
When performing a Data Protection for Microsoft SQL Server backup command with stripes larger than 1, the operation can hang or wait for a long time (IDLETimeout or longer) before sending backup data to the Tivoli Storage Manager Server.
If the Data Protection for the Microsoft SQL Server command line is used, the output will show a message that the backup is waiting on the Server. Please refer to the following screenshot:
The "query session" command output on the Server will show at least one session in MediaWait and the other Sessions in IdleWait for the node. For example:
The following message will be in activity log of Server:
- ANR0511I Session 37044 opened output volume /home/tsminst1/tsmbackup/00000032.BFS. (SESSION: 37044)
- ANR0514I Session 37044 closed volume /home/tsminst1/tsmbackup/00000032.BFS. (SESSION: 37044)
ANR0482W Session 37044 for node TIVVM511_SQL (TDP MSSQL Win64) terminated - idle for more than 15 minutes. (SESSION: 37044)
- ANR8340I FILE volume /home/tsminst1/tsmbackup/00000032.BFS mounted. (SESSION: 37043)
Resolving The Problem
When performing a backup with the Data Protection for Microsoft SQL Server, the backup can be specified to use multiple stripes (Please refer to http://www.ibm.com/support/knowledgecenter/SSGSG7_7.1.0/com.ibm.itsm.db.sql.doc/dps_ref_opt_backupoptional.html?lang=en). Data Protection for Microsoft SQL Server will start a session for each stripe. Once all sessions have a volume mounted for each stripe, the backup will start. If adequate mount points are not available for all sessions, the backup will hang while waiting for an output volume. This will happen when you use file device class or sequential access device class, like tape or VTL.
Follow these points to resolve this problem:
- If the number of available device is less than the value of stripes, reduce the value of stripes option to make sure it is smaller than or equal to the number of available devices for the specified Data Protection for Microsoft SQL Server node in Tivoli Storage Management Server. Consider also that other operations may access the devices in parallel. This would reduce the number of available devices for the Data Protection for Microsoft SQL Server node.
- If the number of available device is already larger than or equal to the value of stripes, check the mountlimit option in TSM Server. Make sure the value of mountlimit option larger than or equal to the value of stripes.
- How to choose the value of stripes option in backup,
The range of this value is from 1 to 64. - Use one data stripes per physical volume for the SQL Server files, the performance will show an improvement over the unstriped case. Each data stripe will needs a drive in the Tivoli Storage Manager server to run the backup. So the value of stripes should less than or equal to the number of available devices for the specified Data Protection for Microsoft SQL Server node in Tivoli Storage Manager Server.
- If you have both sqlbuffers and stripes in backup command, ensure the value of sqlbuffers is less than stripes. The best practice is as follows,
SqlBuffers = NumberofBackupDevices * 3 + Stripes + 2 * NumberofVolumesInvolved
NumberofVolumesInvolved is a placeholder for the number of database files that you are going to back up. Please refer this article for more details on optimal values of SqlBuffers and Stripes.
http://support.microsoft.com/kb/904804/en-us
[{"Product":{"code":"SSTFZR","label":"Tivoli Storage Manager for Databases"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Data Protection for MS SQL","Platform":[{"code":"PF033","label":"Windows"}],"Version":"Version Independent","Edition":"All Editions","Line of Business":{"code":"LOB26","label":"Storage"}}]
Product Synonym
TSM
DP for SQL ITSM ADSM TSM
Was this topic helpful?
Document Information
More support for:
Tivoli Storage Manager for Databases
Software version:
Version Independent
Operating system(s):
Windows
Document number:
254751
Modified date:
09 August 2018
UID
swg21687983