asked on

Out of Control - SQL Transaction Log

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.

AbhishekJha

Refer below link for above mentioned issue.

http://blog.sqlauthority.com/2010/09/20/sql-server-how-to-stop-growing-log-file-too-big/

SOLUTION

Racim BOUDJAKDJI

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Nem Schlecht

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chapmandew

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.

Anthony Perkins

Tim,

When you get a chance can you take a look at this thread:
https://www.experts-exchange.com/questions/28150465/How-to-get-CREATE-table-script-using-TSQL.html

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

Thanks,
Anthony

Nem Schlecht

ac - did you post that to the correct thread?

Anthony Perkins

ac - did you post that to the correct thread?
Yep. Sorry for the hijack. It was directed at chapmandew.

baeriali

ASKER

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.