Showing posts with label re-run. Show all posts
Showing posts with label re-run. Show all posts

Tuesday, March 20, 2012

Re-run snapshot?

I deleted the Indexes from several tables. Should I force a re-run of the
snapshot agent?
You should reinitialize if
1) you want the same indexes replicated to the subscriber and are
replicating unclustered indexes - make sure run a sp_refreshsubscriptions
2) you have evaluated the performance impact of your replication solution by
having additional indexes on your subscriber. Additional indexes will slow
inserts, updates, deletes
Merely re-running the snapshot may not do anything.
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
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:u0VNsYHNFHA.2464@.TK2MSFTNGP10.phx.gbl...
> I deleted the Indexes from several tables. Should I force a re-run of the
> snapshot agent?
>
|||Thanks Hilary.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OD18VcHNFHA.1040@.TK2MSFTNGP12.phx.gbl...
> You should reinitialize if
> 1) you want the same indexes replicated to the subscriber and are
> replicating unclustered indexes - make sure run a sp_refreshsubscriptions
> 2) you have evaluated the performance impact of your replication solution
> by
> having additional indexes on your subscriber. Additional indexes will slow
> inserts, updates, deletes
> Merely re-running the snapshot may not do anything.
> --
> 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
> "Earl" <brikshoe@.newsgroups.nospam> wrote in message
> news:u0VNsYHNFHA.2464@.TK2MSFTNGP10.phx.gbl...
>

re-run snapshot agent for each new subscription?

If one day SubscriberA gets a push subscription, run snapshot agent, and
SubscriberA goes from pending to active one the snapshot agent is done. The
next day SubscriberB gets a push subscription from the same publication. It
doesn't seem to go from pending to active until I re-run the snapshot agent
(it has status of waiting for snapshot). Is this by design or am I doing
something wrong? Do I need to run the snapshot agent immediately everytime
I add a subscriber if I want the subscriber to be active immediately?
Thanks
Mike Jansen
Why is it that when I add a new transactional subscription I must re-run the
snapshot agent before the subscription becomes active, even if the snapshot
agent has been run recently? Do changes to the database "invalidate" the
snapshot or am I doing something wrong?
Thanks,
Mike
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:uk8qPdNVFHA.3184@.TK2MSFTNGP15.phx.gbl...
> If one day SubscriberA gets a push subscription, run snapshot agent, and
> SubscriberA goes from pending to active one the snapshot agent is done.
The
> next day SubscriberB gets a push subscription from the same publication.
It
> doesn't seem to go from pending to active until I re-run the snapshot
agent
> (it has status of waiting for snapshot). Is this by design or am I doing
> something wrong? Do I need to run the snapshot agent immediately
everytime
> I add a subscriber if I want the subscriber to be active immediately?
> Thanks
> Mike Jansen
>
|||Mike,
the snapshot files will be deleted by the distribution cleanup agent once
all the subscribers have read them. If you want this not to occur so
frequently, you can schedule the distribution cleanup agent to run less
frequently eg at 12pm and have the snapshot agent run once the cleanup has
finished.
Rgds,
Paul Ibison
|||OK, that explains it. Thanks you very much.
Since you had such a good answer, I have another question
Is there a way to detect via T-SQL whether a valid snapshot exists?
Obviously I can't base it on job statuses because even if jobs complete, the
distribution cleanup will cleanup the snapshot. Is there another status
table I can check?
I'd rather check before running the snapshot agent _every_ time a new
subscription is created (the whole process is done in T-SQL scripts).
Thanks again,
Mike
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e0To7bXVFHA.3272@.TK2MSFTNGP10.phx.gbl...
> Mike,
> the snapshot files will be deleted by the distribution cleanup agent once
> all the subscribers have read them. If you want this not to occur so
> frequently, you can schedule the distribution cleanup agent to run less
> frequently eg at 12pm and have the snapshot agent run once the cleanup has
> finished.
> Rgds,
> Paul Ibison
>
|||Mike,
take a look at sp_browsesnapshotfolder (sp_browsesnapshotfolder
'testrepxxx'). It'll return a row if there is a publication snapshot
available. If it's been cleaned up there will be an empty recordset.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Excellent! I'll hopefully be testing/incorporating this at some point this
week. That was the last piece (for now!) in the replication part of my
database script framework. Thanks again.
Mike
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ORAfK0hVFHA.3540@.TK2MSFTNGP15.phx.gbl...
> Mike,
> take a look at sp_browsesnapshotfolder (sp_browsesnapshotfolder
> 'testrepxxx'). It'll return a row if there is a publication snapshot
> available. If it's been cleaned up there will be an empty recordset.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

Re-run all the failed jobs

I'm wondering if there's an elegant/efficient way to automatically
re-run all the failed jobs under a SQL server 2000 installation.

TIA."NickName" <dadada@.rock.com> wrote in message
news:1105369505.976361.73090@.c13g2000cwb.googlegro ups.com...
> I'm wondering if there's an elegant/efficient way to automatically
> re-run all the failed jobs under a SQL server 2000 installation.
> TIA.

There's no one-click solution, if that's what you want - you would need to
write something yourself. You could create a proc which uses sp_help_job to
get job statuses, then sp_start_job to restart them. Another approach would
be to set each step in every job to go to step 1 on failure, so the job
keeps running until it succeeds.

Personally, I would avoid doing something like this anyway - it's rather
heavy-handed, and if you have job dependencies, critical scheduling issues
and so on, then you don't want jobs just starting at random times. Even if
every job you have now is independent of all others and could run at any
time, you don't don't know what future jobs you might add. And if a job
fails, I would prefer to investigate why, rather than simply keep running it
until it succeeds.

Simon|||You're right I'm not looking for a one-click solution, however, some
sort of automated way but still with some flexibility (as you correctly
pointed out "you don't know what future jobs you might add". Good to
know about sp_help_job and sp_start_job, they seem handy for the task
at hand, however, sp_help_job does not seem to help me much, I would
like it to list all the failed jobs for a given server, then loop
sp_start_job for each failed one. It would be desirable for
sp_help_job or the like to perform something like the above, that is,
EXEC sp_help_job @.execution_status = 'failed' | 0 (btw, currently the
@.execution_status values seems to be weird)
lists all the failed jobs

I appreciate your sharing of your knowledge, though I'm not a dba, it's
good to pick up some skill like this.

Don|||"NickName" <dadada@.rock.com> wrote in message
news:1105455394.853671.239030@.f14g2000cwb.googlegr oups.com...
> You're right I'm not looking for a one-click solution, however, some
> sort of automated way but still with some flexibility (as you correctly
> pointed out "you don't know what future jobs you might add". Good to
> know about sp_help_job and sp_start_job, they seem handy for the task
> at hand, however, sp_help_job does not seem to help me much, I would
> like it to list all the failed jobs for a given server, then loop
> sp_start_job for each failed one. It would be desirable for
> sp_help_job or the like to perform something like the above, that is,
> EXEC sp_help_job @.execution_status = 'failed' | 0 (btw, currently the
> @.execution_status values seems to be weird)
> lists all the failed jobs
> I appreciate your sharing of your knowledge, though I'm not a dba, it's
> good to pick up some skill like this.
> Don

This will list all failed job executions:

exec sp_help_jobhistory @.run_status = 0

Note that this will show all failures of all jobs (whereas sp_help_job shows
only the last run status for all _current_ jobs), so you might be looking at
a failure from 6 months ago for a job which no longer exists. Querying
sysjobs and sysjobhistory directly would give you more control (both tables
are documented in BOL).

But you should really consider concentrating on making sure that jobs do run
and reacting quickly when they don't, otherwise you may allow a situation
where job failures are considered normal, and therefore you never notice any
more serious issues (such as your backup jobs failing).

Of course, you may already have a good reason for doing this, and perhaps in
a development situation it might be useful.

Simon|||It would be really nice for the parameter of @.start_run_date to support
none equality operation, for instance,

exec sp_help_jobhistory @.run_status = 0, @.start_run_date > 20050108
would return all the failed jobs since Jan 8, 2005 instead of possibly
hundreds of rows/failed jobs.

Otherwise, have to use two loops of INNER and OUTER, just less
efficient.

And you're correct job failures are rare but it happens.
Thanks, Simon.

Don