2

One of our replication jobs is failing on step 3, which runs sp_MSdetect_nonlogged_shutdown, returns a 'String or binary data would be truncated'. Which of course led us down the path of trying to find a problem in the databases. However we compared the two databases and they are identical. From what I can tell the error is actually a bug in sp_MSdetect_nonlogged_shutdown.

Eventually I checked the job again and step 2 was also failing and throwing a mini dump. When we looked at the dump it says:

the thread tried to read from or write to a virtual address for which it does not have the appropriate access

The id running the job is a SQL Id that is sysadmin, the step (2) is running under the agent service account. The service account has access to the snapshot directory. We have multiple other replication jobs using the same security setup that are working fine.

SQL Version:

Microsoft SQL Server 2016 (SP2-GDR) (KB4532097) - 13.0.5102.14 (X64) Dec 31 2019 22:39:35 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)

Anyone have any idea where I can find the permissions error? Or steps I can use to start narrowing it down?

6
  • the thread tried to read from or write to a virtual address for which it does not have the appropriate access looks to be a memory protection fault not a permissions issue.
    – Brian
    Aug 28 '20 at 20:40
  • It's a pretty consistent error and I would have thought a memory protection fault would be intermittent. Aug 28 '20 at 20:46
  • This is a bug and should be reported to Microsoft. Especially if it generating dumps. Aug 28 '20 at 20:53
  • The truncation error is a bug and I'm reporting it to MS but the rest of it isn't as far as I can tell. Aug 28 '20 at 21:09
  • What do you mean? The error "the thread tried to read from or write to a virtual address for which it does not have the appropriate access" is an unhandled exception that produces a dump and the application crashes. Looks like a bug to me. Aug 31 '20 at 15:16
1

While MS releases a patch (not sure if will ever do), this is a quick fix:

  1. Get and save the source code of this procedure

    [distribution].[sys].[sp_MSdetect_nonlogged_shutdown]

  2. Edit the source and find the variable @message, change its length to 4000. Then find the column message in the temp table #JobHistory and do the same

  3. Start SQL Server in Single mode from CMD in Administrative mode

    path_to_binary\sqlservr.exe -m"SQLCMD"

  4. Open another console window and start sqlcmd to connect to the instance

    sqlcmd -S . -U sa -P

  5. Put the catalog in read-write

    USE mssqlsystemresource GO

    alter database mssqlsystemresource set read_write GO

  6. Paste the code modified.

  7. Restore the catalog to read_only mode

    alter database mssqlsystemresource set read_only GO

  8. Shutdown the single mode instance

    SHUTDOWN

    GO

  9. Start the instance using the service console as usual

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.