How to find out what is truncating a database transaction log

Question

How to find out what is truncating a database transaction log

Patti Johnson

SSCertifiable

Points: 7054
More actions
November 22, 2010 at 7:11 am

#149411

I am using SQL 2000. Sorry about that. We are planning to upgrade to 2008 but until then I'm here.
I have looked at all sql server jobs and maintenance plans and cannot find where a transaction log of a specific database is being truncated. I want to use sql profiler and run a trace to find where the truncate of the transaction log is being initiated. I tried once but I couldn't find the right info in the trace to help me. Does anyone have any ideas on how to select the right trace to find out this information. I look in the sql server logs and it appears to have runn successfully. I looked in the application event log and it appears as an error. There is no user associated with the truncate in the event log. User is N/A. Either way, I want to stop the transaction log from truncating. We have regular transaction log backups running and I don't want this job to cause us an issue if we have to recover the database.
Thank you.
Patti

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29422 More actions · Answer 1

Patricia Johnson (11/22/2010)
I am using SQL 2000. Sorry about that. We are planning to upgrade to 2008 but until then I'm here.
I have looked at all sql server jobs and maintenance plans and cannot find where a transaction log of a specific database is being truncated. I want to use sql profiler and run a trace to find where the truncate of the transaction log is being initiated. I tried once but I couldn't find the right info in the trace to help me. Does anyone have any ideas on how to select the right trace to find out this information. I look in the sql server logs and it appears to have runn successfully. I looked in the application event log and it appears as an error. There is no user associated with the truncate in the event log. User is N/A. Either way, I want to stop the transaction log from truncating. We have regular transaction log backups running and I don't want this job to cause us an issue if we have to recover the database.
Thank you.
Patti

Transaction log backups do truncate/clear the inactive portion of the log. Are the transaction log backups failing?

Pradeep Singh

Adiga One Orange Chip Points: 27284 More actions · Answer 2

@Patti,

Since you are running trace it should capture TRUNCATE_ONLY. Try using fn_trace_gettable, it makes reading trace files easy.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Patti Johnson SSCertifiable Points: 7054 More actions · Answer 3

No transaction log backups are failing. I run a transaction log backup every 15 mins for all the databases. This transaction log truncate appears to run around 1:15 and sometimes at 1:30am in the morning every night. Except today it ran at 8:30am. I don't get it.

18278 :

Database log truncated: Database: Insight_v50_0_151946156.

18265 :

Log backed up: Database: Insight_v50_0_151946156, creation date(time): 2007/05/21(19:49:49), first LSN: 3865618:28121:1, last LSN: 3865623:6647:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'T:\trn\Insight_v50_0_151946156\Insight_v50_0_151946156_tlog_201011220830.TRN'}).

To add to the puzzle, there is also a temp_db backup in the app event log and in the ms sql server log. This is nothing that I have configured yet I can't find where it is initiated. It has user N/A as well. It ran about 1 minute after the database log truncate of the insight database.

3041 :

BACKUP failed to complete the command backup log tempdb with no_log

Patti Johnson SSCertifiable Points: 7054 More actions · Answer 4

@pradeep-2 Adiga

Thanks for the info. Do I use the standard trace template or is there another template that is better for tracing a truncate?

Adiga One Orange Chip Points: 27284 More actions · Answer 5

Standard template should capture it. I tried it here

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Patti Johnson SSCertifiable Points: 7054 More actions · Answer 6

Okay. Thanks I will try run another trace and try the fn_trace_gettable

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

Patricia

check for a SQL Server agent job that executes against the Insight database and contains a step that manually truncates the log. I had this issue previously with another application database where a custom agent job used to run at 2:00am and one of the steps ran a stored procedure which truncated the log

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Patti Johnson SSCertifiable Points: 7054 More actions · Answer 8

Thanks. I thought about that as well and didn't have any additional steps in the sql agent jobs. The transaction log backups of all the databases including the insight database is run by the sql agent account. The jobs were created by a database maintenance plan. There is no database maintenance plan for the tempdb that is also have a no_log backup running.

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 9

What is your recovery interval setting.

check dbcc opentran if there is any open transactions that truncating.

Check select * from sys.jobs.

regards,

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

Leo.Miller SSChampion Points: 13051 More actions · Answer 10

Patricia Johnson (11/22/2010)
No transaction log backups are failing. I run a transaction log backup every 15 mins for all the databases. This transaction log truncate appears to run around 1:15 and sometimes at 1:30am in the morning every night. Except today it ran at 8:30am. I don't get it.

OK, so what else runs at about 1:15 to 1:30 a.m. and then suddenly ran late or longer than normal?

Also what time does your normal full backup run? Is it before or after the log is truncated, or at the same time?

Is it possible that there is functionality within the application that is doing this. If I recall correctly the Insight application that uses this atabase does do a few unusual things.

Have you checked that there isn't a Windows Scheduled job somewhere?

Cheers

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 11

If you know the time interval run sp_who2 every 2 minutes and capture the logs. It should give you some info.

OomBoom Hall of Fame Points: 3481 More actions · Answer 12

I would delete the current log backup maint plan and recreate. Do it from the server that is running the SQL. SSMS can sometimes give errors when you work with diff version than that of DB engine.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

ps. One Orange Chip Points: 29422 More actions · Answer 13

Just a thought. Does your application has some sort of db maintainence code written in it which truncates log if it is getting full?

btw, i ran profiler and captured the truncate log statement (pls see attachment).

Pradeep Singh

Perry Whittle SSC Guru Points: 234013 More actions · Answer 14

Patricia Johnson (11/22/2010)
Thanks. I thought about that as well and didn't have any additional steps in the sql agent jobs. The transaction log backups of all the databases including the insight database is run by the sql agent account. The jobs were created by a database maintenance plan. There is no database maintenance plan for the tempdb that is also have a no_log backup running.

Have you checked all agent jobs

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉