MSSQL Transaction Log full

[Former Member]

Hi,

whenever I am ruuning Client copy it got stuck because of Trnsactional log getting full.Then I have to take backup of Transactional log & then again start client copy.

For a client copy it takes around 48 hour & I am facing this problem 10 times.

Please let me know if there can be some solution to avoid this situation.Aslo please suggest procedure to avoid it.

we are using MSSQL 2005 databsae & Windows 2003 server.

Cheers

Santosh

[Former Member · Accepted solution]

Hi,

do you run transaction log backups on a regular basis (job driven - like every 15 minutes or so)?. That is what you should do.

Sven

[Former Member · Accepted solution]

Dear Swen,

we are doing Transactional log backup everyday one time.but if I will schedule a client copy I need to take this backup every hour.

size of Trnsactional backup is 10 GB.Please brief me about your thoughts so i can apply it here.

Cheers

Santosh

[Former Member · Accepted solution]

Hi Santosh,

transaction log backups should be performed more often than once a day. You need them if you want to recover point in time after a hardware crash or something like that. Please have a look at note 421644. We run transaction log backups every few minutes so we won't have to worry about the log running full. Just create a job in the Management Studio or use transaction DB13.

Sven

[Former Member · Accepted solution]

Yes, I would highly recommend running transaction log backups every 15 minutes at least. Especiallly if you are creating 10GB of transaction data every day.

This client copy issue may very well be a lifesaver for you in the future.

Re-evaluate your backup/recovery scenario.

Here's a little bit of basic info from my blog:

http://joeybear23.blogspot.com/2008/04/sap-backuprecovery-planning.html

[Former Member · Accepted solution]

Hi,

By giving the option to increase the transaction log by some percent only during Client copy should solve your issue. Also this would be a probable solution if this Client Copy activity is a biweekly activity or a monthly activity. If it is a weekly activity i would also recommend backup every 15-30 minutes.

Once the client copy is done you can again revert back to the original option.