BIG PROBLEM transaction log does not shrink after full database backup

[Former Member]

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

[kai_gebhardt]

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

[former_member185954]

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

[former_member185954]

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