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
>

Inactive record

Hi just wondering if there is a way to have one record in a table be read
only, were an application can not remove it and have all other records act
normally? I am currently handling this in .net code but would prefer to
have it built into the table.
thanks.
--
Paul G
Software engineer.No there is now row level security based on SQL Server 2000.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Paul" <Paul@.discussions.microsoft.com> schrieb im Newsbeitrag
news:BD1A992D-4723-48EA-82C1-7CF94A102258@.microsoft.com...
> Hi just wondering if there is a way to have one record in a table be read
> only, were an application can not remove it and have all other records act
> normally? I am currently handling this in .net code but would prefer to
> have it built into the table.
> thanks.
> --
> Paul G
> Software engineer.|||Sorry, i mean : No there is no row level security based on SQL Server 2000.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> schrieb
im Newsbeitrag news:eNTxiwzSFHA.612@.TK2MSFTNGP12.phx.gbl...
> No there is now row level security based on SQL Server 2000.
> HTH, Jens Suessmeyer.
>
> --
> http://www.sqlserver2005.de
> --
> "Paul" <Paul@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:BD1A992D-4723-48EA-82C1-7CF94A102258@.microsoft.com...
>|||ok thanks for the information.
"Jens Sü?meyer" wrote:

> No there is now row level security based on SQL Server 2000.
> HTH, Jens Suessmeyer.
>
> --
> http://www.sqlserver2005.de
> --
> "Paul" <Paul@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:BD1A992D-4723-48EA-82C1-7CF94A102258@.microsoft.com...
>
>|||On Wed, 27 Apr 2005 07:54:06 -0700, Paul wrote:

>Hi just wondering if there is a way to have one record in a table be read
>only, were an application can not remove it and have all other records act
>normally? I am currently handling this in .net code but would prefer to
>have it built into the table.
>thanks.
Hi Paul,
You can use a trigger:
CREATE TRIGGER DontTouchThisRow
ON MyTable AFTER UPDATE, DELETE
AS
IF EXISTS (SELECT *
FROM deleted
WHERE KeyCol = 1) -- Column to be protected
BEGIN
RAISERROR ('Row 1 may not be changed or removed', 16, 1)
ROLLBACK TRANSACTION
END
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Inactive Publisher???

I set up a Tranactional replication configuration and created a publication
in SQL2005. When I set up the subscriber the wizard stated all succeeded.
However, when I went to the Replication Monitor there was a error for the
publisher. I went to the history for the agent and it stated the following:
2007-02-09 17:56:51.44 Connecting to Distributor 'U1ST081SORTS2'
2007-02-09 17:56:51.57 The replication agent had encountered an exception.
2007-02-09 17:56:51.57 Source: Replication
2007-02-09 17:56:51.57 Exception Type:
Microsoft.SqlServer.Replication.ReplicationAgentEx ception
2007-02-09 17:56:51.57 Exception Message: The snapshot could not be
generated because the publisher is inactive.
2007-02-09 17:56:51.57 Message Code: 54057
Now, this is not only new to me but also to BOL because I couldn't find
anything about inactive "publishers", only subscribers. Also the Message
code didn't provide any answers. I've found places to change subscriber
inactivity timeouts but nothing for publishers.
Any ideas? I'm used to setting up replication in SQL2K and haven't had this
many problems before. Any significant changes in how to set up replication
with 2005?
Roger.
How did you create your publication?
By chance did you did it like this?
sp_addpublication 'mypublication'?
You need to specify the @.status='active' parameter.
Do a sp_helppublication and note the status field is it 0 or 1. 1 means
active.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roger Denison" <RogerDenison@.discussions.microsoft.com> wrote in message
news:0EF738C9-9EE5-43C9-B19C-117280FAA1AB@.microsoft.com...
>I set up a Tranactional replication configuration and created a publication
> in SQL2005. When I set up the subscriber the wizard stated all succeeded.
> However, when I went to the Replication Monitor there was a error for the
> publisher. I went to the history for the agent and it stated the
> following:
> 2007-02-09 17:56:51.44 Connecting to Distributor 'U1ST081SORTS2'
> 2007-02-09 17:56:51.57 The replication agent had encountered an exception.
> 2007-02-09 17:56:51.57 Source: Replication
> 2007-02-09 17:56:51.57 Exception Type:
> Microsoft.SqlServer.Replication.ReplicationAgentEx ception
> 2007-02-09 17:56:51.57 Exception Message: The snapshot could not be
> generated because the publisher is inactive.
> 2007-02-09 17:56:51.57 Message Code: 54057
> Now, this is not only new to me but also to BOL because I couldn't find
> anything about inactive "publishers", only subscribers. Also the Message
> code didn't provide any answers. I've found places to change subscriber
> inactivity timeouts but nothing for publishers.
> Any ideas? I'm used to setting up replication in SQL2K and haven't had
> this
> many problems before. Any significant changes in how to set up
> replication
> with 2005?
> --
> Roger.
|||Hi Roger,
You can set the publisher to active by running sp_changedistpublisher
@.publisher = <your publisher name>, @.property = 'active', @.value = 'true' at
the distributor, although this happens often enough
([url]http://groups.google.com/group/microsoft.public.sqlserver.replication/browse_frm/thread/cb0ab8eb51efbdc0/20d2368928450759?lnk=gst&rnum=41#20d2368928450759[ /url])
that I am a bit worried about what kind of subtle problems we missed during
replication setup through SSMS. Are you having a SQL2000 publisher by any
chance?
-Raymond
"Roger Denison" <RogerDenison@.discussions.microsoft.com> wrote in message
news:0EF738C9-9EE5-43C9-B19C-117280FAA1AB@.microsoft.com...
>I set up a Tranactional replication configuration and created a publication
> in SQL2005. When I set up the subscriber the wizard stated all succeeded.
> However, when I went to the Replication Monitor there was a error for the
> publisher. I went to the history for the agent and it stated the
> following:
> 2007-02-09 17:56:51.44 Connecting to Distributor 'U1ST081SORTS2'
> 2007-02-09 17:56:51.57 The replication agent had encountered an exception.
> 2007-02-09 17:56:51.57 Source: Replication
> 2007-02-09 17:56:51.57 Exception Type:
> Microsoft.SqlServer.Replication.ReplicationAgentEx ception
> 2007-02-09 17:56:51.57 Exception Message: The snapshot could not be
> generated because the publisher is inactive.
> 2007-02-09 17:56:51.57 Message Code: 54057
> Now, this is not only new to me but also to BOL because I couldn't find
> anything about inactive "publishers", only subscribers. Also the Message
> code didn't provide any answers. I've found places to change subscriber
> inactivity timeouts but nothing for publishers.
> Any ideas? I'm used to setting up replication in SQL2K and haven't had
> this
> many problems before. Any significant changes in how to set up
> replication
> with 2005?
> --
> Roger.
|||Raymond/Hillary,
I used the wizard that came with SQL2005 to set up the publisher and
subscription. I used the sp that Hillary gave me determine the status.
Use Distribution
Execute sp_helppublication
Go
But all I saw was a successful completion. No results. Unlike 2000, which
provided results, I'm still getting used to how 2005 works.
Likewise, I ran sp_changedistpublisher as Raymond specified but it couldn't
find my publisher 'Autosort081 XRep'. It said to make sure it was registered
in sysservers in the distributor. I can't find that table (or anything
resembling) and even if I did I can't open it in Dist or Master. Just
another thing I don't like about 2005.
I do have publishers in 2000 but they only publish to other 2000 servers,
not 2005.
Roger.
"Raymond Mak [MSFT]" wrote:

> Hi Roger,
> You can set the publisher to active by running sp_changedistpublisher
> @.publisher = <your publisher name>, @.property = 'active', @.value = 'true' at
> the distributor, although this happens often enough
> ([url]http://groups.google.com/group/microsoft.public.sqlserver.replication/browse_frm/thread/cb0ab8eb51efbdc0/20d2368928450759?lnk=gst&rnum=41#20d2368928450759[ /url])
> that I am a bit worried about what kind of subtle problems we missed during
> replication setup through SSMS. Are you having a SQL2000 publisher by any
> chance?
> -Raymond
> "Roger Denison" <RogerDenison@.discussions.microsoft.com> wrote in message
> news:0EF738C9-9EE5-43C9-B19C-117280FAA1AB@.microsoft.com...
>
>
|||Roger, looks like your replication setup may be in a pretty bad state
(specifically the distributor doesn't seem to know anything about your
publisher). You may need to tear down all replication meta-data at your
publisher (likely by using @.ignore_distributor = 1 in various replication sp
calls). But before doing that, can you tell me how you upgrade the
distributor in the first place? You may also want to check the sys.servers
table in the master database and the MSdistpublishers table in msdb on your
distributor and see if there are any signs that the distributor actually
knows about your publisher at this point.
-Raymond
"Roger Denison" <RogerDenison@.discussions.microsoft.com> wrote in message
news:FF68B0C8-7B2B-4E70-BAD0-197FC3CDC91C@.microsoft.com...[vbcol=seagreen]
> Raymond/Hillary,
> I used the wizard that came with SQL2005 to set up the publisher and
> subscription. I used the sp that Hillary gave me determine the status.
> Use Distribution
> Execute sp_helppublication
> Go
> But all I saw was a successful completion. No results. Unlike 2000,
> which
> provided results, I'm still getting used to how 2005 works.
> Likewise, I ran sp_changedistpublisher as Raymond specified but it
> couldn't
> find my publisher 'Autosort081 XRep'. It said to make sure it was
> registered
> in sysservers in the distributor. I can't find that table (or anything
> resembling) and even if I did I can't open it in Dist or Master. Just
> another thing I don't like about 2005.
>
> I do have publishers in 2000 but they only publish to other 2000 servers,
> not 2005.
> --
> Roger.
>
> "Raymond Mak [MSFT]" wrote:
|||Thanks for your input, Raymond. You think things are messed up, I have two
publications that I can't delete becasue they "don't exist". And I can't
drop a couple of tables because they are used for replication by the two
publishers that don't exist. But that's another issue. This has been a lot
of fun trying to work in 2005.
For starters, I haven't upgraded anything from 2000 to 2005. With my latest
project, my IT guy purchased 2005 instead of 2000. I was able to import data
from one of my other servers so i wasn't starting from scratch. But there
was no replication associated with the tables at that point.
I then imported data from the publisher to the subscriber so that the schema
would already be present on the subscriber.
So I set up my subscriber as the distributor using the wizard. I then
created the publication on the publisher server. As you probably guessed
there are 2 servers in this scenario. From the publication, I launch the New
Subscriber... wizard to create the subscription. I request the publication
to start the snapshot and start the log reader (this is a trans rep). The
wizard says that everything completed successfully but RepMon shows errors.
Now, you ask me to look in some tables in Master or Distribution but I
cannot look at the data. Even though I am logged on as SA I cannot open any
of those ("open table" is grayed out) How do I go about getting access to
the system tables?
Roger.
"Raymond Mak [MSFT]" wrote:

> Roger, looks like your replication setup may be in a pretty bad state
> (specifically the distributor doesn't seem to know anything about your
> publisher). You may need to tear down all replication meta-data at your
> publisher (likely by using @.ignore_distributor = 1 in various replication sp
> calls). But before doing that, can you tell me how you upgrade the
> distributor in the first place? You may also want to check the sys.servers
> table in the master database and the MSdistpublishers table in msdb on your
> distributor and see if there are any signs that the distributor actually
> knows about your publisher at this point.
> -Raymond
> "Roger Denison" <RogerDenison@.discussions.microsoft.com> wrote in message
> news:FF68B0C8-7B2B-4E70-BAD0-197FC3CDC91C@.microsoft.com...
>
>

Inactive Link and Calendar Controls

After installing SQL Server 2005 Reporting Services, report links to
sub-reports are not working and is producing an error. Also, when selecting a
day from the calendar (parameterized), an error occurs as well.
Line: 129
Char: 5
Error: 'event' is null or not an object
Code: 0
URL:
http://reportserv/reports/pages/report.aspx?itempath=%2fDesigners_Council%2fReports%2fForm_LetterHave you looked at the xml code ebhind the report?
Had a similiar issue when migrating reports across. New reports were
fine. Found editing the xml was quick and fixed the issue.
Tom Bizannes
Microsoft Certified Professional
http://www.smartbiz.com.au
Sydney, Australia
Terry wrote:
> After installing SQL Server 2005 Reporting Services, report links to
> sub-reports are not working and is producing an error. Also, when selecting a
> day from the calendar (parameterized), an error occurs as well.
> Line: 129
> Char: 5
> Error: 'event' is null or not an object
> Code: 0
> URL:
> http://reportserv/reports/pages/report.aspx?itempath=%2fDesigners_Council%2fReports%2fForm_Letter|||Thank you for your suggestion.
However, the following solution resolved the linking challenges.
SOLUTION:
Turned off ScriptScan on Anti-Virus software
Moved the following files between ASPNET_CLIENT folders based on ASP.NET
version being used
File -> WebUIValidation.js (cannot have file available in both
ASPNET_CLIENT folders â' causes conflicts)
"SmartbizAustralia" wrote:
> Have you looked at the xml code ebhind the report?
> Had a similiar issue when migrating reports across. New reports were
> fine. Found editing the xml was quick and fixed the issue.
> Tom Bizannes
> Microsoft Certified Professional
> http://www.smartbiz.com.au
> Sydney, Australia
> Terry wrote:
> > After installing SQL Server 2005 Reporting Services, report links to
> > sub-reports are not working and is producing an error. Also, when selecting a
> > day from the calendar (parameterized), an error occurs as well.
> >
> > Line: 129
> > Char: 5
> > Error: 'event' is null or not an object
> > Code: 0
> > URL:
> > http://reportserv/reports/pages/report.aspx?itempath=%2fDesigners_Council%2fReports%2fForm_Letter
>