Solved

How to shrink a MS SQL Server 2005 Transaction LOG file

Posted on 2013-11-06
5
587 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
  • 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:ScottPletcher
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

896 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now