Solved

Out of Control - SQL Transaction Log

Posted on 2013-06-16
8
419 Views
Last Modified: 2013-06-18
I have just started with a compny which has let their Transaction Log ballon to a riduculous level  305 GB for a !.5 GB Catalog.

The backups have been in FULL mode not SIMPLE

The Database was participating in a replication environment.

PUSH replication from a Master Distributed Database

1 Publisher , 3 Subscribers.

One of the Other subscribers - has a trans log of 38 GB.... still bloated in my mind but livable.

What would you recommend to get the Transaction Log to a reasonable level.

Could a person do a detach and reaatach with a new log file ignoring the bloated 305 gb log.
0
Comment
Question by:baeriali
8 Comments
 
LVL 5

Expert Comment

by:AbhishekJha
ID: 39252184
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 250 total points
ID: 39252708
What would you recommend to get the Transaction Log to a reasonable level.
First, backup your transaction logs.  Second, make sure that nothing is creating a bottleneck in transaction distribution.  As long as the transactions are not properly distributed, log will grow.
0
 
LVL 23

Accepted Solution

by:
nemws1 earned 250 total points
ID: 39253374
My question would be if you are doing *any* backups?  FULL and LOG backups should be done at a minimum.  If they are, the transaction log should not be getting that big, I would think.  I do hourly LOG backups and nightly FULL (or DIFF) backups and don't have any log bloat.

Others will yell at me here for even mentioning it, but once backed up (and a backup schedule is set up as well - search for "Ola Hallengren" and perhaps install the excellent maintenance script he offers for free), you can shrink the LOG file back down to a reasonable size.  You can do this with SSMS or using TSQL.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 39254421
How many virtual log files are there in the log?  Use DBCC LOGINFO to find out.  If you're looking at several thousand VLFs then that will slow replication down a LOT.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39254941
Tim,

When you get a chance can you take a look at this thread:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_28150465.html

This is in reference to a blog you wrote some years ago in Tech Republic.

Thanks,
Anthony
0
 
LVL 23

Expert Comment

by:nemws1
ID: 39254975
ac - did you post that to the correct thread?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39254996
ac - did you post that to the correct thread?
Yep.  Sorry for the hijack.  It was directed at chapmandew.
0
 

Author Closing Comment

by:baeriali
ID: 39257077
All solutions were reasonable.  The only reason I chose the two I did was it seemed specific  to the problem I was faced with.  Would like to say thanks for the info.on reading the contents of the log file.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

746 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

10 Experts available now in Live!

Get 1:1 Help Now