October 3, 2017 at 2:46 am
Guys,
Something is deleting records from an audit table. The table has an auto-incrementing key and there are records missing so either they are being deleted or there is an error in creation and the transaction is rolling back but we don't know what process is failing
The transaction logs get copied every 6 hours so I have them off line.
The command to copy the logs is
BACKUP LOG [SQLActiveH] TO DISK = N'F:\SQLBackups\DatabaseName_Tlog_0100.trn' WITH NOFORMAT, INIT, NAME = N'DatabaseName-Transaction Log Backup 0100', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
We believe that the table gets populated based on a batch job, but no one knows where it is.
I am looking to find references to the particular table and to see if I can identify what time it is running, what sproc is being run to update the table (system vendor uses sprocs for CRUD operations most of the time) and whether the transaction is rolling back or if something else is deleting the records.
There doesn't appear to be any reference to the records in fn_dblog(null,null) but that may be because we have run the log shipping since the transaction occurred.
Is there any way to sensibly search these logs (4GB) without restoring a copy of the database and replaying the logs.
October 3, 2017 at 5:22 am
fn_dump_dblog will be the table valued fuction to go for
select *
from fn_dump_dblog (NULL,NULL, N'DISK', 1, <PATH TO TRN>, DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT)
From that you can see the transactions and see if it yields any results for you.
October 3, 2017 at 1:28 pm
Thanks Anthony,
We found out what the problem was. A 3rd party system was trying to insert records into the table and the application vendors have changed the nullability of one of the fields so the insert was failing which increments the auto-increment field so no need to interrogate the logs, but it is good to have that in the knowledge base.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply