How to shrink a MS SQL Server 2005 Transaction LOG file

Please refer to these article, also would suggest to backup your database / log file before running shrink command.

USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

Select allOpen in new window

http://support.microsoft.com/kb/907511

http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/

You can shrink the log as above only after SQL has re-synchronized the replicated dbs.  Otherwise, SQL "knows" it needs the retain the data in the logs to replicate it to the other dbs.

Thanks pateljitu.
I did this skript:
Use LeoAlpha
GO
DBCC SHRINKFILE(LeoAlpha_Log,1)
BACKUP LOG LeoAlpha WITH TRUNCATE_ONLY
DBCC SHRINKFILE(LeoAlpha_Log,1)
GO


This is the result MS SQL Server Management Studio:


-   Dbld      Field CurrentSize MinimumSize UsedPages Estimated Pages
1     5           2       4644920       63                     4644920       56

-   Dbld      Field CurrentSize MinimumSize UsedPages Estimated Pages
1     5           2       4644920       63                     4644920       56

Query executed successfully 00:09:48 2 rows

The file size of LeoAlpha_log.ldf is unchanged: 40.875.328 KB


Hi ScottPletcher

that's exactly the point why the above does not work at all. The replication monitor shows that this server is out of synch since a long time. My customer has redirected all clients from this server to one of the other two. So it seems best to me to remove this server from the peer-to-peer replication. How do I do this without risking to destroy the replication between the other two servers (which runs smoothly)?

Sincerly,
Josef

many thanks
Josef