When a transaction truncate a global temporary table, is that logged in transaction log or error log?

Question

When a transaction truncate a global temporary table, is that logged in transaction log or error log ? I have inherited bunch of old SQL 2012 servers from my senior dbas who left the organisation. I'm stuck with queries from report users which processes are truncating certain business critical tables.

If it is logged in transaction log, then could the SID,login details be retrieved? Suspect, there are multiple agent job using those ##table.

Answer 1

You mean a ##temp table? It isn't logged in error log. But it must be logged in transaction log since you can rollback a truncate table command.

Answer 2

This query should at least get you closer to identifying your culprits. As I mentioned in the comments, the only way to be sure to catch everything is to use an Extended Event, which is not something I'm good enough at to teach you to use through an answer here.

However, if you just want to identify which stored procedures and/or which agent jobs mention that table, you can use this to get you started. Plug in what you are looking for and it will find any objects in any database that reference it and any agent jobs that mention it. You will need to use successive searches if (for example) the truncate table is in a stored procedure, then you will also need to search for that stored procedure name to find any stored procedures/agent jobs that call it.

SET NOCOUNT ON 

DECLARE @SearchString VARCHAR(200) = '##TempName'

DECLARE @SQLCMD NVARCHAR(4000) = N' 
    SELECT ''?'' AS DBName
        , SCHEMA_NAME(O.schema_id) + ''.'' + O.[name] AS ObjectName 
    FROM sys.sql_modules AS M 
        INNER JOIN sys.objects AS O ON O.object_id = M.object_id 
    WHERE M.[definition] LIKE ' + QUOTENAME('%' + @SearchString + '%', '''') 

PRINT '/** Query if you want to run manually **/'
PRINT @SQLCmd 

--Search all procedures, functions, etc. in all databases.
EXEC sp_MSforeachdb @SQLCmd 

--Search all Agent Jobs.
SELECT J.job_id
    , J.[name] AS JobName
    , JS.step_id
    , JS.step_name 
FROM msdb.dbo.sysjobs AS J
    INNER JOIN msdb.dbo.sysjobsteps AS JS ON JS.job_id = J.job_id
WHERE JS.command LIKE '%' + @SearchString + '%'