How to shrink the index .ldf file

Question

Post reply

How to shrink the index .ldf file

MAK-1128556

SSCarpal Tunnel

Points: 4594
More actions
April 5, 2010 at 7:20 am

#143906

when i run my reindex job my log file grow from 10 gb to 135gb and I am using my database for log shipping and I do not want to bark log shipping
also when I ran my create reindex job my tran log file grow from 20MB to 40GB how can i avoid this issue, i don't want to increase tran log backup file that big.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

1. Only rebuild or reorganize indexes that absolutely require it

2. Spread the bare-minimum of rebuilds and reorganizations out over time

3. Compress the log backups before shipping them across the link

4. Consider partitioning large indexes (Enterprise only) and rebuilding at partition level

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

MAK-1128556 SSCarpal Tunnel Points: 4594 More actions · Answer 2

I try to compress the log file but it is not copressing because database is in FULL mode

Paul White SSC Guru Points: 150467 More actions · Answer 3

MAK-1128556 (4/5/2010)
I try to compress the log file but it is not copressing because database is in FULL mode

You can't compress the active log file (unless you are using SQL Server 2008 Enterprise Edition, which has this built-in). It is the log backups that are shipped, and it is these you would need to compress.

Sadly, the basic log-shipping support in SSMS does not include a way to do this easily, so you have to write a manual log shipping process.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

MAK-1128556 SSCarpal Tunnel Points: 4594 More actions · Answer 4

do you have any sample scrip to compress the active log when no one useing the database and do you have sample script to shrink the tran logbackup

Paul White SSC Guru Points: 150467 More actions · Answer 5

MAK-1128556 (4/5/2010)
do you have any sample scrip to compress the active log

The log file (normally *.LDF) is a SQL Server file and cannot be compressed by SQL Server 2005. Do not attempt to compress the file yourself - you will break your database.

do you have sample script to shrink the tran logbackup

The following articles should give you the idea:

http://www.sqlservercentral.com/articles/Administration/customlogshipping/1201/

http://www.sqlservercentral.com/articles/Log+Shipping/62676/

http://www.sqlservercentral.com/scripts/Backup+/+Restore/31125/

There are also some commercial products that support this:

http://www.hyperbac.com/sqlserver/logshipping.asp

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi