Not that I think this will actually help... I think this more just demonstrates how generic the exception is, that so many 'solutions' are possible to fix one generic error message.

But, lessons learned for me: None of the troubleshooting and issues mentioned by commenters here helped me. GPT was no help. Well, sort of...it did mention my issue in passing. Temp Tables.

I have an edge case: I have an app that must build "payloads" of transactional data and pass them up through an MQTT broker. Queries must run every minute to build more payloads, and they must reach out to other databases for much of this through linked servers.

So, performance is a must. I can't have a very costly join to large production tables hinder me. So, I designed my app to replace any huge tables with joins to temp tables (which I build up with only the records needed), making the joins astronomically cheaper.

This caused a new problem: I'm using tempdb too much. Too many temp tables are being created, filled, queried, and dropped in rapid succession.

It took me 14 solid hours of research and troubleshooting... multiple iterations of recreating the database from script, repopulating data, testing, hitting the "kill state," swearing a bit, revising my create database script, rinse and repeat.

The real problem for me is both temp tables and linked servers have been known to cause this issue... along with corrupt indexes... and who knows what else. So I had no clue what could be causing this. I had to poke at everything.

• I checked Event Logs on the server when it occurred. No info there - there's a generic entry that just says to check sql logs.
• I checked SQL logs. No usable info - just said fatal exception and my session was killed. Oh, and here's a memory dump. Thanks.
• I performed all CheckDB steps described in this post. No errors found.
• I rebuilt all indexes. No effect.
• I checked the size of the log file. Rebuilt it, shrunk it, detached, and reattached. Didn't matter.
• I reduced compatibility hoping this was a newer issue. Nope. Every version does it.
• I rebuilt the database multiple ways. Did not work.

So, here's what I did to fix it:

• Made sure DB compatibility was then set to 160 (SQL Server 2022.)

  ALTER DATABASE [database_name] SET COMPATIBILITY_LEVEL = 160;

• Added more database MDF files to tempdb - 1 per core, making those files bigger than default with better ability to grow. This reduces resource contention as every core can now hit tempdb without stepping on each other

  ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev2', FILENAME = N'C:\TempDB\tempdb_mssql_2.mdf', SIZE = 1024MB, FILEGROWTH = 256MB);

  ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev3', FILENAME = N'C:\TempDB\tempdb_mssql_3.mdf', SIZE = 1024MB, FILEGROWTH = 256MB);

  ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev4', FILENAME = N'C:\TempDB\tempdb_mssql_4.mdf', SIZE = 1024MB, FILEGROWTH = 256MB);

• Made those files all the same size.

  ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev', SIZE = 1024MB);

  ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev2', SIZE = 1024MB);

  ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev3', SIZE = 1024MB);

  ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev4', SIZE = 1024MB);

• Turned on memory-optimized tables (introduced in SQL Server 2017).

  ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

  -- Restart the SQL Server instance for the change to take effect

• Created a maintenance job to run in a maintenance window to drop any temp tables older than 1 day.

• Placed a 1-second timer between the CREATE TABLE statement and the INSERT to handle any potential delay (GPT said there could be, I have no proof it's asynchronous, but it seemed to help.)

  WAITFOR DELAY '00:00:01'; -- Wait for 1 second

• I already designed my app to drop temp tables when finished, but that's important here too. Don't abandon temp tables, drop them when your script is finished.