FIX: Error when you run RBS Maintainer in SQL Server 2012 or SQL Server 2014

Van toepassing: SQL Server 2012 DeveloperSQL Server 2012 EnterpriseSQL Server 2012 Standard Meer

Symptoms


Assume that you are using Microsoft SQL Server 2012 or SQL Server 2014. When you run Remote BLOB Storage (RBS) Maintainer, the RBS Maintainer tool fails, and you receive the following error message:
Starting Maintainer tasks.
Starting Garbage Collection.
Starting Reference Scan.
Reference Scan is complete for this database.
Scanned 113 blobs. Deleted 0 blobs in the range of 0x000000000000000000000000(exclusive) to 0x00000000000000b600000002(inclusive).
This task has ended. Processed 1 work units total. 0 Work units were incomplete.
Starting Delete Propagation.
No work is available at this time.
Other clients, processes or threads may be currently working on other tasks.
Skipping the current unit of work because of an error. For more information, see the RBS Maintainer log.
[..]
This task has ended. Processed 102 work units total. 101 Work units were incomplete. Needed to delete 0 blobs. Succeeded in deleting 0 blobs, 0 blobs were not found in the blob store.
This task has ended.
This task has ended.

When you use Profiler to trace the problem, you receive the following error message:
Msg 50000, Level 16, State 0, Procedure rbs_sp_rethrow_error, Line 38
RBS Error. Original Error: Number 535, Severity 16, State 0, Procedure rbs_sp_count, Line 52,
Message: The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

Cause


This problem occurs because a query that checks the time since the last execution can overflow when it converts a value to milliseconds. This causes the query to fail and the task to end without processing the data.

Resolution


The fix for this problem is available in latest feature pack download for RBS.msi. If you already have an existing installation of RBS, we recommend that you run the script that is provided in the "Workaround" section to temporarily fix the issue.
 

Workaround


If you have an existing installation of RBS on SQL Server 2012 or SQL Server 2014 that does not have the fix installed, you can use the following workaround steps:

  1. In SSMS, open the script from step 3, and then connect to the SQL Server instance that hosts the RBS client database that you want to apply the fix to.
  2. In the script, replace the “your_RBS_client_database” string with the actual name of the RBS client database.
  3. Run the script to apply the fix.

    use [your_RBS_client_database]
    go


    if object_id(N'mssqlrbs_resources.rbs_sp_count') is not null
    drop procedure mssqlrbs_resources.rbs_sp_count
    go


    create procedure mssqlrbs_resources.rbs_sp_count (
    @operation smallint,
    @object smallint = 0,
    @value bigint = null,
    @start_time datetime = null,
    @end_time datetime = null,
    @description sysname = N'',
    @message nvarchar(max) = null
    )
    as
    begin

    declare @type tinyint,
    @secondsUsed bit,
    @seconds float

    declare @maxDays int = 10;


    if not exists (
    select * from mssqlrbs_resources.rbs_internal_counters
    where operation = @operation and object = @object and description = @description
    )
    raiserror (N'Internal error in RBS. Attempt to count invalid performance counter. Counter: <%d>.<%d>.<%s>.',
    16, 101, @operation, @object, @description)

    select
    @type = type,
    @secondsUsed = seconds_used
    from
    mssqlrbs_resources.rbs_internal_counters
    where
    operation = @operation and
    object = @object and
    description = @description

    if @object != 0
    begin
    if @value is null
    raiserror (N'Internal error in RBS. Value not specified for performance counter <%d>.<%d>.<%s>.',
    16, 103, @operation, @object, @description)
    end

    if @secondsUsed = 1
    begin
    if (@start_time is null)
    raiserror (N'Internal error in RBS. Start time not specified for performance counter <%d>.<%d>.<%s>.',
    16, 102, @operation, @object, @description)

    set @end_time = isnull(@end_time, getutcdate())

    if (@start_time > @end_time)
    raiserror (N'Internal error in RBS. Start time is after end time. Counter: <%d>.<%d>.<%s>.',
    16, 104, @operation, @object, @description)

    if (datediff(day, @start_time, @end_time) >= @maxDays)
    begin
    set @seconds = convert(float, datediff(second, @start_time, @end_time))
    end
    else
    begin
    set @seconds = convert(float, datediff(millisecond, @start_time, @end_time)) / 1000
    end
    end


    update mssqlrbs_resources.rbs_internal_counters
    set
    count = count + 1,
    value = value + @value,
    squared_value = squared_value + square(@value),
    min_value = case
    when min_value is null then @value
    when (@value < min_value) then @value
    else min_value
    end,
    max_value = case
    when max_value is null then @value
    when (@value > max_value) then @value
    else max_value
    end,
    last_value = @value,
    seconds = seconds + @seconds,
    squared_seconds = squared_seconds + square(@seconds),
    min_seconds = case
    when min_seconds is null then @seconds
    when (@seconds < min_seconds) then @seconds
    else min_seconds
    end,
    max_seconds = case
    when max_seconds is null then @seconds
    when (@seconds > max_seconds) then @seconds
    else max_seconds
    end,
    last_seconds = @seconds,
    last_update_time = getutcdate(),
    last_message = @message
    where
    operation = @operation and
    object = @object and
    description = @description

    end
    go

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.