none
Data Protection Manager (DPM) SQL Database Consistency Check Always Fails ID 2033 RRS feed

  • Question

  • Hello,

    I am backing up all SQL databases for an SQL Server using Microsoft Data Protection Manager (DPM).

    Most of the Databases backup successfully except the 2 largest Databases which always fail after a few minutes. They then try to run a consistency check but that also always fails with the below error:

    Affected area:    SERVER_NAME\database_name
    Occurred since:    08/07/2020 12:50:45
    Description:    The replica of SQL Server 2016 database SERVER_NAME\database_name on server.domain.com is inconsistent with the protected data source. All protection activities for data source will fail until the replica is synchronized with consistency check. You can recover data from existing recovery points, but new recovery points cannot be created until the replica is consistent.

    For SharePoint farm, recovery points will continue getting created with the databases that are consistent. To backup inconsistent databases, run a consistency check on the farm. (ID 3106)
        DPM encountered an error while performing an operation for \\?\GLOBALROOT\Device\HarddiskVolumeShadowCopy81\SQL_Data\MSSQL13.MSSQLSERVER\MSSQL\DATA\database_name.mdf on server.domain.com (ID 2033 Details: The system cannot find the file specified (0x80070002))
        More information
    Recommended action:    1) Refer to the detailed error code in the description above. Retry this operation after the issue has been fixed.
    2) If the error was due to insufficient resources, then it could be a transient failure and you should retry this operation after some time.
        Synchronize with consistency check.
        Run a synchronization job with consistency check...
    Resolution:    To dismiss the alert, click below
        Inactivate



    Any help would be greatly appreciated!

    Thank you,
    Ryan
    Wednesday, July 8, 2020 3:13 PM

All replies

  • Hi,

    Looking at the error it looks the shadowcopy: HarddiskVolumeShadowCopy81 got deleted and hence DPM threw the error mentioning :The system cannot find the file specified

    On the SQL server, make sure the drive has enough free space to hold shadowcopy for backups. As you mentioned it is happening for 2 largest DB, I am assuming that SQL Drive is running out of space and hence shadowcopy can not grow in time. Look for Volsnap Event ID:  25 on the SQL Server.

    Wednesday, July 8, 2020 3:16 PM
  • Hello Aayoosh,

    Thank you for your response.

    The two large failing Databases are 103 GB and 75 GB in size and are located on the D:\ of the SQL Server which has 271 GB free of 600 GB. I would assume that would be enough free space?

    I do see Error Event ID 25 on the SQL Server which has the description:

    "The shadow copies of volume D: were deleted because the shadow copy storage could not grow in time.  Consider reducing the IO load on the system or choose a shadow copy storage volume that is not being shadow copied."

    I also get Information Event ID 18456 on the SQL Server which has the description:

    "Login failed for user 'DOMAIN\SQLServerName$'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
    Reason: Could not find a login matching the name provided."

    But I'm guessing if it was due to Event ID 18456 then all Database DPM backups of the server would fail instead of just the largest two.

    The SQL Server does have a really high IO load but the CPU only gets up to about 40%. I've restricted the amount of RAM SQL uses to be 50 GB of the available 65 GB (80%).

    So I'm not sure why it would not have enough free disk space or what it means by choose a drive that isn't being shadow copied?

    Thanks again for your help.

    Ryan

    Thursday, July 9, 2020 6:58 AM
  • Hi,

    Since you are getting event id 25 that means shadowcopy couldn't grow in D volume due its hard limit set by default and hence it was deleted causing your backup job to fail.

    You need to make sure your Shadowcopy Storage capacity for D volume is not bound to certain Gigs as it may run out of space quickly.

    Use below command in CMD to check the currect SC storage allocation for D volume:

    vssadmin list shadowstorage

    You might see output similar to this:

    Shadow Copy Storage association
       For volume: (E:)\\?\Volume{01b3d1bc-1e21-4585-8cea-e22970cf0c38}\
       Shadow Copy Storage volume: (E:)\\?\Volume{01b3d1bc-1e21-4585-8cea-e22970cf0c38}\
       Used Shadow Copy Storage space: 3.22 GB (0%)
       Allocated Shadow Copy Storage space: 3.54 GB (0%)
       Maximum Shadow Copy Storage space: 9.52 GB (2%)

    As you can see, E volume on my computer has allocated on 2% of my total capacity of the disk and it may not be sufficient if am taking backups of large DBs.

    You can use below command to set it UNBOUND to ensure it uses the entire free space for the D volume.

    Use below format to change it.

    vssadmin resize shadowstorage /for=<ForVolumeSpec> /on=<OnVolumeSpec> [/maxsize=<MaxSizeSpec>

    For example: If I want to change the Shadowcopy size limit for my D volume to unbound, I would use below command:

    vssadmin Resize ShadowStorage /For=D: /On=D: /MaxSize=UNBOUNDED





    Thursday, July 9, 2020 7:06 AM
  • Hello Aayoosh and thanks again for your reply.

    I have now changed that so the Shadow Copy Storage association for the D:\ drive looks like:

    Shadow Copy Storage association
       For volume: (D:)\\?\Volume{b8ec5169-341c-47aa-8bc1-cf6516fc0a85}\
       Shadow Copy Storage volume: (D:)\\?\Volume{b8ec5169-341c-47aa-8bc1-cf6516fc0a85}\
       Used Shadow Copy Storage space: 0 bytes (0%)
       Allocated Shadow Copy Storage space: 0 bytes (0%)
       Maximum Shadow Copy Storage space: UNBOUNDED (2863922183%)

    After making the change I have restarted the DPMRA and Volume Shadow Copy services on the SQL server but the DPM backup still fails with the same error. Does the SQL server need to be restarted to pickup the change?

    Thank you,

    Ryan

    Thursday, July 9, 2020 12:25 PM
  • Hi, No you don't need to restart SQL services.

    How much data it transferred before it failed, please share the failed job details from DPM job section? And do you still see Event 25 on the SQL server?

    Thursday, July 9, 2020 12:59 PM
  • Hello again,

    Sometimes a few GB are copied before it fails but I just ran it again and only about 1 GB copied. DPM does indicated that 24 GB is used by that DB on the DPM server so it must have copied that amount at one point when the job ran.

    I do still see Event ID 25 on the SQL server at the time the DPM job fails.

    The failed job details are:

    Affected area:    SQL_Server\Database
    Occurred since:    09/07/2020 16:45:51
    Description:    The replica of SQL Server 2016 database SQL_Server\Database on sql_server.domain.com is inconsistent with the protected data source. All protection activities for data source will fail until the replica is synchronized with consistency check. You can recover data from existing recovery points, but new recovery points cannot be created until the replica is consistent.

    For SharePoint farm, recovery points will continue getting created with the databases that are consistent. To backup inconsistent databases, run a consistency check on the farm. (ID 3106)
        DPM encountered an error while performing an operation for \\?\GLOBALROOT\Device\HarddiskVolumeShadowCopy130\SQL_Data\MSSQL13.MSSQLSERVER\MSSQL\DATA\database.mdf on sqlserver.domain.com (ID 2033 Details: The system cannot find the file specified (0x80070002))
        More information
    Recommended action:    1) Refer to the detailed error code in the description above. Retry this operation after the issue has been fixed.
    2) If the error was due to insufficient resources, then it could be a transient failure and you should retry this operation after some time.
        Synchronize with consistency check.
        Run a synchronization job with consistency check...
    Resolution:    To dismiss the alert, click below
        Inactivate

    Thanks again,

    Ryan

    Thursday, July 9, 2020 2:51 PM
  • Hi,

    Looks like shadowcopy storage is not sufficient enough for the D volume hence you keep getting event 25. Since you have huge SQL IOPs on D volume,  you can probably increase the D volume capacity to ensure shadowcopy can grow during the backup. Or you can change the shadowcopy association to another free volume. By this doing this, it will also improve the overall disk Read and write performance.

    You can use below command to change the shadowcopy association for D volume to E in below example.

    vssadmin add shadowstorage /for=D: /on=E: /maxsize=UNBOUNDED

    Note: Since you are running CC, this will even take much longer to complete the job since it will do a block comparison from SQL d volume to the DPM volume. So I would suggest you to drop the replica (if you dont have many RPs) and start from scratch. It will help you a bit but you still need to ensure you have enough headroom for VSS to grow during the backup. 



    Thursday, July 9, 2020 3:05 PM
  • Hello

    I think I will create an extra drive dedicated just to shadow copies. Do you know how I would calculate how large that drive needs to be? Just a bit bigger than the largest database? Or a bit larger than the total size of all the databases?

    Thank you,

    Ryan

    Friday, July 10, 2020 5:38 AM
  • Hi,

    Yes - Using a dedicated volume for shadowcopies will provide better performance for SQL drives. There is no particular formula to calculate the diff area because it depends on various things like:- The number IOPS on a given disk, plus the duration of the backup and file size itself.

    But I would recommend to have an additional drive (for diff area) a bit larger than the total space used of source volume (Volume which is being shadow Copied).

    Lets take an example: If I have a D volume with total capacity as : 600gb. Out of which 450 GB is used. In this case I would prefer to have 550-600 gb diff area volume dedicate for volume shadowcopies. We also need to keep in mind that D volume data may increase with time so it may effect the diff area size as well. So we need to give enough headroom for the diff area after considering all the possibilities. 

    Friday, July 10, 2020 6:32 AM
  • Hi, 

    Checking in, if you are still facing the issue or has been sorted after allocating a dedicated volume for shadowcopies?

    Tuesday, July 14, 2020 5:26 PM
  • Hello Aayoosh,

    I didn't want to say anything too soon as to jinx it.

    Everything is now running smoothly and all my DPM Protection Groups including all the Databases on this SQL Server are backing up just fine now.

    The solution was to create a larger stand alone ShadowCopy drive on the SQL. The largest Database is 80GB but it was still failing with a 200GB dedicated drive. Once I created a 500GB drive which is used just for ShadowCopies for all other drives on the SQL Server it started working.

    What I did also notice in DPM is it does also backup the log files which are located on another drive dedicated just for the database log files. So the size of those log files need to be taken into account, and the Log Files drive needs to also use this new ShadowCopy drive for its ShadowCopies.

    We got there in the end.

    Thank you so much for taking the time to help me!

    A very satisfied customer after a long time of pulling my hair out.

    Thank you!

    Ryan

    Wednesday, July 15, 2020 7:50 PM
  • Hi Redward,

    Thank you for the update. I am glad I was able to help you :). Regarding SQL Log backups, it does not use VSS shadowocopy. It has its own login. You can create another thread to know more about it.

    (Please don't forget to mark a comment as answer if it helped you)


    Regards, Aayoosh Moitro Disclaimer: This posting is provided "AS IS" with no warranties or guarantees, and confers no rights.

    Wednesday, July 15, 2020 7:58 PM