Inactive Transaction/Log will not truncate after replication disabled

Hi, all
We have a clustered SQL Server 2000 (SP3a) in Window 2003 server
environment. I set up a transactional replication from 'DB1' at node A (
serves as 'publisher' ) to 'DB2' (as 'distributor' and 'subscriber') at node
B. Database option for DB1 in Node A is 'full recovery' model with
scheduled transaction backup run.
I set up the push replication at 'DB1' with published article 'T' and not
yet to let the snapshot agent start to do the reinitiating, I decided to
disable/remove all replications setup via Enterprise Manager. The Enterprise
Manager did remove everything from what I can see. But from that on, I
noticed that the Transaction Log in the 'DB1' keep growing even after log
file backup, the inactive transactions are queued for future snapshot usage,
I guess. I followed some articles found to run DTS to transfer some data
over to other database or reset up the replication and run the 'EXEC
sp_repldone @.xactid = NULL, @.xact_segno = NULL, @.numtrans = 0, @.time = 0,
@.reset = 1'. They all did truncate the log after the action.
However, problem is that I do not want the replication any more on the
DB1/Node1. It is kind of a vicious cycle in the set up now. If I removed the
replication/publisher setup, I can not run that 'sp_repldone' to clear the
log. But once I put back the replication/publisher for that cleanup action
and remove the replication after that, it seems to put a mark in the
database and the transaction log starts to queue and grow in a
no-replication setup. For comparison, I also did some test in a stand-alone,
non-clustered SQL server box and with publisher/distributor/subscriber all
in the same box but with two SQL Server instances to mimic the operation,
the transaction log seems to work fine and not to keep growing after
replication setup was removed.
Does anyone have the same experience or any insight to this issue ? please
help.
Thanks in advance for any of your input.
Eugene
I am a little confused by this statement "I decided to disable/remove all
replications setup via Enterprise Manager" Do you mean you disabled
replication or your dropped all subscriptions? If you dropped anonymous pull
subscriptions, it is possible that the log reader will keep reading the tlog
on DB1, and writing to the distribution database on DB2.
It seems however, from what you describe that you have disabled replication
on DB1, and still your tlog continues to grow. If this is the case, can you
run dbcc opentran in the database you are publishing? Also run sp_repltran.
sp_repltran should return nothing.
I would try to disable the publisher (db1) from being able to use the
distribution database on db2, and then enable db1 as a distributor, create a
local publication and a local subscription, and then disable publishing.
This should clear the condition.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on SQL FTS/Indexing Services
http://www.indexserverfaq.com
"Eugene Hwang" <hwang_eugene@.hotmail.com> wrote in message
news:uxkXd9KCFHA.2288@.TK2MSFTNGP14.phx.gbl...
> Hi, all
> We have a clustered SQL Server 2000 (SP3a) in Window 2003 server
> environment. I set up a transactional replication from 'DB1' at node A (
> serves as 'publisher' ) to 'DB2' (as 'distributor' and 'subscriber') at
node
> B. Database option for DB1 in Node A is 'full recovery' model with
> scheduled transaction backup run.
> I set up the push replication at 'DB1' with published article 'T' and not
> yet to let the snapshot agent start to do the reinitiating, I decided to
> disable/remove all replications setup via Enterprise Manager. The
Enterprise
> Manager did remove everything from what I can see. But from that on, I
> noticed that the Transaction Log in the 'DB1' keep growing even after log
> file backup, the inactive transactions are queued for future snapshot
usage,
> I guess. I followed some articles found to run DTS to transfer some data
> over to other database or reset up the replication and run the 'EXEC
> sp_repldone @.xactid = NULL, @.xact_segno = NULL, @.numtrans = 0, @.time =
0,
> @.reset = 1'. They all did truncate the log after the action.
> However, problem is that I do not want the replication any more on the
> DB1/Node1. It is kind of a vicious cycle in the set up now. If I removed
the
> replication/publisher setup, I can not run that 'sp_repldone' to clear the
> log. But once I put back the replication/publisher for that cleanup action
> and remove the replication after that, it seems to put a mark in the
> database and the transaction log starts to queue and grow in a
> no-replication setup. For comparison, I also did some test in a
stand-alone,
> non-clustered SQL server box and with publisher/distributor/subscriber all
> in the same box but with two SQL Server instances to mimic the operation,
> the transaction log seems to work fine and not to keep growing after
> replication setup was removed.
> Does anyone have the same experience or any insight to this issue ?
please
> help.
> Thanks in advance for any of your input.
> Eugene
>
>
>
|||Hilary,
I did what you said to re-establish the replication setup again but have
everything ( publisher/distributor/subscriber) LOCALLY in one node instead
of across two nodes that I did before and then disable publishing. It works
perfectly to clean up all marks, the inactive transaction log is cleared up
after the backup. Everything is working now.
Thanks a lot for your prompt suggestion and want to say thanks again on your
attention on almost every post in the group.
Eugene
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e2PBQROCFHA.3840@.tk2msftngp13.phx.gbl...
> I am a little confused by this statement "I decided to disable/remove all
> replications setup via Enterprise Manager" Do you mean you disabled
> replication or your dropped all subscriptions? If you dropped anonymous
pull
> subscriptions, it is possible that the log reader will keep reading the
tlog
> on DB1, and writing to the distribution database on DB2.
> It seems however, from what you describe that you have disabled
replication
> on DB1, and still your tlog continues to grow. If this is the case, can
you
> run dbcc opentran in the database you are publishing? Also run
sp_repltran.
> sp_repltran should return nothing.
> I would try to disable the publisher (db1) from being able to use the
> distribution database on db2, and then enable db1 as a distributor, create
a[vbcol=seagreen]
> local publication and a local subscription, and then disable publishing.
> This should clear the condition.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on SQL FTS/Indexing Services
> http://www.indexserverfaq.com
> "Eugene Hwang" <hwang_eugene@.hotmail.com> wrote in message
> news:uxkXd9KCFHA.2288@.TK2MSFTNGP14.phx.gbl...
> node
not[vbcol=seagreen]
> Enterprise
log[vbcol=seagreen]
> usage,
=[vbcol=seagreen]
> 0,
> the
the[vbcol=seagreen]
action[vbcol=seagreen]
> stand-alone,
all[vbcol=seagreen]
operation,
> please
>

In What Order Index Sorts the Data?

Hi,

I want to ask a basic question, that is

IN WHAT ORDER A CLUSTERED INDEX SORT THE DATA IN THE COLUMN?

Somewhere in the MSDN library I read the following line:

"A clustered index physically sorts the table's contents in the order of the specified index columns"

But Sorting means it will be in ASCENDING ORDER (ASC) or It will be in DESCENDING ORDER (DESC)

So my question is lets suppose a column on which the cluistered index is defined and it contains character data liek abcd so in wht order it will sort the data alphabetically ASC or DESC

or

If the same above case with integer type of values, if column having integer values then in wht order the data in the table will be sorted.

?

Thanks..!!!

You are the one that has to speciy it. By default SS will use ASC.

Code Snippet

use tempdb

go

create table dbo.t1(

c1 int identity not null,

constraint pk_t1 primary key clustered (c1 ASC))

go

drop table dbo.t1

go

create table dbo.t1(

c1 int identity not null,

constraint pk_t1 primary key clustered (c1 DESC))

go

drop table dbo.t1

go

create table dbo.t1(

c1 int identity not null

)

go

create unique clustered index t1_c1_u_c_ix

on dbo.t1(c1 ASC)

go

drop index t1_c1_u_c_ix on dbo.t1

go

create unique clustered index t1_c1_u_c_ix

on dbo.t1(c1 DESC)

go

drop table dbo.t1

go

AMB|||

Ascending by default, but you can specify descinding if you'd like

|||

Hi,

I want to ask a basic question, that is

IN WHAT ORDER A CLUSTERED INDEX SORT THE DATA IN THE COLUMN?

Somewhere in the MSDN library I read the following line:

"A clustered index physically sorts the table's contents in the order of the specified index columns"

But Sorting means it will be in ASCENDING ORDER (ASC) or It will be in DESCENDING ORDER (DESC)

So my question is lets suppose a column on which the cluistered index is defined and it contains character data liek abcd so in wht order it will sort the data alphabetically ASC or DESC

or

If the same above case with integer type of values, if column having integer values then in wht order the data in the table will be sorted.

?

Thanks..!!!

--
PRASHANT PANDEY

Aargh!

Prashant,

Unfortunately, this information is simply wrong. There is absolutely no way to do anything at all in SQL Server to guarantee the physical order of the data. This is a good thing. If the data could be kept this way, you might have to move many gigabytes of data just to insert one row in the middle of a huge table. This would be terrible. "Ok, everyone move over to make room for the new person."

A clustered index does optimize the data storage for retrieval in the order of the clustered key columns, but if you want to be certain that data from a SELECT query comes back in a particular order, you absolutely must include an ORDER BY clause for that SELECT.

You may discover that without the order by, it works for years, and for millions of repetitions, but nevertheless, the order is not guaranteed, and suddenly when your table gets large, or you move to a different storage or processor configuration, or you apply a service pack, you may find you no longer see data in the order you expect.

Just in case all you were asking is in what logical order the data is organized when a column is specified as a key column, it is ascending order unless you specify otherwise with DESC.

Steve Kass

Drew University

http://www.stevekass.com

In what order does a clustered index store data?

Hi

I was going through the book by Kalen Delaney where she has mentioned the following paragpraph in Chapter 7 (Index Internals):

Many documents describing SQL Server indexes will tell you that the clustered index physically stores the data in sorted order. This can be misleading if you think of physical storage as the disk itself. If a clustered index had to keep the data on the actual disk in a particular order, it could be prohibitively expensive to make changes. If a page got too full and had to be split in two, all the data on all the succeeding pages would have to be moved down. Sorted order in a clustered index simply means that the data page chain is logically in order.

Then I read the book on SQL Server 2000 (on Perf Tuning) by Ken England. He says the clustered index stores data in physical order and any insert means moving the data physically. Also the same statement is echoed on the net by many articles.

What is the truth? How are really clustered index stored? What does physical order in the above statement really mean?

Regards

SanjaySi

It is an 'Apples and Oranges' difference.

Kalen is referrring to the actual physical disk sectors, where Ken is referring to the pages where SQL Server stores indexes.

The disk is NOT kept in perfect sequential order. Files can become fragmented from daily ordinary use. For indexes, SQL Server keeps a 'chain' of logical order, so that even where there is a page split, the chain is unbroken.

The 'Logical' order of clustered indexes is indeed kept in sequetial order. (Simplistic view coming...)

The index may be located on pages 1, 2, 3, 4, 5, 6, 27, 55, etc. The logical order 'chain', after page splits may be 1, 2, 55, 3, 27, 4, 5, 6, etc. And due to file fragmentation, the file may be stored in disk sectors 1,2, 454, 455, 1036, 1037, etc.