I want to be emailed when certain alerts are trigger.
There's a place where I'm told I need to install microsoft outlook in
order to do this.
Doesn't come SQL Server with some email capabilities installed ?
The IT guys don't allow me install outlook in the server.
What else can I do ? Even if I need, I would program a command-line
email client in an hour if needed, but I wonder if I would be able to
use it.
Craig,
See:
<craigkenisston@.hotmail.com> wrote in message
news:1129050195.157365.278430@.g44g2000cwa.googlegr oups.com...
>I want to be emailed when certain alerts are trigger.
> There's a place where I'm told I need to install microsoft outlook in
> order to do this.
> Doesn't come SQL Server with some email capabilities installed ?
> The IT guys don't allow me install outlook in the server.
> What else can I do ? Even if I need, I would program a command-line
> email client in an hour if needed, but I wonder if I would be able to
> use it.
>
|||Craig,
See:
http://www.aspfaq.com/show.asp?id=2403
and
http://sqldev.net/xp/xpsmtp.htm
HTH
Jerry
For a non-Outlook environment see:
<craigkenisston@.hotmail.com> wrote in message
news:1129050195.157365.278430@.g44g2000cwa.googlegr oups.com...
>I want to be emailed when certain alerts are trigger.
> There's a place where I'm told I need to install microsoft outlook in
> order to do this.
> Doesn't come SQL Server with some email capabilities installed ?
> The IT guys don't allow me install outlook in the server.
> What else can I do ? Even if I need, I would program a command-line
> email client in an hour if needed, but I wonder if I would be able to
> use it.
>
|||SQL Mail and SQL Agent mail need a mapi compliant email client to work. It
does not have to be outlook and your mail server does not have to be Exchange.
HTH
"craigkenisston@.hotmail.com" wrote:
> I want to be emailed when certain alerts are trigger.
> There's a place where I'm told I need to install microsoft outlook in
> order to do this.
> Doesn't come SQL Server with some email capabilities installed ?
> The IT guys don't allow me install outlook in the server.
> What else can I do ? Even if I need, I would program a command-line
> email client in an hour if needed, but I wonder if I would be able to
> use it.
>
Showing posts with label certain. Show all posts
Showing posts with label certain. Show all posts
Tuesday, March 27, 2012
Do I need to install microsoft outlook ?
I want to be emailed when certain alerts are trigger.
There's a place where I'm told I need to install microsoft outlook in
order to do this.
Doesn't come SQL Server with some email capabilities installed ?
The IT guys don't allow me install outlook in the server.
What else can I do ? Even if I need, I would program a command-line
email client in an hour if needed, but I wonder if I would be able to
use it.Craig,
See:
<craigkenisston@.hotmail.com> wrote in message
news:1129050195.157365.278430@.g44g2000cwa.googlegroups.com...
>I want to be emailed when certain alerts are trigger.
> There's a place where I'm told I need to install microsoft outlook in
> order to do this.
> Doesn't come SQL Server with some email capabilities installed ?
> The IT guys don't allow me install outlook in the server.
> What else can I do ? Even if I need, I would program a command-line
> email client in an hour if needed, but I wonder if I would be able to
> use it.
>|||Craig,
See:
http://www.aspfaq.com/show.asp?id=2403
and
http://sqldev.net/xp/xpsmtp.htm
HTH
Jerry
For a non-Outlook environment see:
<craigkenisston@.hotmail.com> wrote in message
news:1129050195.157365.278430@.g44g2000cwa.googlegroups.com...
>I want to be emailed when certain alerts are trigger.
> There's a place where I'm told I need to install microsoft outlook in
> order to do this.
> Doesn't come SQL Server with some email capabilities installed ?
> The IT guys don't allow me install outlook in the server.
> What else can I do ? Even if I need, I would program a command-line
> email client in an hour if needed, but I wonder if I would be able to
> use it.
>|||SQL Mail and SQL Agent mail need a mapi compliant email client to work. It
does not have to be outlook and your mail server does not have to be Exchang
e.
HTH
"craigkenisston@.hotmail.com" wrote:
> I want to be emailed when certain alerts are trigger.
> There's a place where I'm told I need to install microsoft outlook in
> order to do this.
> Doesn't come SQL Server with some email capabilities installed ?
> The IT guys don't allow me install outlook in the server.
> What else can I do ? Even if I need, I would program a command-line
> email client in an hour if needed, but I wonder if I would be able to
> use it.
>
There's a place where I'm told I need to install microsoft outlook in
order to do this.
Doesn't come SQL Server with some email capabilities installed ?
The IT guys don't allow me install outlook in the server.
What else can I do ? Even if I need, I would program a command-line
email client in an hour if needed, but I wonder if I would be able to
use it.Craig,
See:
<craigkenisston@.hotmail.com> wrote in message
news:1129050195.157365.278430@.g44g2000cwa.googlegroups.com...
>I want to be emailed when certain alerts are trigger.
> There's a place where I'm told I need to install microsoft outlook in
> order to do this.
> Doesn't come SQL Server with some email capabilities installed ?
> The IT guys don't allow me install outlook in the server.
> What else can I do ? Even if I need, I would program a command-line
> email client in an hour if needed, but I wonder if I would be able to
> use it.
>|||Craig,
See:
http://www.aspfaq.com/show.asp?id=2403
and
http://sqldev.net/xp/xpsmtp.htm
HTH
Jerry
For a non-Outlook environment see:
<craigkenisston@.hotmail.com> wrote in message
news:1129050195.157365.278430@.g44g2000cwa.googlegroups.com...
>I want to be emailed when certain alerts are trigger.
> There's a place where I'm told I need to install microsoft outlook in
> order to do this.
> Doesn't come SQL Server with some email capabilities installed ?
> The IT guys don't allow me install outlook in the server.
> What else can I do ? Even if I need, I would program a command-line
> email client in an hour if needed, but I wonder if I would be able to
> use it.
>|||SQL Mail and SQL Agent mail need a mapi compliant email client to work. It
does not have to be outlook and your mail server does not have to be Exchang
e.
HTH
"craigkenisston@.hotmail.com" wrote:
> I want to be emailed when certain alerts are trigger.
> There's a place where I'm told I need to install microsoft outlook in
> order to do this.
> Doesn't come SQL Server with some email capabilities installed ?
> The IT guys don't allow me install outlook in the server.
> What else can I do ? Even if I need, I would program a command-line
> email client in an hour if needed, but I wonder if I would be able to
> use it.
>
Sunday, March 25, 2012
Do I need to install microsoft outlook ?
I want to be emailed when certain alerts are trigger.
There's a place where I'm told I need to install microsoft outlook in
order to do this.
Doesn't come SQL Server with some email capabilities installed ?
The IT guys don't allow me install outlook in the server.
What else can I do ? Even if I need, I would program a command-line
email client in an hour if needed, but I wonder if I would be able to
use it.Craig,
See:
<craigkenisston@.hotmail.com> wrote in message
news:1129050195.157365.278430@.g44g2000cwa.googlegroups.com...
>I want to be emailed when certain alerts are trigger.
> There's a place where I'm told I need to install microsoft outlook in
> order to do this.
> Doesn't come SQL Server with some email capabilities installed ?
> The IT guys don't allow me install outlook in the server.
> What else can I do ? Even if I need, I would program a command-line
> email client in an hour if needed, but I wonder if I would be able to
> use it.
>|||Craig,
See:
http://www.aspfaq.com/show.asp?id=2403
and
http://sqldev.net/xp/xpsmtp.htm
HTH
Jerry
For a non-Outlook environment see:
<craigkenisston@.hotmail.com> wrote in message
news:1129050195.157365.278430@.g44g2000cwa.googlegroups.com...
>I want to be emailed when certain alerts are trigger.
> There's a place where I'm told I need to install microsoft outlook in
> order to do this.
> Doesn't come SQL Server with some email capabilities installed ?
> The IT guys don't allow me install outlook in the server.
> What else can I do ? Even if I need, I would program a command-line
> email client in an hour if needed, but I wonder if I would be able to
> use it.
>|||SQL Mail and SQL Agent mail need a mapi compliant email client to work. It
does not have to be outlook and your mail server does not have to be Exchange.
HTH
"craigkenisston@.hotmail.com" wrote:
> I want to be emailed when certain alerts are trigger.
> There's a place where I'm told I need to install microsoft outlook in
> order to do this.
> Doesn't come SQL Server with some email capabilities installed ?
> The IT guys don't allow me install outlook in the server.
> What else can I do ? Even if I need, I would program a command-line
> email client in an hour if needed, but I wonder if I would be able to
> use it.
>sql
There's a place where I'm told I need to install microsoft outlook in
order to do this.
Doesn't come SQL Server with some email capabilities installed ?
The IT guys don't allow me install outlook in the server.
What else can I do ? Even if I need, I would program a command-line
email client in an hour if needed, but I wonder if I would be able to
use it.Craig,
See:
<craigkenisston@.hotmail.com> wrote in message
news:1129050195.157365.278430@.g44g2000cwa.googlegroups.com...
>I want to be emailed when certain alerts are trigger.
> There's a place where I'm told I need to install microsoft outlook in
> order to do this.
> Doesn't come SQL Server with some email capabilities installed ?
> The IT guys don't allow me install outlook in the server.
> What else can I do ? Even if I need, I would program a command-line
> email client in an hour if needed, but I wonder if I would be able to
> use it.
>|||Craig,
See:
http://www.aspfaq.com/show.asp?id=2403
and
http://sqldev.net/xp/xpsmtp.htm
HTH
Jerry
For a non-Outlook environment see:
<craigkenisston@.hotmail.com> wrote in message
news:1129050195.157365.278430@.g44g2000cwa.googlegroups.com...
>I want to be emailed when certain alerts are trigger.
> There's a place where I'm told I need to install microsoft outlook in
> order to do this.
> Doesn't come SQL Server with some email capabilities installed ?
> The IT guys don't allow me install outlook in the server.
> What else can I do ? Even if I need, I would program a command-line
> email client in an hour if needed, but I wonder if I would be able to
> use it.
>|||SQL Mail and SQL Agent mail need a mapi compliant email client to work. It
does not have to be outlook and your mail server does not have to be Exchange.
HTH
"craigkenisston@.hotmail.com" wrote:
> I want to be emailed when certain alerts are trigger.
> There's a place where I'm told I need to install microsoft outlook in
> order to do this.
> Doesn't come SQL Server with some email capabilities installed ?
> The IT guys don't allow me install outlook in the server.
> What else can I do ? Even if I need, I would program a command-line
> email client in an hour if needed, but I wonder if I would be able to
> use it.
>sql
Do I need cursors?
I am creating a stored procedure to send emails (with xp_sendmail, I think)
based on certain conditions. This is my logic:
I need to loop through all of the Table1 records.
if Table2 exists for Table1 and Table2.column='T'
send variation 1 of email
else if table2 exists for table 1 and Table2.column='F'
send variation 2 of email
else if table3 exists for table 1
send variation 3 of email
Should I use a cursor to loop through the Table1 records? Or should I join
Table1 and Table2/Table3 and not have a top level query? I'm thinking I
should not use a cursor.
My second problem is this. Depending on the email variation I need to loop
through some records and concatenate their values. Do I need a cursor for
that? Or do I have other options?
Thanks for any help, I really appreciate it.Nick
DECLARE @.EmailName VARCHAR(100),@.userid VARCHAR(20)
IF EXISTS (SELECT * FROM Table2 JOIN Table1 ON Table2.pk=Table1.pk AND
Table2.column='T')
> My second problem is this. Depending on the email variation I need to loop
> through some records and concatenate their values. Do I need a cursor for
> that? Or do I have other options?
SET @.userid='john,arie,alex'
SELECT @.EmailName=@.EmailName+COALESCE(Emailadd,
'') +',' FROM users where
CHARINDEX(',' + userid + ',',','+ @.userid +',')>0 and EmailName IS NOT
NULL
SET @.EmailName=LEFT(@.EmailName,LEN(@.EmailNam
e)-1)
--Send emails
I'm currently unable to test it but I'm sure it gives you an idea.
"Nick" <nickfinity@.nospam.nospam> wrote in message
news:48C35529-4878-455F-9DCC-F97057A4A5B8@.microsoft.com...
>I am creating a stored procedure to send emails (with xp_sendmail, I think)
> based on certain conditions. This is my logic:
> I need to loop through all of the Table1 records.
> if Table2 exists for Table1 and Table2.column='T'
> send variation 1 of email
> else if table2 exists for table 1 and Table2.column='F'
> send variation 2 of email
> else if table3 exists for table 1
> send variation 3 of email
> Should I use a cursor to loop through the Table1 records? Or should I join
> Table1 and Table2/Table3 and not have a top level query? I'm thinking I
> should not use a cursor.
> My second problem is this. Depending on the email variation I need to loop
> through some records and concatenate their values. Do I need a cursor for
> that? Or do I have other options?
> Thanks for any help, I really appreciate it.|||>From the limited information you've posted, it sounds like a CASE
statement is what you're looking for. In general, the only time I ever
find that I must use a cursor is when I have to call a stored procedure
on each value in a resultset. They seem to be much more useful to me in
ad hoc situations than in deployed solutions.
CASE Example in an UPDATE (sorry for the poor formatting):
UPDATE <tablealias1>
SET EmailAddress = CASE WHEN <condition1> THEN <expression>
WHEN <condition2> THEN <expression> ELSE <expression> END
FROM Table1 <tablealias1> INNER JOIN Table2 ON <...>
WHERE <condition>
based on certain conditions. This is my logic:
I need to loop through all of the Table1 records.
if Table2 exists for Table1 and Table2.column='T'
send variation 1 of email
else if table2 exists for table 1 and Table2.column='F'
send variation 2 of email
else if table3 exists for table 1
send variation 3 of email
Should I use a cursor to loop through the Table1 records? Or should I join
Table1 and Table2/Table3 and not have a top level query? I'm thinking I
should not use a cursor.
My second problem is this. Depending on the email variation I need to loop
through some records and concatenate their values. Do I need a cursor for
that? Or do I have other options?
Thanks for any help, I really appreciate it.Nick
DECLARE @.EmailName VARCHAR(100),@.userid VARCHAR(20)
IF EXISTS (SELECT * FROM Table2 JOIN Table1 ON Table2.pk=Table1.pk AND
Table2.column='T')
> My second problem is this. Depending on the email variation I need to loop
> through some records and concatenate their values. Do I need a cursor for
> that? Or do I have other options?
SET @.userid='john,arie,alex'
SELECT @.EmailName=@.EmailName+COALESCE(Emailadd,
'') +',' FROM users where
CHARINDEX(',' + userid + ',',','+ @.userid +',')>0 and EmailName IS NOT
NULL
SET @.EmailName=LEFT(@.EmailName,LEN(@.EmailNam
e)-1)
--Send emails
I'm currently unable to test it but I'm sure it gives you an idea.
"Nick" <nickfinity@.nospam.nospam> wrote in message
news:48C35529-4878-455F-9DCC-F97057A4A5B8@.microsoft.com...
>I am creating a stored procedure to send emails (with xp_sendmail, I think)
> based on certain conditions. This is my logic:
> I need to loop through all of the Table1 records.
> if Table2 exists for Table1 and Table2.column='T'
> send variation 1 of email
> else if table2 exists for table 1 and Table2.column='F'
> send variation 2 of email
> else if table3 exists for table 1
> send variation 3 of email
> Should I use a cursor to loop through the Table1 records? Or should I join
> Table1 and Table2/Table3 and not have a top level query? I'm thinking I
> should not use a cursor.
> My second problem is this. Depending on the email variation I need to loop
> through some records and concatenate their values. Do I need a cursor for
> that? Or do I have other options?
> Thanks for any help, I really appreciate it.|||>From the limited information you've posted, it sounds like a CASE
statement is what you're looking for. In general, the only time I ever
find that I must use a cursor is when I have to call a stored procedure
on each value in a resultset. They seem to be much more useful to me in
ad hoc situations than in deployed solutions.
CASE Example in an UPDATE (sorry for the poor formatting):
UPDATE <tablealias1>
SET EmailAddress = CASE WHEN <condition1> THEN <expression>
WHEN <condition2> THEN <expression> ELSE <expression> END
FROM Table1 <tablealias1> INNER JOIN Table2 ON <...>
WHERE <condition>
Friday, March 9, 2012
dividing a large flat file into small files
Hi ,
Is there any method by which I can divide the large flat file into certain number of small files keeping the header in each of the sub files?
Regards,
Prash
See this post by John:http://agilebi.com/cs/blogs/jwelch/archive/2007/06/03/multi-file-output-destination-script-component.aspx|||
prashant550806 wrote:
Hi ,
Is there any method by which I can divide the large flat file into certain number of small files keeping the header in each of the sub files?
Regards,
Prash
This should help as well:
Splitting a file into multiple files
(http://blogs.conchango.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx)
-Jamie
Divide table into two parts
Hi all!
I wanna divide a table into two parts by inserting a page break after a certain row (I need only one page break). How can I achieve this goal?
Thanks.
What do you mean by page break, do you mean that the first part stays in the table and the other part goes to another table ? Which is the ORDER criteria that you want these tables to order for ?HTH, Jens Suessmeyer.
http://www.sqlserver2005.de|||I've got several controls just after table and i need that controls are displaied at the same page as table (at least a row must be at the same page with controls). At the moment I can get a situation when table is displaied in one page and controls are dispaied on another page.
Wednesday, March 7, 2012
distribution sync with backup
hi,
i am not certain what is purpose when the distribution
database is configured with sync with backup. i understand
from the help online that this will not truncate the
publication database log.
however, i can't achieve this.
this is what i did in my testing.
1) configure distribution and publication db with sych
with backup.
2) Backup the distribution db.
3) Add a record in the publication db.
4) Backup the publication db.
5) Make sure that the record is replicated in the
subscriber db.
6) delete the record in the subscriber db.
7) Restore the distribution db from the backup.
8) Backup the publication db.
i expect the missing record to be replicated to the
subscriber db. but it didn't. anything i missed out? or is
my understanding wrong?
thanks for any help.
-
leonghio (leonghio@.yahoo.com.sg)
This option prevents the backup of the distribution database getting ahead
of the backup of the publishing database. You need to add the record then do
the backups - publishing db first then distributor. Next delete the record
on the subscriber then do the restores and it should work ok with the new
record going down.
Regards,
Paul Ibison
|||i tried the following steps but still can't get the
results.
1) make sure both publication and distribution are sync
with backup
2) add a record to publication.
3) log backup publication.
4) full backup distribution.
5) delete the record from subscriber.
6) stop replication agents and restore distribution.
7) start replcation agents.
8) log backup publication again to re-initiate replication.
but record is not replicated again to the subscriber.
what seems to be the problem? am i getting the steps wrong?
thanks!
-
leonghio(leonghio@.yahoo.com.sg)
>--Original Message--
>This option prevents the backup of the distribution
database getting ahead
>of the backup of the publishing database. You need to add
the record then do
>the backups - publishing db first then distributor. Next
delete the record
>on the subscriber then do the restores and it should work
ok with the new
>record going down.
>Regards,
>Paul Ibison
>
>.
>
|||If the record has already gone to the subscriber it won't
be sent again. This is because MSreplication_subscriptions
holds a column called transaction_timestamp which is the
last one sent down. This value can be obtained from
msrepl_commands (the last 8 zeros aren't always seen), so
it would be possible to force the change by updating
MSreplication_subscriptions yourself to a previous
transaction if there is one.
HTH,
Paul Ibison
|||the way the sync with backup setting works is
1) a transaction is entered in the published databases
2) the transaction is written to the transaction log
3) the transaction log is dumped and during the dump the log reader will
then read the transaction from the log and write it to the distribution
database
"leonghio" <anonymous@.discussions.microsoft.com> wrote in message
news:0a5001c4317f$efbf3630$7d02280a@.phx.gbl...
> hi,
> i am not certain what is purpose when the distribution
> database is configured with sync with backup. i understand
> from the help online that this will not truncate the
> publication database log.
> however, i can't achieve this.
> this is what i did in my testing.
> 1) configure distribution and publication db with sych
> with backup.
> 2) Backup the distribution db.
> 3) Add a record in the publication db.
> 4) Backup the publication db.
> 5) Make sure that the record is replicated in the
> subscriber db.
> 6) delete the record in the subscriber db.
> 7) Restore the distribution db from the backup.
> 8) Backup the publication db.
> i expect the missing record to be replicated to the
> subscriber db. but it didn't. anything i missed out? or is
> my understanding wrong?
> thanks for any help.
> -
> leonghio (leonghio@.yahoo.com.sg)
i am not certain what is purpose when the distribution
database is configured with sync with backup. i understand
from the help online that this will not truncate the
publication database log.
however, i can't achieve this.
this is what i did in my testing.
1) configure distribution and publication db with sych
with backup.
2) Backup the distribution db.
3) Add a record in the publication db.
4) Backup the publication db.
5) Make sure that the record is replicated in the
subscriber db.
6) delete the record in the subscriber db.
7) Restore the distribution db from the backup.
8) Backup the publication db.
i expect the missing record to be replicated to the
subscriber db. but it didn't. anything i missed out? or is
my understanding wrong?
thanks for any help.
-
leonghio (leonghio@.yahoo.com.sg)
This option prevents the backup of the distribution database getting ahead
of the backup of the publishing database. You need to add the record then do
the backups - publishing db first then distributor. Next delete the record
on the subscriber then do the restores and it should work ok with the new
record going down.
Regards,
Paul Ibison
|||i tried the following steps but still can't get the
results.
1) make sure both publication and distribution are sync
with backup
2) add a record to publication.
3) log backup publication.
4) full backup distribution.
5) delete the record from subscriber.
6) stop replication agents and restore distribution.
7) start replcation agents.
8) log backup publication again to re-initiate replication.
but record is not replicated again to the subscriber.
what seems to be the problem? am i getting the steps wrong?
thanks!
-
leonghio(leonghio@.yahoo.com.sg)
>--Original Message--
>This option prevents the backup of the distribution
database getting ahead
>of the backup of the publishing database. You need to add
the record then do
>the backups - publishing db first then distributor. Next
delete the record
>on the subscriber then do the restores and it should work
ok with the new
>record going down.
>Regards,
>Paul Ibison
>
>.
>
|||If the record has already gone to the subscriber it won't
be sent again. This is because MSreplication_subscriptions
holds a column called transaction_timestamp which is the
last one sent down. This value can be obtained from
msrepl_commands (the last 8 zeros aren't always seen), so
it would be possible to force the change by updating
MSreplication_subscriptions yourself to a previous
transaction if there is one.
HTH,
Paul Ibison
|||the way the sync with backup setting works is
1) a transaction is entered in the published databases
2) the transaction is written to the transaction log
3) the transaction log is dumped and during the dump the log reader will
then read the transaction from the log and write it to the distribution
database
"leonghio" <anonymous@.discussions.microsoft.com> wrote in message
news:0a5001c4317f$efbf3630$7d02280a@.phx.gbl...
> hi,
> i am not certain what is purpose when the distribution
> database is configured with sync with backup. i understand
> from the help online that this will not truncate the
> publication database log.
> however, i can't achieve this.
> this is what i did in my testing.
> 1) configure distribution and publication db with sych
> with backup.
> 2) Backup the distribution db.
> 3) Add a record in the publication db.
> 4) Backup the publication db.
> 5) Make sure that the record is replicated in the
> subscriber db.
> 6) delete the record in the subscriber db.
> 7) Restore the distribution db from the backup.
> 8) Backup the publication db.
> i expect the missing record to be replicated to the
> subscriber db. but it didn't. anything i missed out? or is
> my understanding wrong?
> thanks for any help.
> -
> leonghio (leonghio@.yahoo.com.sg)
Saturday, February 25, 2012
Distribution Job Step Retries all in one connection and/or transaction?
When a distribution job step "Retry Attempts" is > 0 and the step has certain problems, the step is "retried" after "Retry Interval".
But I am uncertain as to the details. Are the retries within one database transaction? Does each "try" get its own transaction? And what about connection? Is the "retry" done with the same connection? I know this may sound funny but I would like to know exactly what is going on here.
Thanks,
I am pretty sure that each retry spawns its own transaction - any failed attempt will rollback the current transaction. And the retry is done in the same connection unless it's a retry due to a connection issue. I can try to clarify later.Distribution Data File Growth
Recently rebuilt Windows 2003 OS, SQL Server 2000 sp3, 3 publishers of
various shapes and sizes.
I am almost certain that I re-created the distribution database using the
file properties before everything was moved to the new OS. I ended up having
to drop and recreate replication because I didn't back up the publishers
with the keep_replication switch. So, I dropped the distribution database
and created a new one. But, for some reason the data file seems to be
growing and growing. This behavior is unexpected. How can I determine the
cause of this growth? The subscribers are certainly receiving the
transactions. We have other sql servers with multiple publishers and a
single distribution database but the data file for it stays small.
Michelle,
have a look at the msrepl_commands table and see if this is the cause of the
large size. If it is, it could be that you have a subscriber who hasn't
synchronized in a while, or the distribution cleanup agent is disabled, or
you have an anonymous subscriber, so the commands remain until the retention
period is reached.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||run a dbcc opentran in your distributon database to see what happens.
The keep replication switch was designed to be enable disaster recovery
of your transactional replication solution. You can use it to restore
publications on a server but only so you can script out the
publications or view them. Don't expect to use the keep_replication
swithc on a new server and have everything work. You need to restore
the distribution, master, and msdb databases as well.
|||Thanks. I had all of the pieces (msdb, distribution, master, etc.). All
databases were restored but since I didn't back up the publishers with the
keep_replication switch, I couldn't get replication 'kicked off' again. I
re-marked them after the restore for replication and all of the jobs were
succeeding. However, the log reader was not finding any transactions.
No open transactions in distribution. I'll see where Paul's suggestion leads
me...
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:1113509425.813891.157720@.z14g2000cwz.googlegr oups.com...
> run a dbcc opentran in your distributon database to see what happens.
> The keep replication switch was designed to be enable disaster recovery
> of your transactional replication solution. You can use it to restore
> publications on a server but only so you can script out the
> publications or view them. Don't expect to use the keep_replication
> swithc on a new server and have everything work. You need to restore
> the distribution, master, and msdb databases as well.
>
|||Results from msrepl_commands table:
publisher db_id count (xact_seqno)
1 20867
2 1002769
3 159454
Ran distribution clean up agent job (has been running successfully, every 10
minutes):
publisher db_id count (xact_seqno)
1 20866
2 996394
3 158467
The counts are all lower. I added an output file to the job which states:
Removed 74 replicated transactions consisting of 245 statements in 30
seconds (10 rows/sec). Retention max looks to be set at 72 hours (default, I
assume - I don't think that we changed this in the old system).
I'll just keep monitoring this for now. Maybe a 1 GB data file for this
distribution database is not out of line and I no longer have access to the
old system to compare anything.
Thanks,
Michelle
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uWKIq2SQFHA.248@.TK2MSFTNGP15.phx.gbl...
> Michelle,
> have a look at the msrepl_commands table and see if this is the cause of
the
> large size. If it is, it could be that you have a subscriber who hasn't
> synchronized in a while, or the distribution cleanup agent is disabled, or
> you have an anonymous subscriber, so the commands remain until the
retention
> period is reached.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Well over a million records is quite a lot, but I'd be surprised if this
amounts to 1GB. Running, sp_spaceused will give the exact ratio of empty to
used space in the database. If the subscriber(s) have synchronized and are
up to date, you could reduce the retention period and run the cleanup agent
to remove a big chunk of this data but this will only work if anonymous
subscribers aren't enabled.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
various shapes and sizes.
I am almost certain that I re-created the distribution database using the
file properties before everything was moved to the new OS. I ended up having
to drop and recreate replication because I didn't back up the publishers
with the keep_replication switch. So, I dropped the distribution database
and created a new one. But, for some reason the data file seems to be
growing and growing. This behavior is unexpected. How can I determine the
cause of this growth? The subscribers are certainly receiving the
transactions. We have other sql servers with multiple publishers and a
single distribution database but the data file for it stays small.
Michelle,
have a look at the msrepl_commands table and see if this is the cause of the
large size. If it is, it could be that you have a subscriber who hasn't
synchronized in a while, or the distribution cleanup agent is disabled, or
you have an anonymous subscriber, so the commands remain until the retention
period is reached.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||run a dbcc opentran in your distributon database to see what happens.
The keep replication switch was designed to be enable disaster recovery
of your transactional replication solution. You can use it to restore
publications on a server but only so you can script out the
publications or view them. Don't expect to use the keep_replication
swithc on a new server and have everything work. You need to restore
the distribution, master, and msdb databases as well.
|||Thanks. I had all of the pieces (msdb, distribution, master, etc.). All
databases were restored but since I didn't back up the publishers with the
keep_replication switch, I couldn't get replication 'kicked off' again. I
re-marked them after the restore for replication and all of the jobs were
succeeding. However, the log reader was not finding any transactions.
No open transactions in distribution. I'll see where Paul's suggestion leads
me...
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:1113509425.813891.157720@.z14g2000cwz.googlegr oups.com...
> run a dbcc opentran in your distributon database to see what happens.
> The keep replication switch was designed to be enable disaster recovery
> of your transactional replication solution. You can use it to restore
> publications on a server but only so you can script out the
> publications or view them. Don't expect to use the keep_replication
> swithc on a new server and have everything work. You need to restore
> the distribution, master, and msdb databases as well.
>
|||Results from msrepl_commands table:
publisher db_id count (xact_seqno)
1 20867
2 1002769
3 159454
Ran distribution clean up agent job (has been running successfully, every 10
minutes):
publisher db_id count (xact_seqno)
1 20866
2 996394
3 158467
The counts are all lower. I added an output file to the job which states:
Removed 74 replicated transactions consisting of 245 statements in 30
seconds (10 rows/sec). Retention max looks to be set at 72 hours (default, I
assume - I don't think that we changed this in the old system).
I'll just keep monitoring this for now. Maybe a 1 GB data file for this
distribution database is not out of line and I no longer have access to the
old system to compare anything.
Thanks,
Michelle
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uWKIq2SQFHA.248@.TK2MSFTNGP15.phx.gbl...
> Michelle,
> have a look at the msrepl_commands table and see if this is the cause of
the
> large size. If it is, it could be that you have a subscriber who hasn't
> synchronized in a while, or the distribution cleanup agent is disabled, or
> you have an anonymous subscriber, so the commands remain until the
retention
> period is reached.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Well over a million records is quite a lot, but I'd be surprised if this
amounts to 1GB. Running, sp_spaceused will give the exact ratio of empty to
used space in the database. If the subscriber(s) have synchronized and are
up to date, you could reduce the retention period and run the cleanup agent
to remove a big chunk of this data but this will only work if anonymous
subscribers aren't enabled.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Subscribe to:
Posts (Atom)