June 20, 2012 at 7:30 am
Hi, I have a legacy SAP system running on SQL 2000. Once a week the SAP administrators perform a data copy from production to the Quality server via SAP, and last week this blew up because the transaction log had grown to 40GB and filled the drive.
At the start of the process we switch the database from FULL to SIMPLE recovery and at the end switch it back, and usually everything is fine. I got round the problem over the weekend by setting up a job that ran a manual checkpoint once every 15 minutes to clear the log.
This confuses me as I am not sure why the automatic checkpoints that should run do not empty the log, whilst the manual ones I run do?
Any ideas?
Thanks.
June 20, 2012 at 9:34 am
Automatic checkpoints do clear the log in simple recovery, same as manual ones.
Either the automatic ones weren't running for some reason (and that would require analysis as it's happening) or something else was holding the log active
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
June 20, 2012 at 11:28 am
Thanks for the feedback Gail, I figured that was the case but wasn't sure why automatic checkpoints wouldn't run. Nice to have some confirmation that I am not missing something obvious. 🙂
I will run some profiles during the next weeks copy.
Thanks again.
June 20, 2012 at 11:46 am
There is a traceflag to disable checkpoints, I wouldn't be too astonished to find that SAP enables it, it's an app that doesn't exactly play well with others. Check see if there are any traceflags enabled.
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
June 20, 2012 at 11:47 am
I have to ask, why the change in recovery model from FULL to SIMPLE and then back to FULL every week? This breaks the log chain meaning you can't restore to a point in time during week 2 using a full backup taken during week 1 if all full backups taken during week 2 were found to be corrupt.
June 20, 2012 at 12:11 pm
GilaMonster (6/20/2012)
There is a traceflag to disable checkpoints, I wouldn't be too astonished to find that SAP enables it, it's an app that doesn't exactly play well with others. Check see if there are any traceflags enabled.
Thanks Gail, didn't know about that, but there are no active traceflags enabled, I ran DBCC TRACESTATUS and it returned nothing.
Lynn Pettis (6/20/2012)
I have to ask, why the change in recovery model from FULL to SIMPLE and then back to FULL every week? This breaks the log chain meaning you can't restore to a point in time during week 2 using a full backup taken during week 1 if all full backups taken during week 2 were found to be corrupt.
Hi Lynn, good question, and one I asked when I took over DB responsibilities for this SAP system (It is the only one of 12 SAP landscapes that does this). The weekly copy I am having issues with essentially pushes all client data and schema changes from production to quality (where the problem is) over the weekend, they work on it during the week, and then it is wiped and restarted and the cycle continues. So there is no need to go back a couple of weeks. It is kept in FULL during the week so we can maintain log backups of any Transports they do Mon-Fri, come Friday however, they don't care.
I have had to change my way of thinking since I started working with SAP, it is an app that like to play by it's own rules, and don't get me started on the teams that use and develop in it. 🙂 As Gail said "it's an app that doesn't exactly play well with others".
Thanks for the replies. When I found out what is happening I will post, as I have found a couple of other forums with similar threads that had no solution.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply