Same behavior on 2K5 SP4 using your reproducing script:

Physical server, local storage, 32 bit.

select @@version

Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86) Dec 10 2010 10:56:29 ...

Grant Fritchey (8/19/2011)

skrilla99 (8/19/2011)

SELECT dtat.transaction_begin_time,

dtat.transaction_status,

dtat.transaction_state,

...

SELECT dtat.transaction_begin_time,

dtat.transaction_status,

dtat.transaction_state,

der.blocking_session_id,

...

Nice question! Thanks!

Thanks Gail.

That is a staggering amount of data...

-Dan

Nice refresher, thanks!

Could it be the snapshot agent that creates or marks the transaction(s) as replicated?

I had thought that it was the log reader agent that did that.

Wishing I had run sp_replcmds...

Well... I am stumped.

It just happened again, this time with no log reader present.

All VLFs marked as active and full tlog errors being thrown left and right.

No...

<vacuous post>

Woo hoo! Congrats!

What is the over/under for 2 million posts?

</vacuous post>

Have you seen this?

http://www.sqlservercentral.com/Forums/Topic530866-149-1.aspx

-Dan B

You can create new alerts under SQL Server Agent, right-click Alerts and choose New.

Or, like this:

IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Full Log - 9002'))

---- Delete...

Gail,

Thanks again - I just noticed you helped someone else with the same problem only a few weeks ago!

http://www.sqlservercentral.com/Forums/Topic977853-146-1.aspx

Some sort of half-replication epidemic.

😀

-Dan B

Gail,

For larger memory machines, do you stick with a percentage of RAM as max or do you like to leave a static amount? 10% or 10 GB, etc...

Thanks,

Dan B

Hi Anish,

Is it safe to assume there is only one instance on this server?

Does the problem go away with max memory set to 65 GB?

(Edit --> Oops! A bit late...

NJ-DBA,

I think Pradeep was correct in the last thread that DBCC OPENTRAN reads the transaction log to see open transactions.

Perhaps one of Gail Shaw's queries here would help you?

http://www.sqlservercentral.com/Forums/Topic987056-146-1.aspx

Sincerely,

Dan B