how to shrink Transaction log backups

[former_member203029]

Hi all,

I need tot shrink my database transaction log backups through SQL server management studio .please let me know how to do it.

[Former Member]

Check out the following article this will show you step by step method to shrink transaction log file : http://www.sqlserverlogexplorer.com/shrink-transaction-file/

[luisdarui]

Hi Kaushik,

While truncating and shrinking the transaction log file in SQL Server may be solution for a disk space shortage, you shouldn't do it on a SAP system.

Why?

Because each time any database transaction is performed, the transaction log will start growing (if you have autogrow on).

Database file growing over the file system = I/O expensive operation = more time to complete the transactions.

You've also asked what is the difference between the FULL and SIMPLE recovery mode of SQL Server.

The SIMPLE recovery model don't allow log backups and it rewrites the log area and you can't use log shipping, Point in time recovery. If you lose your database your last point of recovery will be the latest full backup.

If you truncate your log file, you're just clearing the data contained there, while shrinking will diminish the size of the log file.

You should never change the Recovery model of a SAP System. If you somehow lose your database you can rely on your good backups to bring it back to its most recent state.

[former_member203029]

Hi Cristina,

Yes, the .LDF files . The frequency of the log backups happen for every 20 min.I'm planing to perform as show in the below link.
Shrink OPSROOT_Log.LDF file [Q13362]</title>

[former_member206857]

Change the recovery mode from full to simple.

Backup your log file.

truncate it

Then like others have said, via the management sql console, right click the dataset, choose shrink. watch what file you select, if you don't change it, you could shrink the datafiles.

Simple as that...then set your recovery mode back to full

P.S Sap best practices advise against this,.....but its real world!

[former_member203029]

Hi Joshua

• What is the difference between full and simple mode recovery.
• What happens when I perform truncate

regards,

Kaushik G

[Sriram2009]

Hi Kaushik

You can search the MS SQL DB shrink in Google you can find so many links & some time you may get it youtube video,

You can refer the Google search link

https://www.google.com/webhp?sourceid=chrome-instant&rlz=1C1WLXB_enOM586OM586&ion=1&espv=2&ie=UTF-8#...

BR

SS

[former_member229431]

you mean to shrink your database transaction log ? the  .LDF file ?? in database > right click > properties > Files

Transaction log backup depends on the transaction log size and this depends on the write activity to the database, the frequency of the log backups and how long are the transaction expected to be open.