How to shrink a MS SQL Server 2005 Transaction LOG file

I have a MS SQL Server 2005 Database with Peer-To-Peer Replication (on three severs).
One of this servers was down for some days and therefor the Log file is exploded to 40 GB size. Analysis shows that "transaction" is the cause for this unexpected growth of the log file (LeoAlpha_Log).
The model is SIMPLE.
How can I shrink the log file. I do not need the information in the log file?
On the hosting disk is only 7 GB space left.
joschrammAsked:
Who is Participating?
 
pateljituCommented:
0
 
pateljituCommented:
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

Open in new window


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

http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/
0
 
Scott PletcherSenior DBACommented:
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.
0
 
joschrammAuthor Commented:
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
0
 
joschrammAuthor Commented:
many thanks
Josef
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.