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?

3
  • 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, 2020 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, 2020 at 15:16
  • Ahh ok. I thought you meant the truncation error which also appears to be a bug. I guess I'll report them both and see what happens. Aug 31, 2020 at 18:34

1 Answer 1

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

2
  • Where shoud you paste the code modified. I just get this error when pasting it in the second CMD window: Msg 208, Level 16, State 6, Procedure sp_MSdetect_nonlogged_shutdown, Line 1 [Batch Start Line 7] Invalid object name 'sys.sp_MSdetect_nonlogged_shutdown'.
    – Torgeir
    Feb 7, 2023 at 9:17
  • @Torgeir which version are you using?
    – Rodrigo
    Jun 14, 2023 at 0:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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