January 16, 2013 at 12:22 pm
I've got a SQL Server 2000 system that has many databases in Full recovery with full, diff, and tlog backups. I'm seeing an unusual error that says "Database log truncated". It only shows as an error in the Application Event log. In the SQL Error log it doesn't show it as an error.
Here's the time sequence:
5AM Database log backup of db A
6AM Error: Database log truncated: Database: A
6:01AM Database differential changes backed up: Database: A
So my question is, how/why would the database log be truncated an hour after it has been backed up? I don't know if the Application Event log is erroneously reporting an error or if I have an actual problem.
Any help would be greatly appreciated!
January 16, 2013 at 12:44 pm
Check the differential backup process for the database. It may have a truncate log in it just before the differential backup. Not something I'd want being done.
January 16, 2013 at 12:48 pm
- If the time interval is near constant, just start a sqlprofiler trace to capture all the needed and analyse it afterward.
- how are the backups being created at your systems ? SQLAgent scheduled jobs ?
check all sqlagent jobs to they don't contain a "backup log with truncate only" statement (we've seen that at some of our systems and had to contact the corresponding dev teams to clarify and remove them )
select J.name, suser_sname(J.owner_sid) as Owner, J.enabled, JS.*
from msdb..sysjobs J
inner join msdb..sysjobsteps JS
on JS.job_id = J.job_id
where JS.command like '%backup%truncate%'
order by Owner, name
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 16, 2013 at 12:52 pm
Thank you both. I found the issue. Our backup procedure was using Backup log @Database with truncate_only, which truncates the transaction log. Immediately after that it made a differential backup but the truncate_only would invalidate the previous log backups. We'll be fixing this today... Oi Vay!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply