SCCL why do logs still write with sql (2005) set in simple mode????

[MPGraziano]

I ran sccl between two clients in our QAS system. While monitoring the db file growth during the copy, I saw that the log file started to grow while the copy was running

how I ran the client copy

1. in background

2. assigned 5 parallel

3. profile = sap_all

the log file grew from 1/4 Gig to 153G...

are these ghost logs? are they really there?

After the copy finally completed (about 4 hrs, even though the test ran about 2hrs), I went back to sql management studio and shrunk the logs back again, without issue

BUT, my understanding from setting sql (2005) in simple mode, the logs should NOT be recording?

why is this happening, It's driving me nuts!

Thanks,

Maria

[Former Member · Accepted solution]

Hello Maria,

wel great to know that your client copy got completed.Logs get generated even in smple recovery model

But,In this approach, SQL Server maintains only a minimal amount of information in the transaction log. SQL Server truncates the transaction log each time the database reaches a transaction checkpoint, leaving no log entries for disaster recovery purposes.

I hope this helps

Rohit

[MPGraziano · Accepted solution]

Thanks Rohit,

this was a local client copy, however , we found that the database drive seemed to have no more space left after sccl finished.

the log file was at 153G. If what you say is true, and we have the db set in simple mode, we should not see any logs at all. In fact we have done upgrades for sql db and when the db is set in simple mode the transaction logs DO NOT accumulate

I am curious as to how SAP handles the locks and updates, and release the space?

Maria

[Former Member · Accepted solution]

Hello Maria,

This is from Booksonline:

If the database is using the simple recovery model, an automatic checkpoint is generated whenever the number of log records reaches the lesser of these two values:

1.The log becomes 70 percent full.

2.The number of log records reaches the number SQL Server estimates it can process during the time specified in the recovery interval option.

Long-Running Transactions

The active portion of the log must include every part of all uncommitted transactions. An application that starts a transaction and does not commit it or roll it back prevents SQL Server from advancing the MinLSN. This can cause two types of problems:

If the system is shut down after the transaction has performed many uncommitted modifications, the recovery phase of the subsequent restart can take considerably longer than the amount of time specified in the recovery interval option.

A database running in SIMPLE recovery does not keep committed transactions around, but if a transaction made a large number of changes, the transaction log will still need to accommodate the changes made by the transaction.

The log may grow very large because the log cannot be truncated past the MinLSN. This happens even if the database is using the simple recovery model, in which the transaction log is normally truncated on each automatic checkpoint.

Replication Transactions

The active portion of the log must also contain all transactions marked for replication, but that have not yet been replicated to a subscriber. If these transactions are not replicated in a timely manner, they can also prevent truncation of the log.

I hope this helps

Rohit

[MPGraziano · Accepted solution]

what is MinLSN?

And how does this translate into what SAP is doing during SCCL?

[MPGraziano · Accepted solution]

In other words why did my entire db drive fill up for the logs ... 153G worth?

[Former Member · Accepted solution]

Hello Maria,

In the simple recovery model, the transactions log entries are kept only for the purpose of database integrity and are not kept for database recovery purposes. Once the log entries are marked as inactive, that is once the associated data pages have been written to disk, the log entries can be discarded. In simple recovery mode, when a checkpoint operation runs, all inactive log records are removed from the transaction log and the space is made available for reuse.

In simple recovery, operations that qualify as bulk operations are minimally logged

This is the simplest recovery mode in terms of log management as the log manages itself. The downside of simple recovery is that because transaction log backups cannot be made, recovery of the database can only be done up until the latest full or differential database backup.

Now during the client copy,the transactions were not committed(they were all active entries) and so database didnt write that to disk and hence the size grew up to 153 G.The database frees up the space only when the transactions are committed.

Also refer to http://msdn.microsoft.com/en-us/library/ms188748.aspx

Rohit

[MPGraziano · Accepted solution]

Wow that is certainly inefficent, but I can see why SAP would do this... unless the client copy completes , then nothing changes in the target client?

Correct?

[Former Member · Accepted solution]

Yes.....

but that is the property of SQL server database and it is not at all inefficient...let me assure you that

Rohit

[MPGraziano · Accepted solution]

What I was referring to is that the client copy holds ALL transactions as active, until the end of the process, without committing and thus releasing the transactions.

Of course the nature of SQL gives us the option not to have logs with simple mode, where NO logs are written ,

but it is contradictary with the way SAP performs the client copy?

correct?

[Former Member · Accepted solution]

Hello Maria,

you seem to be right just that in simple mode transaction logs get generated but are written automatically to the disk after some time

so we dont face any issue of transaction logs getting full

But rest you seem to be Ok

Rohit

[MPGraziano · Accepted solution]

But, in my case it seemed to ONLY release the space AFTER the client copy process was completed.

which indicates that a checkpoint did not occur till it ALL finished

Which in essence, locked up that 153G until after the copy process ended.

In future and as the db continues to grow, what are my options here?

>. Add more space so that a client copy will end?

Are there additional settins in SAP or in SQL?

[Former Member · Accepted solution]

what I meant was that it automatically write to disk if the transactions commit

but if they dont commit,it will not write and you are absolutely right in that

I am sure that you dont need extra space for this,if they get filled database will automatically truncate the log as database will automatically truncate log when it reaches 70% as I have previously mentioned also

Rohit

[MPGraziano · Accepted solution]

And the 70% should be the 70% of the MAXimum size set for the trans.log?

And this becomes a tricky excersize because we have to find how big to set that maximum at, so that the copy will commit, when it has had sufficient space to log and truncate at that 70%

ie. If I have the logs set at in simple recovery mode and

Enable autogrowth = yes

File growth

In Percent = 10

Max. File Size = 2,000,000 MB

[Former Member · Accepted solution]

Hello Maria,

whatever be the size of the transactional log,sql server will handle that situation

you just need to make sure that simple recovery model is set,SQL server will truncate logs if the transactional log size reach 70% of your defined size.So size doesnt matter at all

Just make sure simple recvery mode is set and just let your activities happen

Rohit

[Former Member]

Maria,

SQL Server transaction log file stores the details of all the modifications that you perform on your SQL Server database and the details of the transactions that performed each modification. During SCCL, system is copying the tables based on selected profile. As you had selected SAP_ALL it might have taken considerable space on the server.

Following link gives more information about this.

[http://support.microsoft.com/kb/873235]

Hope this helps.

Manoj chintawar

[Former Member]

Hi

Online Redo logs are recreated by the Control<SID>.SQL

Otherwise adjust appropriate traced control file from the sourse system.

Please check the following link :

http://help.sap.com/erp2005_ehp_04/helpdata/EN/69/c24c5b4ba111d189750000e8322d00/frameset.htm

I hope it helps you

Regards

Chen

[MPGraziano]

Chetan

The database is on SQL 2005 not Oracle,