Error in Client copy

[Former Member]

Hi Team,

We got a problem during client import because of the transaction log being full.We observe that the transaction log space in initially at 95 % free but when we start the import job its keep on growing drastically and reaching its maximum available size before the import completes.

Is there any way to switch off the transaction log temporarily during the import job ? we are running on SQL 2005 , ECC 6.0.

I got following command from some forum ( sp_dboption QAS, 'trunc. log on chkpt', false )

(sp_dboption QAS, 'trunc. log on chkpt', true* )*

which says these SQL queries would switch off the changes recording on to transaction log. i dont know whether this would work on SQL 2005. Please let me know the way to fulfill my requirement.

Any quick replies would much appriciated. Please help me to fix this issue.

Thanks & Regards,

Vinod

[Former Member · Accepted solution]

Hello Vinod,

You can NEVER switch OFF the logging in SQL Server. Even a SIMPLE recovery model will NOT help if there is a single long running transaction and if there is not enough space for the log to grow. The truncation happens only on check point and that also for the inactive part of the log which is generally NOT the case in Client Copy situations.

In a nutshell, if you want a better performance and no hangups during your activity, then better keep enough space for the log file to grow (a drastic growth of log during such activities is not surprising). How much space would you need for the log file can be calculated by your last experiences during such activity (or check from R3 level: db02->db space history (go for logs)).

Hope this helps you.

Regards

[Former Member · Accepted solution]

Hi Topquark,

Thanks for the information.

what you said was true..simple recovery model did not help us to switch off the logging in SQL server 2005. We do not have any past experince as we are performing the client copy activity first for our client. We have around 17.5 GB free space available at file system for transaction log directory.

we have autogrowth enabled and set to 50MB. Production client export job was sucessfull( it is aroung 5.4 GB export copy) when we start the import job intially it is 98 % free however it was running fine for some after that it started increasing and occupying all free space before import job completes.Hence job fails due to insufficient free space.

In normal circumstances log file growth was normal. I have exported the histroy of log space couldn't find much info.

We have tried following options to fix this issue but all are went with no sucess.

1) tried with changing database recovery mdel to 'Simple'

2) Shrink the transaction log file and provided 17.5 GB free space.

3) Created addditional transactional log in other disk drive.

Please let us know what do more on this?

Thanks & Regards,

Vinod

[Former Member · Accepted solution]

well Vinod,

there is one more thing you can do,

while you start client copy,keep shrinking the log until the client copy completes

Rohit

[Former Member]

Thanks much for all your time who has helped me in fixing this problem.

[Former Member]

Hey I have the same problem.

How did you finally solved it?

[Former Member]

Hi Maria,

Please try below things.

1)Try with changing database recovery model to 'Simple'.

2) Schedule a transactional backup to run for every 5 mins or 10 mins initervel ( It's based on how fast your trasaction log file growing).

3) keep shrinking the log until the client copy completes.

Refer note: 421644 & 363018 which talks more about this problem.

Thanks,

Vinod

[Former Member]

When you are taking backup of your transaction log backup make sure you try with truncate_only option and keep on shrinking the transaction log fine to its minimum size while performing client copy.

Please refer the notes mentioned in my earlier post...those notes would helps more in fixing the issue.

Thanks & Regards,

Vinod

[Former Member]

Thanks your reply was very helpful.!!

[Former Member]

Dear ,

Try this if your log is full during client copy

1. shedule trancation log backup every 1/2 hour

2. try system copy

Regards

Pritpal

[Former Member]

Hi

Check this may help you [Link|https://docushare.xerox.com/doug/dsweb/Get/Document-5079/How%20to%20stop%20the%20transaction%20log%20of%20a%20SQL%20Server%20database%20from...pdf]

Regards

uday

[Former Member]

yes you can

change the recovery model of SQL server to simple and this will not allow transactional logs to grow

Open SQL server studio,connec to database,right click on it and then properties,then go to options and change the recovery model to simple

Rohit