0

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.

  • If you choose not to register, you'll be using a cookie-based account. To keep control of your question, be able to comment on answers, and accept an answer - you'll need to access the site from the same device and browser each time you visit, without clearing cookies. Most often it is easier to just register. – Paul White Aug 14 at 12:24
  • Maybe this post by Aaron Bertrand can help you with Extended events for temp tables sqlperformance.com/2014/05/t-sql-queries/… – Danielle Paquette-Harvey Aug 14 at 15:41
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.

| improve this answer | |
  • true, but more than likely the OP meant logged as in "can I find out what process did it"... which the answer is no, unless they setup an XE to try and catch it. – Jonathan Fite Aug 13 at 19:53
  • Yes, indeed. I should have been more observant to the intent. :-) Agreed, some tracing is most likely needed to capture who/what/when etc. I guess we could try some undocumented ways to get this from the tlog, but I don't think that transaction logging includes who performed the operation. – Tibor Karaszi Aug 14 at 8:44
0

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 + '%'
| improve this answer | |

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.