0

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

Bebus
  • 15
  • 1
  • 11
  • check the permissions: are there many users allowed to perform that operation? what about the applications using these tables? does these applications have any scheduler? – Paolo Sep 09 '15 at 07:51
  • Which version of SQL-Server? Is there any kind of replication running? Just a silly question: Are you sure, that this is not the backwriting of a backup? Tables won't truncate just for fun... – Shnugo Sep 09 '15 at 08:13
  • 1
    Open a profiler during those hours capturing all Truncate commands. Or create a database trigger to capture all truncates as they occur and get the session context. – Amir Pelled Sep 09 '15 at 08:15
  • +1 for Amir. Use SQL profiler to capture all commands at that time. I am going to guess that it is an automatic deployment script dropping and recreating the table... What jobs run at that time? – Ben Sep 09 '15 at 11:09
  • @Shnugo There is no replication on the server. It is Sql Server 2008. Backup jobs are scheduled to run at other time. – Bebus Sep 09 '15 at 12:47
  • @Amir Thanks for the hint. I will activate profiler tonight and will check it tomorrow morning. As far as I know, you can not create on truncate trigger, as it is not a single line transaction (not a DML) – Bebus Sep 09 '15 at 12:48
  • @Ben There are no jobs, that run at that time – Bebus Sep 09 '15 at 12:51
  • @Paolo You were right. That was an application scheduler – Bebus Sep 10 '15 at 09:16

4 Answers4

1

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.

Tony
  • 74
  • 2
  • Thank you Tony, I have checked it up. So as I thought, it is a truncate statement. I didnt find any DROP history for these 2 tables. Anyway it is a useful hint – Bebus Sep 09 '15 at 12:52
0

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.

Irfan Shaikh
  • 90
  • 6
  • There is only one SP, but it does not have any truncate statements. It makes only one insert into this table, thats all – Bebus Sep 09 '15 at 12:54
  • check with your sql jobs or any inline query you have written...if this all not works change the table names :) – Irfan Shaikh Sep 10 '15 at 11:57
0

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.

Bebus
  • 15
  • 1
  • 11
-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
Toby Speight
  • 27,591
  • 48
  • 66
  • 103
user5316612
  • 1