Over the last couple of years, I have met a number of you, who as I, are required to struggle daily to make CommVault work as the enterprise backup solution for your SQL Server databases. Given that, I thought I would share with you one of the issues that we have run into and possibly any third party product could run into that uses Microsoft’s Volume Shadow Copy Services (VSS). To be fair, I have to give the credit for the research and most of the write up of this issue and solution to one of the DBA’s that works for me (whom I am sure many of you know J), Samson Loo (twitter: @ayesamson).

Problem:

I/O operations are frozen on one or more databases due to CommVault issuing a “BACKUP DATABASE WITH SNAPSHOT” command and remain frozen until the operation completes successfully or is cancelled. (This is appears to be known behavior of VSS. If you wish to dig further into how VSS works, I would suggest reading these articles: http://msdn.microsoft.com/en-us/library/windows/desktop/aa384615(v=vs.85).aspx and http://msdn.microsoft.com/en-us/library/aa384589(v=VS.85).aspx).

This particular message gets logged in the SQL Server error log whenever any backup service makes use of the SQL Server Virtual Device interface to backup the database with snapshot. Microsoft Backup (ntbackup.exe), Volume Shadow Copy Services, Data Protection Manager, Symantec Backup Exec and other third party tools, in addition to CommVault can cause this message to be logged.

If ntbackup.exe is configured to take a backup of a drive that happens to house SQL Server data files, then the command “BACKUP DATABASE WITH SNAPSHOT” is issued to ensure the backup is consistent since the data files are in use. During this time, the I/O for the database that is currently being backed up is frozen until the backup operation is complete.

The message that you will typically see logged is:

    Error:

I/O is frozen on database master. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.

 

 

    Note: In the example error message above, the database master was referenced, but this could be any database on your instance that is being backed up.

Solution:

Disable VSS in the subclient following the steps below:

  1. Open the CommVault client (often called the “Simpana CommCell Console”)
  2. Navigate to the target subclient
  3. Right click anywhere in the white area

  4. Select Properties
  5. Uncheck “Use VSS” and click OK

 

Again, extreme thanks go out to Samson Loo (twitter: @ayesamson) for providing most of this content!

The ability to lock pages in memory is used on 64-bit systems to help prevent the operating system from paging out SQL Server’s working set.  This may be enabled when the DBA starts seeing errors like the following:

 

"A significant part of sql server process memory has been paged out. This may result in a performance degradation."

 

If you’re running SQL Server 2005/2008 Enterprise, you would take the steps to lock pages in memory and you’re done with it.   If you’re on SQL Server 2005/2008 Standard Edition, you still have a ways to go.  The ability to lock pages in memory for standard

This flag will not help in this situation

This flag will not help in this situation

edition is handled through  a trace flag.  For SQL Server 2005 SP3, you need to apply CU4 .  For SQL Server 2008 SP1, you need to apply CU2.    Once those CUs have been applied, set trace flag 845 as a startup parameter.  Here’s a good ServerFault question that explains how to set a trace flag as a startup parameter.

 

Once the trace flag was enabled, the memory issues were solved.   Day saved, once again. :)  

 

As with anything, this has the potential to degrade system performance.   In this article, scroll to the section entitled “Important considerations before you assign “Lock Pages in memory” user right for an instance of a 64-bit edition of SQL Server”.  Read it thoroughly prior to making any changes to your production systems.