November 22, 2010 at 7:11 am
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
November 22, 2010 at 7:41 am
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?
November 22, 2010 at 7:54 am
@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
November 22, 2010 at 7:55 am
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
November 22, 2010 at 7:57 am
@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?
November 22, 2010 at 8:01 am
Standard template should capture it. I tried it here
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
November 22, 2010 at 8:03 am
Okay. Thanks I will try run another trace and try the fn_trace_gettable
November 22, 2010 at 10:23 am
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" 😉
November 22, 2010 at 10:29 am
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.
November 22, 2010 at 5:17 pm
November 22, 2010 at 8:31 pm
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.
November 22, 2010 at 9:26 pm
If you know the time interval run sp_who2 every 2 minutes and capture the logs. It should give you some info.
November 23, 2010 at 3:29 am
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.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
November 23, 2010 at 3:41 am
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).
November 23, 2010 at 4:07 am
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" 😉
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply