Transaction Log out of control

Question

Post reply

Transaction Log out of control

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

DjH SSC Eights! Points: 843 More actions · Answer 1

DBCC OpenTran returns:

Transaction information for database 'Askesis_Dev'.

Replicated Transaction Information:

Oldest distributed LSN : (0:0:0)

Oldest non-distributed LSN : (849:480:2)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Is there supposed to be any transactional replication set up? Are there any transactional replication publications?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

DjH SSC Eights! Points: 843 More actions · Answer 3

I did have Transactional Replication setup at one point, but i removed it in favor of Snapshot Replication. Transactional Replication is not supposed to be setup on this database.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

I keep on seeing this. Kinda an incomplete cleanup of the replication.

Create a new transactional replication publication.

run sp_repldone in that db. This will not affect the snapshot replication

Drop the publication.

Check DBCC OPENTRAN again. See if the reference to distributed and nondistributed LSN are gone. They should be.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

DjH SSC Eights! Points: 843 More actions · Answer 5

Bingo, that worked. Log Spaced Used % is down from 99% to < 3%

Thanks a bunch!

David Coulby Newbie Points: 3 More actions · Answer 6

Just as a quick note, everything mentioned here is correct and should be adopted as best practice, what I would add is that the ASPState database is used by IIS servers to hold the session state information between pages. Very rarely if at all does that database need to retain transactional information. I've set ours (although not the production machines) to Simple without any complications.

The other point to note is that you should ensure the SQL agent job that should be created during the initialisation of the ASPState database that deletes the expired sessions. If that job is missing or not running and the IIS server/farm are under heavy use it can also generate a large volume in the database.

Dave