Solved

How to shrink a MS SQL Server 2005 Transaction LOG file

Posted on 2013-11-06
5
609 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 eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how many extra RAM for SQL server is needed 23 49
MS SQL order by with "over" statement and row_number() 11 63
SQL Error - Query 6 54
Find unused columns in a table 12 90
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.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

738 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