joschramm
asked on
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.
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.
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
many thanks
Josef
Josef
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/