Why is my log truncating?

Question

Why is my log truncating?

paul-644917

Grasshopper

Points: 22
More actions
August 11, 2008 at 6:59 pm

#190927

Every Sunday night, about an hour after a full backup, my production database sees its transaction log truncated for no reason that I can figure out. There are no backup jobs with parameters that would result in truncation.
The truncation occurs after a DBCC CHECKDB is run and before a stored procedure that performs internal maintenance. Since it happens consistently between these two events I figure it has to be somehow related to either the CHECKDB or the SP. But why would DBCC CHECKDB trigger a truncation? And there are no steps in the SP that would truncate the log, either (it's an indexing job)..
The question is: How can I determine what's telling SQL Server to truncate the log on this database?

Viewing 7 posts - 1 through 7 (of 7 total)

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

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 1

Is there a process that changes the recovery model, say from full recovery to simple and back again?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Steve Jones - SSC Editor SSC Guru Points: 739116 More actions · Answer 2

Run a trace.

The only things that truncate a log file are a checkpoint if you are in simple mode, or the backup log command.

CHECKDB has nothing to do with it.

Johan Bijnens SSC Guru Points: 135582 More actions · Answer 3

paul (8/11/2008)
...
The question is: How can I determine what's telling SQL Server to truncate the log on this database?

- run a trace to see what's going on.

(you can schedule a "start trace"-job just before your backup,... sequence starts)

You can script a trace statement using sqlprofiler.

- Start a trace with all trace events you want to monitor.

- stop that trace

- file / Script trace .... et voila ... there's your start trace script.

You'll have to modifile the file location , the rollover size and the end-time in the sqlscript !

Create a job to execute this start trace script and schedule it right before your maintenace window.

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 and code to get the best help

- 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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

Are you perhaps switching to simple recovery mode to do the index rebuilds?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

paul-644917 Grasshopper Points: 22 More actions · Answer 5

Problem solved. I tore apart the 500-line SP that was involved in the weekend job and discovered several places where it launched a "DUMP TRANSACTION WITH NOLOG" exec.

Now I have to start a 300-Gb database over on log shipping from scratch.. but at least I know what happened :crying:

Johan Bijnens SSC Guru Points: 135582 More actions · Answer 6

Glad that's been solved.

another little remark:

(from BOL sql2000)

The DUMP statement is included in SQL Server version 2000 for backward compatibility. It is recommended that the BACKUP statement be used instead of the DUMP statement. In a future version of SQL Server, DUMP will not be supported.