Solved

How to shrink a MS SQL Server 2005 Transaction LOG file

Posted on 2013-11-06
5
604 Views
Last Modified: 2013-11-07
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.
0
Comment
Question by:joschramm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 15

Expert Comment

by:pateljitu
ID: 39627824
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39628802
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
 

Author Comment

by:joschramm
ID: 39630190
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
 
LVL 15

Accepted Solution

by:
pateljitu earned 500 total points
ID: 39630441
0
 

Author Closing Comment

by:joschramm
ID: 39630481
many thanks
Josef
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question