Sql-server – When a transaction truncate a global temporary table, is that logged in transaction log or error log

Based on your 2nd code snippet in the question, if the table already exists by the time you get to the sp_executesql, then you didn't need a global temporary table (##table) to begin with: a local temporary table (#table) would work just fine. Local temporary tables are available to sub-processes, and any changes made to them (data and/or schema) are available to all levels, up to the top level where it was created.

If that code snippet is actually inaccurate, and we interpret the following:

I use dynamic SQL in a stored procedure and must use a global temp table so the temp table and its data is available outside the instance when sp_executesql happens.

to mean that the temporary table is created within Dynamic SQL at some point AND needs to be available to other Dynamic SQL later on, then you still don't need a global temporary table: just create the local temporary table in the main process scope, prior to any Dynamic SQL, and just use the Dynamic SQL to insert into it.

The following is the code posted in the question, modified slightly to be working code, AND working with a local (not global) temporary table:

IF (OBJECT_ID(N'tempdb..#BOM') IS NOT NULL)
BEGIN
  DROP TABLE #BOM;
END;

CREATE TABLE #BOM(ObjectID INT, ObjectName sysname, ParamField VARCHAR(3));
DECLARE @TBL AS TABLE (ObjectName sysname NOT NULL, ColumnName sysname NULL);
DECLARE @SqlQuery NVARCHAR(MAX),
        @Param NVARCHAR(100);

--SET @Param = 'v';

INSERT INTO #BOM (ObjectID, ObjectName, ParamField)
  SELECT [object_id], [name], [type]
  FROM   [msdb].[sys].[objects] so

SET @SqlQuery = 'SELECT tmp.ObjectName, sc.[name]
                 FROM #BOM tmp
                 LEFT JOIN msdb.sys.columns sc
                        ON sc.[object_id] = tmp.ObjectID
                 WHERE 1=1 '; --build sql query

IF (@Param IS NOT NULL)
BEGIN
  SET @SqlQuery = @SqlQuery + ' AND tmp.ParamField = @InnerParam'; --add to sql query
END;

INSERT INTO @TBL (ObjectName, ColumnName) --insert records into table
  EXECUTE sp_Executesql @SqlQuery,
                        N'@InnerParam NVARCHAR(100)',
                        @InnerParam = @Param; --from final sql query

SELECT * FROM @TBL;

In either case, you don't need to create a real table that enforces process separation via @@SPID or a GUID. That is just extra junk that needs to be cleaned up whereas the local temporary table is inherently "thread safe" and is automatically cleaned up for you.

You need to ensure you are running the query in the context of the correct database.

Since system views such as sys.objects are context sensitive, they only return rows related to the current database.

For instance, running this code in master would only show tables where the PartitionId exists in master, and those table names would likely be incorrect.

Assuming your database name is OTIS you could work around this by adding the database name into your join clause, such as JOIN OTIS.sys.partitions and JOIN OTIS.sys.objects.

Since you've hard-coded the name of the log file itself, something like this should work more reliably:

SELECT [Transaction ID], 
    [Current LSN], 
    PartitionId, 
    sp.object_id,   
    so.[name], 
    [Operation],  
    [Context],
    [AllocUnitName],
    [Begin Time],
    [End Time],
    [Transaction SID],
    [Num Elements] ,
    [RowLog Contents 0],
    [RowLog Contents 1],
    [RowLog Contents 2],
    [RowLog Contents 3]
 FROM fn_dump_dblog(NULL,NULL,'DISK',1
    ,'D:\Users\Chad\SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\OTIS_LogBackup_2016-02-02_13-36-21.bak'
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL) as logF
    LEFT OUTER JOIN OTIS.sys.partitions sp ON sp.partition_id = logF.PartitionId 
    LEFT OUTER JOIN OTIS.sys.objects so on so.object_id = sp.object_id
WHERE
    [Transaction ID] = '0000:00d15747';

Alternately, you could simply place USE OTIS; prior to your select statement, so it executes first.