Another Snap shot for publication is running ?

i was doing replication on SQL 7, both my pulisher and subscriber was SQL 7
and the replicatoin was happening without any problem.Then i upgraded it to
SQL 2000 and restore the backup which was taken on SQL7.
After that when i am starting my agent then it is giving thie error "ANOTHER
SNAPSHOT AGENT FOR THE PUBLICATION IS RUNNING"
Please let me know hot to fix this problem as my complete setup is down.
Thanks & Regards
Asif
There are some reports that this was caused by an incomplete application of
a SQL 7 SP.
With this in mind, perhaps the best option is to script out your
publication(s), and subscriber(s), and then drop them, disable replication,
reenable it, and rerun your publication and subscription scripts.
"Asif" <Asif@.discussions.microsoft.com> wrote in message
news:BCCBF6A0-3EFB-459F-9C58-A0FC19FF2F46@.microsoft.com...
> i was doing replication on SQL 7, both my pulisher and subscriber was SQL
7
> and the replicatoin was happening without any problem.Then i upgraded it
to
> SQL 2000 and restore the backup which was taken on SQL7.
> After that when i am starting my agent then it is giving thie error
"ANOTHER
> SNAPSHOT AGENT FOR THE PUBLICATION IS RUNNING"
> Please let me know hot to fix this problem as my complete setup is down.
> Thanks & Regards
> Asif
|||Thanks for the reply.
Our SQL servers are SQL 2000 from ground up. No SQL7 SPs should be around.
In fact, I eventually was able to reproduce it. The tricky option is on the
Snapshot tab on publication properties - If you check "Do not lock tables
during snapshot generation...", that's when it happened. Without check the
option, you get a normal sp_MSins.
But I still couldn't figure out what exactly this has helped not to block
the table.
Richard
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eO#6xBstEHA.2948@.TK2MSFTNGP15.phx.gbl...
> There are some reports that this was caused by an incomplete application
of
> a SQL 7 SP.
> With this in mind, perhaps the best option is to script out your
> publication(s), and subscriber(s), and then drop them, disable
replication,[vbcol=seagreen]
> reenable it, and rerun your publication and subscription scripts.
>
> "Asif" <Asif@.discussions.microsoft.com> wrote in message
> news:BCCBF6A0-3EFB-459F-9C58-A0FC19FF2F46@.microsoft.com...
SQL
> 7
> to
> "ANOTHER
>
|||It still locks the tables with that box checked. You can see the lock
requests if you watch.
"Richard Ding" wrote:

> Thanks for the reply.
> Our SQL servers are SQL 2000 from ground up. No SQL7 SPs should be around.
> In fact, I eventually was able to reproduce it. The tricky option is on the
> Snapshot tab on publication properties - If you check "Do not lock tables
> during snapshot generation...", that's when it happened. Without check the
> option, you get a normal sp_MSins.
> But I still couldn't figure out what exactly this has helped not to block
> the table.
>
> Richard
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eO#6xBstEHA.2948@.TK2MSFTNGP15.phx.gbl...
> of
> replication,
> SQL
>
>

anormal increase of rowmodctr for a table

HI,
We have 5 GIG OLTP database with 25 users.
Last week, we upgraded from SQL 7.0 to SQL 2000 (sp3a) on W2k (clustered)
Since last week, I began to use "sp_dba_ShowMe_TableStats 'DB','I'" every
days
I cannot explain why rowmodcrt increase so rapidly for a specific table.
It look like 500,000 modifications are done on this table evweryday.
After 4 days, rowmodctr = 2,000,000
I f check witth PROFILER, around 200 upgrades are done on this table
everyday.
Since fews days, I decided to scedule every nignt the Maintenace Plan that
Update Statistic
Any idea ?
Thank you
danny
(P.S. The rowmodctr column in SYSINDEXES value shows the number of changes
on the table since the last time the statistics were updated or created.Hi Danny
Can you tell us a bit about what the procedure sp_dba_ShowMe_TableStats
does?
It is not a supplied procedure, so somebody must have added it to your
system.
Profiler will show you the number of update commands, but rowmodctr counts
every row. How many rows are being updated in each update statement?
How many rows are in the table?
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
news:#h$$mjKAEHA.3804@.TK2MSFTNGP09.phx.gbl...
> HI,
> We have 5 GIG OLTP database with 25 users.
> Last week, we upgraded from SQL 7.0 to SQL 2000 (sp3a) on W2k (clustered)
> Since last week, I began to use "sp_dba_ShowMe_TableStats 'DB','I'" every
> days
> I cannot explain why rowmodcrt increase so rapidly for a specific table.
> It look like 500,000 modifications are done on this table evweryday.
> After 4 days, rowmodctr = 2,000,000
> I f check witth PROFILER, around 200 upgrades are done on this table
> everyday.
> Since fews days, I decided to scedule every nignt the Maintenace Plan that
> Update Statistic
> Any idea ?
> Thank you
> danny
> (P.S. The rowmodctr column in SYSINDEXES value shows the number of changes
> on the table since the last time the statistics were updated or created.
>|||Thank you Kalen

answer: each update statement update only 1 row
answer : 22,000 rows
CREATE PROCEDURE Usp_dba_ShowMe_TableStats @.dbname sysname=NULL, @.option
char (1) = NULL
AS
BEGIN
Declare @.what char(1),
@.qry varchar(2000)
set @.what = @.option
IF @.what = 'I'
Begin
set @.qry = ' Use ' + @.dbName +' SELECT substring(o.name,1,50) AS [table
name], substring(o.name,1,50) AS [Index Name], i.rowmodctr AS [Rows
Modified]
FROM SYSOBJECTS o JOIN SYSINDEXES i
ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = ''U''
ORDER BY i.rowmodctr DESC'
exec (@.qry)
End
ELSE IF @.what = 'A'
Begin
Print space(10)+' Run the Update Statistics on the following Tables'
SET @.qry = 'SET NOCOUNT ON'+char(13)+ 'Use ' + @.dbName + ' SELECT Distinct
''UPDATE STATISTICS''+SPACE(1)+O.NAME+CHAR(13)+''GO'' FROM SYSOBJECTS O
JOIN SYSINDEXES i ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = ''U''
--ORDER BY O.NAME'
exec (@.qry)
End
ELSE
Begin
Print space(10)+'Please pass in the right parameters : DBName and option
"I" for Information or "A" Action"'
PRINT
'----
----'
set @.qry = ' Use ' + @.dbName +' SELECT substring(o.name,1,50) AS [table
name], substring(o.name,1,50) AS [Index Name], i.rowmodctr AS [Rows
Modified]
FROM SYSOBJECTS o JOIN SYSINDEXES i
ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = ''U''
ORDER BY i.rowmodctr DESC'
exec (@.qry)
End
END
"Kalen Delaney" <replies@.public_newsgroups.com> a crit dans le message de
news:O9cu6uPAEHA.3048@.tk2msftngp13.phx.gbl...
> Hi Danny
> Can you tell us a bit about what the procedure sp_dba_ShowMe_TableStats
> does?
> It is not a supplied procedure, so somebody must have added it to your
> system.
> Profiler will show you the number of update commands, but rowmodctr counts
> every row. How many rows are being updated in each update statement?
> How many rows are in the table?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
> news:#h$$mjKAEHA.3804@.TK2MSFTNGP09.phx.gbl...
(clustered)
every
that
changes
>|||HI,
I think I found what happens
A process is modifiing rowmodctr associated with a _WA_Sys
I think I identify the user associated with this process.
danny
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> a crit dans le message de
news:%23h$$mjKAEHA.3804@.TK2MSFTNGP09.phx.gbl...
> HI,
> We have 5 GIG OLTP database with 25 users.
> Last week, we upgraded from SQL 7.0 to SQL 2000 (sp3a) on W2k (clustered)
> Since last week, I began to use "sp_dba_ShowMe_TableStats 'DB','I'" every
> days
> I cannot explain why rowmodcrt increase so rapidly for a specific table.
> It look like 500,000 modifications are done on this table evweryday.
> After 4 days, rowmodctr = 2,000,000
> I f check witth PROFILER, around 200 upgrades are done on this table
> everyday.
> Since fews days, I decided to scedule every nignt the Maintenace Plan that
> Update Statistic
> Any idea ?
> Thank you
> danny
> (P.S. The rowmodctr column in SYSINDEXES value shows the number of changes
> on the table since the last time the statistics were updated or created.
>|||Hi Danny
Good catch! It is a interesting procedure you have, but you can add another
condition to ignore column stats. The number may be high as those are
automatically dropped if no one uses the stats for a while and then maybe
rebuilt later. There is little overhead.
To have your proc ignore stats only rows in sysindexes, you can make this
change:
SELECT substring(o.name,1,50) AS [table
name], substring(o.name,1,50) AS [Index Name], i.rowmodctr AS [Rows
Modified]
FROM SYSOBJECTS o JOIN SYSINDEXES i
ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = ''U''
and indexproperty(i.id, indid, 'IsStatistics') = 0 -- <=======
additional condition
ORDER BY i.rowmodctr DESC
Please read about INDEXPROPERTY in the BOL for more details.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
news:O9AoXHhAEHA.3308@.TK2MSFTNGP10.phx.gbl...
> HI,
> I think I found what happens
> A process is modifiing rowmodctr associated with a _WA_Sys
> I think I identify the user associated with this process.
> danny
> "Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> a crit dans le message de
> news:%23h$$mjKAEHA.3804@.TK2MSFTNGP09.phx.gbl...
(clustered)
every
that
changes
>

anormal increase of rowmodctr for a table

HI,
We have 5 GIG OLTP database with 25 users.
Last week, we upgraded from SQL 7.0 to SQL 2000 (sp3a) on W2k (clustered)
Since last week, I began to use "sp_dba_ShowMe_TableStats 'DB','I'" every
days
I cannot explain why rowmodcrt increase so rapidly for a specific table.
It look like 500,000 modifications are done on this table evweryday.
After 4 days, rowmodctr = 2,000,000
I f check witth PROFILER, around 200 upgrades are done on this table
everyday.
Since fews days, I decided to scedule every nignt the Maintenace Plan that
Update Statistic
Any idea ?
Thank you
danny
(P.S. The rowmodctr column in SYSINDEXES value shows the number of changes
on the table since the last time the statistics were updated or created.Hi Danny
Can you tell us a bit about what the procedure sp_dba_ShowMe_TableStats
does?
It is not a supplied procedure, so somebody must have added it to your
system.
Profiler will show you the number of update commands, but rowmodctr counts
every row. How many rows are being updated in each update statement?
How many rows are in the table?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
news:#h$$mjKAEHA.3804@.TK2MSFTNGP09.phx.gbl...
> HI,
> We have 5 GIG OLTP database with 25 users.
> Last week, we upgraded from SQL 7.0 to SQL 2000 (sp3a) on W2k (clustered)
> Since last week, I began to use "sp_dba_ShowMe_TableStats 'DB','I'" every
> days
> I cannot explain why rowmodcrt increase so rapidly for a specific table.
> It look like 500,000 modifications are done on this table evweryday.
> After 4 days, rowmodctr = 2,000,000
> I f check witth PROFILER, around 200 upgrades are done on this table
> everyday.
> Since fews days, I decided to scedule every nignt the Maintenace Plan that
> Update Statistic
> Any idea ?
> Thank you
> danny
> (P.S. The rowmodctr column in SYSINDEXES value shows the number of changes
> on the table since the last time the statistics were updated or created.
>|||Thank you Kalen
>>How many rows are being updated in each update statement
answer: each update statement update only 1 row
>>How many rows are in the table
answer : 22,000 rows
CREATE PROCEDURE Usp_dba_ShowMe_TableStats @.dbname sysname=NULL, @.option
char (1) = NULL
AS
BEGIN
Declare @.what char(1),
@.qry varchar(2000)
set @.what = @.option
IF @.what = 'I'
Begin
set @.qry = ' Use ' + @.dbName +' SELECT substring(o.name,1,50) AS [table
name], substring(o.name,1,50) AS [Index Name], i.rowmodctr AS [Rows
Modified]
FROM SYSOBJECTS o JOIN SYSINDEXES i
ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = ''U''
ORDER BY i.rowmodctr DESC'
exec (@.qry)
End
ELSE IF @.what = 'A'
Begin
Print space(10)+' Run the Update Statistics on the following Tables'
SET @.qry = 'SET NOCOUNT ON'+char(13)+ 'Use ' + @.dbName + ' SELECT Distinct
''UPDATE STATISTICS''+SPACE(1)+O.NAME+CHAR(13)+''GO'' FROM SYSOBJECTS O
JOIN SYSINDEXES i ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = ''U''
--ORDER BY O.NAME'
exec (@.qry)
End
ELSE
Begin
Print space(10)+'Please pass in the right parameters : DBName and option
"I" for Information or "A" Action"'
PRINT
'----
----'
set @.qry = ' Use ' + @.dbName +' SELECT substring(o.name,1,50) AS [table
name], substring(o.name,1,50) AS [Index Name], i.rowmodctr AS [Rows
Modified]
FROM SYSOBJECTS o JOIN SYSINDEXES i
ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = ''U''
ORDER BY i.rowmodctr DESC'
exec (@.qry)
End
END
"Kalen Delaney" <replies@.public_newsgroups.com> a écrit dans le message de
news:O9cu6uPAEHA.3048@.tk2msftngp13.phx.gbl...
> Hi Danny
> Can you tell us a bit about what the procedure sp_dba_ShowMe_TableStats
> does?
> It is not a supplied procedure, so somebody must have added it to your
> system.
> Profiler will show you the number of update commands, but rowmodctr counts
> every row. How many rows are being updated in each update statement?
> How many rows are in the table?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
> news:#h$$mjKAEHA.3804@.TK2MSFTNGP09.phx.gbl...
> > HI,
> >
> > We have 5 GIG OLTP database with 25 users.
> > Last week, we upgraded from SQL 7.0 to SQL 2000 (sp3a) on W2k
(clustered)
> >
> > Since last week, I began to use "sp_dba_ShowMe_TableStats 'DB','I'"
every
> > days
> > I cannot explain why rowmodcrt increase so rapidly for a specific table.
> > It look like 500,000 modifications are done on this table evweryday.
> > After 4 days, rowmodctr = 2,000,000
> > I f check witth PROFILER, around 200 upgrades are done on this table
> > everyday.
> > Since fews days, I decided to scedule every nignt the Maintenace Plan
that
> > Update Statistic
> >
> > Any idea ?
> >
> > Thank you
> > danny
> > (P.S. The rowmodctr column in SYSINDEXES value shows the number of
changes
> > on the table since the last time the statistics were updated or created.
> >
> >
>|||HI,
I think I found what happens
A process is modifiing rowmodctr associated with a _WA_Sys
I think I identify the user associated with this process.
danny
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> a écrit dans le message de
news:%23h$$mjKAEHA.3804@.TK2MSFTNGP09.phx.gbl...
> HI,
> We have 5 GIG OLTP database with 25 users.
> Last week, we upgraded from SQL 7.0 to SQL 2000 (sp3a) on W2k (clustered)
> Since last week, I began to use "sp_dba_ShowMe_TableStats 'DB','I'" every
> days
> I cannot explain why rowmodcrt increase so rapidly for a specific table.
> It look like 500,000 modifications are done on this table evweryday.
> After 4 days, rowmodctr = 2,000,000
> I f check witth PROFILER, around 200 upgrades are done on this table
> everyday.
> Since fews days, I decided to scedule every nignt the Maintenace Plan that
> Update Statistic
> Any idea ?
> Thank you
> danny
> (P.S. The rowmodctr column in SYSINDEXES value shows the number of changes
> on the table since the last time the statistics were updated or created.
>|||Hi Danny
Good catch! It is a interesting procedure you have, but you can add another
condition to ignore column stats. The number may be high as those are
automatically dropped if no one uses the stats for a while and then maybe
rebuilt later. There is little overhead.
To have your proc ignore stats only rows in sysindexes, you can make this
change:
SELECT substring(o.name,1,50) AS [table
name], substring(o.name,1,50) AS [Index Name], i.rowmodctr AS [Rows
Modified]
FROM SYSOBJECTS o JOIN SYSINDEXES i
ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = ''U''
and indexproperty(i.id, indid, 'IsStatistics') = 0 -- <=======additional condition
ORDER BY i.rowmodctr DESC
--
Please read about INDEXPROPERTY in the BOL for more details.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
news:O9AoXHhAEHA.3308@.TK2MSFTNGP10.phx.gbl...
> HI,
> I think I found what happens
> A process is modifiing rowmodctr associated with a _WA_Sys
> I think I identify the user associated with this process.
> danny
> "Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> a écrit dans le message de
> news:%23h$$mjKAEHA.3804@.TK2MSFTNGP09.phx.gbl...
> > HI,
> >
> > We have 5 GIG OLTP database with 25 users.
> > Last week, we upgraded from SQL 7.0 to SQL 2000 (sp3a) on W2k
(clustered)
> >
> > Since last week, I began to use "sp_dba_ShowMe_TableStats 'DB','I'"
every
> > days
> > I cannot explain why rowmodcrt increase so rapidly for a specific table.
> > It look like 500,000 modifications are done on this table evweryday.
> > After 4 days, rowmodctr = 2,000,000
> > I f check witth PROFILER, around 200 upgrades are done on this table
> > everyday.
> > Since fews days, I decided to scedule every nignt the Maintenace Plan
that
> > Update Statistic
> >
> > Any idea ?
> >
> > Thank you
> > danny
> > (P.S. The rowmodctr column in SYSINDEXES value shows the number of
changes
> > on the table since the last time the statistics were updated or created.
> >
> >
>

Analysis Services upgrade to Enterprise Edition

I was running SQL 2000 Standard with the Analysis Services
repository in a SQL database.
I upgraded SQL to Enterprise. When a user tried to create
a partitioned cube he got: user-defined partitions are
available only if you install Analysis Services SQL Server
2000 Enterprise Edition
I "upgraded" to Analysis Services Enterprise - actually
all it did was reinstall. Since I had already migrated
the repository to SQL, my cubes did not exist.
Here is probably where my mistake occurred: I "re-
migraded" the repository to SQL ... my cubes are still not
there.
I have since restored the repository database from a pre-
upgraded Analysis Services version ... my cubes are still
not there.
I have a large project that is waiting for these cubes.
Please tell me I have a chance of getting these cubes back!
Does anyone have any suggestiongs?
TIA
WadeIf you were using SQL Database A as repository before upgrading and
migrating repsitory to SQL Database B after upgrading, then restore the
backup to Database A won't help. You need to edit the repository connection
string so it points to the database holds the right meta data for OLAP.
Thanks
Wei Zhang
Microsoft OLAP Support

Analysis Services upgrade to Enterprise Edition

I was running SQL 2000 Standard with the Analysis Services
repository in a SQL database.
I upgraded SQL to Enterprise. When a user tried to create
a partitioned cube he got: user-defined partitions are
available only if you install Analysis Services SQL Server
2000 Enterprise Edition
I "upgraded" to Analysis Services Enterprise - actually
all it did was reinstall. Since I had already migrated
the repository to SQL, my cubes did not exist.
Here is probably where my mistake occurred: I "re-
migraded" the repository to SQL ... my cubes are still not
there.
I have since restored the repository database from a pre-
upgraded Analysis Services version ... my cubes are still
not there.
I have a large project that is waiting for these cubes.
Please tell me I have a chance of getting these cubes back!
Does anyone have any suggestiongs?
TIA
Wade
If you were using SQL Database A as repository before upgrading and
migrating repsitory to SQL Database B after upgrading, then restore the
backup to Database A won't help. You need to edit the repository connection
string so it points to the database holds the right meta data for OLAP.
Thanks
Wei Zhang
Microsoft OLAP Support