How to find out what is truncating a database transaction log

Question

How to find out what is truncating a database transaction log

Viewing 5 posts - 16 through 19 (of 19 total)

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

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

Check for any jobs setup to rebuild/reorganize your indexes - that is generally where someone will add code to truncate the log (e.g. BACKUP LOG ... WITH TRUNCATE_ONLY).

Also look for any archive/purge jobs being run - these may be scheduled outside of SQL Server either in scheduled tasks or from the application itself.

And finally, check for third-party backup utilities that could be backing up the system. Some system backup utilities have hooks into SQL Server and could be backing up your databases.

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

Patti Johnson SSCertifiable Points: 7054 More actions · Answer 2

Thanks Jeffrey. I'm looking into the hooks from 3rd party software. It's like a hunt and peck because the people who set this up have left and others have taken over and they don't know. :w00t: That's why I was thinking a sql trace.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

The obvious thing is to review all scheduled activity that occurs around the times of concern. I recommend sqljobvis to view job activity as a gantt chart - amazing little freebie tool.

Just in case you don't know, having a truncate log action occur voids the use of all future tlog backups for recovery!!! I just this week had a client call me in a panic because they did an "oh sh-t" update of a table without a WHERE clause. Last week I was onsite with them for a system review and told them that their truncate_only action was the SINGLE MOST IMPORTANT FLAW they had and it was to be addressed before they left the office that day. So they wound up blowing up their single most important table and losing most of a day's activity because they had no recourse but to pull the table back from the 234am that morning backup. They are now having a nightmare dealing with this with their customers. It is conceivable the company will not survive this action.

Moving on, PLEASE go to ola.hallengren.com and get and learn to use the great free scripts there for all maintenance activities. Wonderful documentation and flexibility! Maintenance plans are crap.

Using profiler it should have taken you only one night to determine the application that is firing the command you are concerned about. Trace to file, and use the function others have pointed you to. But note that finding out it is sql agent or some other app won't point you to the job, etc. you will still have to dig.

Best of luck with it!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Patti Johnson SSCertifiable Points: 7054 More actions · Answer 4

Thanks Kevin, that's why I am looking for the culprit. I know it messes up recovery if it's needed. I'm not having my name be tarnished on account of someone else's error. I'm digging and thanks for the site. Going there. Like I said in a previous post, I ran sql profiler but I couldn't find it easily. I hope to now, just haven't been able to get back since the post. Had to do a sql 2008 r2 install.

Kevin, I will let you all know what I find out here shortly within the next day or two.

Randy Doub SSCertifiable Points: 5322 More actions · Answer 5

I didn't see Alerts mentioned above. Also check to see if any Alerts were coded way back when and are now being invoked.