Table is automatically truncated on SQL Server

Question

I have a really strange problem on my SQL Server.

Every night 2 tables, that I have recently created, are being automatically truncated...

I am quite sure, that it is truncate, as my ON DELETE Trigger does not log any delete transactions. Additionally, using some logging procedures, I found out, that this happens between 01:50 and 01:52 at night. So I checked the scheduled Jobs on the server and did not find anything. I have this problem only on our production server. That is why it is very critical. On the cloned test server everything works fine.

I have checked transaction log entries (fn_dblog), but didnt find any truncate logs there.

I would appreciate any help or hints that will help me to find out process/job/user who truncates the table.

Thanks

Answer 1

Check with below query,

declare @var as varchar(max)='tblname' EXEC sp_depends @objname =@var;

it will return number of stored procedure name which are using your table and try search for any truncate query if you have wrote by mistake.

Answer 2

From personal experience of this, as a first step I would look to determine whether this is occurring due to a DROP statement or a TRUNCATE statement. To provide a possible answer, using SSMS, right click the DB name in Object Explorer, mouse over Reports >> Standard Reports and click Schema Changes History.

This will open up a simple report with the object name and type columns. Find the name of the table(s), click the + sign to expand, and it will provide you history of what has happened at the object level for that table. If you find the DROP statement in there, then at least you know what you are hunting for, likewise if there is no DROP statement, you are likely looking for a TRUNCATE.

Answer 3

I don't know if can help you to resolve the question. I often encounter the following situations.

Look at this example:

declare @t varchar(5)
    set @t='123456'
    select @t as output
    output:12345

Answer 4

Thanks a lot to everyone who has helped!

I've found out the reason of truncating. It was an external application.

So if you experience the same problem, my hint is to check your applications that could access the data.