Error on Hangfire connections to the SQL server

bug?
1

Any ideas on the below error, Its being logged for me few times a day. I don’t see any impact on my functionality but its definitely related to Hangfire connections to the SQL server.

String or binary data would be truncated.
The statement has been terminated. System.Data.SqlClient.SqlException Void OnError(System.Data.SqlClient.SqlException, Boolean, System.Action`1[System.Action])    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Dapper.SqlMapper.ExecuteCommand(IDbConnection cnn, CommandDefinition& command, Action`2 paramReader)
   at Dapper.SqlMapper.ExecuteImpl(IDbConnection cnn, CommandDefinition& command)
   at Dapper.SqlMapper.Execute(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType)
   at Hangfire.SqlServer.SqlServerConnection.AnnounceServer(String serverId, ServerContext context)
   at Hangfire.Server.ServerBootstrapper.Execute(CancellationToken cancellationToken)
   at Hangfire.Server.AutomaticRetryServerComponentWrapper.ExecuteWithAutomaticRetry(CancellationToken cancellationToken)
2

Are you passing a custom value for ServerId? If not, what machine name do you have?

3

I coded like below:

           if (_started) return;
            _started = true;

            HostingEnvironment.RegisterObject(this);

            var sqlOptions = new SqlServerStorageOptions
            {
                PrepareSchemaIfNecessary = false,
                InvisibilityTimeout = TimeSpan.FromMinutes(invisibilityTimeout),
                QueuePollInterval = TimeSpan.FromSeconds(pollInterval)
            };

            JobStorage.Current = new SqlServerStorage("HangfireDB", sqlOptions);
            logger.Trace("HangfireDB:" + JobStorage.Current.ToString());

            GlobalJobFilters.Filters.Add(new AutomaticRetryAttribute { Attempts = retryAttempts });

            var options = new BackgroundJobServerOptions
            {
                ServerName = String.Format(
                    "{0}.{1}",
                    Environment.MachineName,
                    Guid.NewGuid().ToString())
            };

            _backgroundJobServer = new BackgroundJobServer(options);
            _backgroundJobServer.Start();
4

The problem is caused by the max size of Hangfire.Server.Id column, its value is 50 now. Don’t understand why I set such a small limit for a table with a few records. I’ve created a GitHub workitem for this issue, however there are some workarounds available.

Workarounds

  1. Open SQL Server Management Studio and increase that column’s size to 100 or so.
  2. You may skip the Guid parameter – it is not necessary, since the server id will be generated on the basis of Environment.MachineName and a process id to be unique.
ServerName = String.Format(
    "{0}.{1}",
    Environment.MachineName,
    Guid.NewGuid().ToString())
5

Hey Serj

As suggested I trimmed the Guid to 5 charecters but the issue is still not resolved.

Moreover the data which is stored in Server.Id for me MachineName + A 4 digit numeric from begining, The property Server name from the above code was not being place in there.

Any thoughts will be helpful.

Thanks!

6

Please try also to replace the type of Hangfire.Server.LastHeartbeat column with datetime2.

7

Just in case anyone else keeps having weird issues, it seems there is a query that Hangfire runs that seems to make the value it is inserting into the ID column to long.

exec sp_executesql N';merge [HangFire].Server with (holdlock) as Target using (VALUES (@id, @data, @heartbeat)) as Source (Id, Data, Heartbeat) on Target.Id = Source.Id when matched then update set Data = Source.Data, LastHeartbeat = Source.Heartbeat when not matched then insert (Id, Data, LastHeartbeat) values (Source.Id, Source.Data, Source.Heartbeat);',N'@id nvarchar(4000),@data nvarchar(4000),@heartbeat datetime',@id=N'dev webjob worker - fl-mevans-dev.618beba4-0728-421e-869a-e5e9844732c1:19784:d5a98176-bf89-46fa-9d44-bfe41defb757',@data=N'{"WorkerCount":20,"Queues":["intercom","critical","default"],"StartedAt":"2016-08-15T17:48:37.5089732Z"}',@heartbeat='2016-08-15 17:48:37.573'

I ended up trimming the GUID i was creating.

var workerName = $"{ConfigurationManager.AppSettings.Get("EnvName")} - WORKER - {Environment.MachineName}.{Guid.NewGuid().ToString().Substring(0,20)}";