Skip to Content
avatar image
Former Member

BIG PROBLEM transaction log does not shrink after full database backup

Hello

I have a BIG PROBLEM i.e.MS sql transaction log does not shrink after full database backup.

I am only few days before going live . the server is running half year.

I would like to switch off transaction log on development and shrink with full database copy or transaction log backup(or anyhow)

I am performing backup with Enterprise manager.

I also restarted server few days ago. but still the transaction log does not shrink after database backup. I tried also only transaction log backup.

Please help me

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • avatar image
    Former Member
    Dec 25, 2006 at 01:26 AM

    Hi,

    There are probably some uncommited transactions pending in your transaction log.

    One hint is terminated updates, check SM13 for any terminated updates.

    The idea is to backup your log and truncate it manually via some command like :

    BACKUP LOG databasename WITH TRUNCATE_ONLY

    Please check with the SQL server documentation first.

    Please check the below links:

    http://support.microsoft.com/kb/256650/EN-US/

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318

    Hope that helps,

    Siddhesh

    Message was edited by:

    Siddhesh Ghag

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 28, 2006 at 11:04 AM

    Hi Tina,

    I do it always this way (without garantuees :-):

    • Stop the R/3 and all Services

    • Then via SQL Query:

    BACKUP LOG [SOL] TO DISK = 'D:\Backup\sol_tlog.bak' WITH STATS = 10

    GO

    USE [SOL]

    GO

    DBCC SHRINKFILE ('SOLLOG1' , 2309)

    GO

    -> The Logfile shrinks to 2.3 GB.

    Shrinking via Enterprise Manager doesn't work very often and when it works, only, if I did a backup of the transaction log and shut down the R/3 before that.

    Best regards,

    Kai

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Tina,

      Kai is correct, that's what i was looking for too.. i haven't used MSSQL for 3 straight years.. so i couldn't recollect.

      Kai has given the correct procedure, it should work!

      Regards,

      Siddhesh