Maintenace Plan Backup

I created a Maintenance Plan with All Database option. The job Transaction
Log Backup failed because the Master and msdb database is in SIMPLE model.
How can I create a Maintenance Plan with All Database option and run the the
job Transaction Log Backup without fail message?
Thanks in advance.
You can't. Create a separate backup plan for the system DB's and backup the
DB's and not the logs for them. For your app DB's, you create a plan for
them and backup both DB and log.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Eduardo Barcante" <Eduardo Barcante@.discussions.microsoft.com> wrote in
message news:8A52B027-7056-4720-A5FC-1EF404C0FBF9@.microsoft.com...
>I created a Maintenance Plan with All Database option. The job Transaction
> Log Backup failed because the Master and msdb database is in SIMPLE model.
> How can I create a Maintenance Plan with All Database option and run the
> the
> job Transaction Log Backup without fail message?
> Thanks in advance.
|||It means that the All Database option does not work... I will create separate
backups.
Thanks for your help.
"Tom Moreau" wrote:

> You can't. Create a separate backup plan for the system DB's and backup the
> DB's and not the logs for them. For your app DB's, you create a plan for
> them and backup both DB and log.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "Eduardo Barcante" <Eduardo Barcante@.discussions.microsoft.com> wrote in
> message news:8A52B027-7056-4720-A5FC-1EF404C0FBF9@.microsoft.com...
>
>
|||The All Databases option does work - but only for full backs, not log
backups.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Eduardo Barcante" <EduardoBarcante@.discussions.microsoft.com> wrote in
message news:1C0F22CD-555D-46AD-94EA-B663A503C0F5@.microsoft.com...
It means that the All Database option does not work... I will create
separate
backups.
Thanks for your help.
"Tom Moreau" wrote:

> You can't. Create a separate backup plan for the system DB's and backup
> the
> DB's and not the logs for them. For your app DB's, you create a plan for
> them and backup both DB and log.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "Eduardo Barcante" <Eduardo Barcante@.discussions.microsoft.com> wrote in
> message news:8A52B027-7056-4720-A5FC-1EF404C0FBF9@.microsoft.com...
>
>

Maint. plan data optimization on system DBs

Hello,
Does it really make sense to run maint. plan data optimization jobs on
SQL Server 2K system DBs (master,msdb)? If yes, why? Isn't it risky
(e.g. when rebuilding an index fails in the middle of the process)?
Many thanks,
Pablo
No it doesn't - unless you're storing user data in them. You cannot rebuild
the indexes of the critical system tables (sysindexes, syscolumns,
sysobjects) anyway.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"msnews.microsoft.com" <pablo@.this_isnt_my_mail.cc> wrote in message
news:udEsTKpPEHA.308@.TK2MSFTNGP11.phx.gbl...
> Hello,
> Does it really make sense to run maint. plan data optimization jobs on
> SQL Server 2K system DBs (master,msdb)? If yes, why? Isn't it risky
> (e.g. when rebuilding an index fails in the middle of the process)?
> --
> Many thanks,
> Pablo

Maint. plan data optimization on system DBs

Hello,
Does it really make sense to run maint. plan data optimization jobs on
SQL Server 2K system DBs (master,msdb)? If yes, why? Isn't it risky
(e.g. when rebuilding an index fails in the middle of the process)?
--
Many thanks,
PabloNo it doesn't - unless you're storing user data in them. You cannot rebuild
the indexes of the critical system tables (sysindexes, syscolumns,
sysobjects) anyway.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"msnews.microsoft.com" <pablo@.this_isnt_my_mail.cc> wrote in message
news:udEsTKpPEHA.308@.TK2MSFTNGP11.phx.gbl...
> Hello,
> Does it really make sense to run maint. plan data optimization jobs on
> SQL Server 2K system DBs (master,msdb)? If yes, why? Isn't it risky
> (e.g. when rebuilding an index fails in the middle of the process)?
> --
> Many thanks,
> Pablo

Maint. plan data optimization on system DBs

Hello,
Does it really make sense to run maint. plan data optimization jobs on
SQL Server 2K system DBs (master,msdb)? If yes, why? Isn't it risky
(e.g. when rebuilding an index fails in the middle of the process)?
Many thanks,
PabloNo it doesn't - unless you're storing user data in them. You cannot rebuild
the indexes of the critical system tables (sysindexes, syscolumns,
sysobjects) anyway.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"msnews.microsoft.com" <pablo@.this_isnt_my_mail.cc> wrote in message
news:udEsTKpPEHA.308@.TK2MSFTNGP11.phx.gbl...
> Hello,
> Does it really make sense to run maint. plan data optimization jobs on
> SQL Server 2K system DBs (master,msdb)? If yes, why? Isn't it risky
> (e.g. when rebuilding an index fails in the middle of the process)?
> --
> Many thanks,
> Pablo

Mail Profile sql 2005 : How to retry until successfully sent?

Hi
I recently had a big problem with an important job, after sending an email
using the msdb..sp_send_dbmail stored procedure the job succeeded!
BUT
the emails never went out! because the email server was problematic at
roughly the same time. This is the error that was logged in the sql 2005
email job log.:
No connection could be made because the target machine avtively refused it.
mail server failure ... bla bla bla
When i reran the job a couple of hours later the mails went out because the
mail server was up and running.
My question - is there not a way to tell the mail profile to retry sending
the email a couple of times with say 1 minute intervals between retries -
same as in a sql 2005 job'
thanks
IanHi
As I understood, you may want to PING the server
to check whether or not the server is running up and if it succeded then
send the email
Some ideas, check it out
SET NOCOUNT ON
CREATE TABLE #t_ip (ip varchar(255))
DECLARE @.PingSql varchar(1000)
SELECT @.PingSql = 'ping ' + '00.00.0.0'
INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
BEGIN
DROP TABLE #t_ip
RETURN
END
DROP TABLE #t_ip
"I.W Coetzer" <I.W Coetzer@.discussions.microsoft.com> wrote in message
news:27F55D35-F6A1-437D-888D-B897A30DA9A0@.microsoft.com...
> Hi
> I recently had a big problem with an important job, after sending an email
> using the msdb..sp_send_dbmail stored procedure the job succeeded!
> BUT
> the emails never went out! because the email server was problematic at
> roughly the same time. This is the error that was logged in the sql 2005
> email job log.:
> No connection could be made because the target machine avtively refused
> it.
> mail server failure ... bla bla bla
> When i reran the job a couple of hours later the mails went out because
> the
> mail server was up and running.
> My question - is there not a way to tell the mail profile to retry sending
> the email a couple of times with say 1 minute intervals between retries -
> same as in a sql 2005 job'
> thanks
> Ian|||You can set the number of retires and the retry delay
interval at the server configuration level. In T-SQL, you
can use sysmail_configure_sp. If you are using the Database
Mail Configuration wizard in Management Studio, select the
option to View or Change System Parameters.
-Sue
On Mon, 16 Apr 2007 04:14:04 -0700, I.W Coetzer <I.W
Coetzer@.discussions.microsoft.com> wrote:
>Hi
>I recently had a big problem with an important job, after sending an email
>using the msdb..sp_send_dbmail stored procedure the job succeeded!
>BUT
>the emails never went out! because the email server was problematic at
>roughly the same time. This is the error that was logged in the sql 2005
>email job log.:
>No connection could be made because the target machine avtively refused it.
>mail server failure ... bla bla bla
>When i reran the job a couple of hours later the mails went out because the
>mail server was up and running.
>My question - is there not a way to tell the mail profile to retry sending
>the email a couple of times with say 1 minute intervals between retries -
>same as in a sql 2005 job'
>thanks
>Ian|||Hi
This would not have solved the problem because the server was up and running
- but the mail service was hanging / not working i think.
bye
"Uri Dimant" wrote:
> Hi
> As I understood, you may want to PING the server
> to check whether or not the server is running up and if it succeded then
> send the email
> Some ideas, check it out
> SET NOCOUNT ON
> CREATE TABLE #t_ip (ip varchar(255))
> DECLARE @.PingSql varchar(1000)
> SELECT @.PingSql = 'ping ' + '00.00.0.0'
> INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
> IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
> BEGIN
> DROP TABLE #t_ip
> RETURN
> END
> DROP TABLE #t_ip
>
>
> "I.W Coetzer" <I.W Coetzer@.discussions.microsoft.com> wrote in message
> news:27F55D35-F6A1-437D-888D-B897A30DA9A0@.microsoft.com...
> > Hi
> >
> > I recently had a big problem with an important job, after sending an email
> > using the msdb..sp_send_dbmail stored procedure the job succeeded!
> > BUT
> > the emails never went out! because the email server was problematic at
> > roughly the same time. This is the error that was logged in the sql 2005
> > email job log.:
> > No connection could be made because the target machine avtively refused
> > it.
> > mail server failure ... bla bla bla
> >
> > When i reran the job a couple of hours later the mails went out because
> > the
> > mail server was up and running.
> >
> > My question - is there not a way to tell the mail profile to retry sending
> > the email a couple of times with say 1 minute intervals between retries -
> > same as in a sql 2005 job'
> >
> > thanks
> >
> > Ian
>
>|||brilliant! thank you very much - now to wait and see what happens tomorrow
morning ...
bye
"Sue Hoegemeier" wrote:
> You can set the number of retires and the retry delay
> interval at the server configuration level. In T-SQL, you
> can use sysmail_configure_sp. If you are using the Database
> Mail Configuration wizard in Management Studio, select the
> option to View or Change System Parameters.
> -Sue
> On Mon, 16 Apr 2007 04:14:04 -0700, I.W Coetzer <I.W
> Coetzer@.discussions.microsoft.com> wrote:
> >Hi
> >
> >I recently had a big problem with an important job, after sending an email
> >using the msdb..sp_send_dbmail stored procedure the job succeeded!
> >BUT
> >the emails never went out! because the email server was problematic at
> >roughly the same time. This is the error that was logged in the sql 2005
> >email job log.:
> >No connection could be made because the target machine avtively refused it.
> >mail server failure ... bla bla bla
> >
> >When i reran the job a couple of hours later the mails went out because the
> >mail server was up and running.
> >
> >My question - is there not a way to tell the mail profile to retry sending
> >the email a couple of times with say 1 minute intervals between retries -
> >same as in a sql 2005 job'
> >
> >thanks
> >
> >Ian
>

Mail Profile sql 2005 : How to retry until successfully sent?

Hi
I recently had a big problem with an important job, after sending an email
using the msdb..sp_send_dbmail stored procedure the job succeeded!
BUT
the emails never went out! because the email server was problematic at
roughly the same time. This is the error that was logged in the sql 2005
email job log.:
No connection could be made because the target machine avtively refused it.
mail server failure ... bla bla bla
When i reran the job a couple of hours later the mails went out because the
mail server was up and running.
My question - is there not a way to tell the mail profile to retry sending
the email a couple of times with say 1 minute intervals between retries -
same as in a sql 2005 job?
thanks
Ian
Hi
As I understood, you may want to PING the server
to check whether or not the server is running up and if it succeded then
send the email
Some ideas, check it out
SET NOCOUNT ON
CREATE TABLE #t_ip (ip varchar(255))
DECLARE @.PingSql varchar(1000)
SELECT @.PingSql = 'ping ' + '00.00.0.0'
INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
BEGIN
DROP TABLE #t_ip
RETURN
END
DROP TABLE #t_ip
"I.W Coetzer" <I.W Coetzer@.discussions.microsoft.com> wrote in message
news:27F55D35-F6A1-437D-888D-B897A30DA9A0@.microsoft.com...
> Hi
> I recently had a big problem with an important job, after sending an email
> using the msdb..sp_send_dbmail stored procedure the job succeeded!
> BUT
> the emails never went out! because the email server was problematic at
> roughly the same time. This is the error that was logged in the sql 2005
> email job log.:
> No connection could be made because the target machine avtively refused
> it.
> mail server failure ... bla bla bla
> When i reran the job a couple of hours later the mails went out because
> the
> mail server was up and running.
> My question - is there not a way to tell the mail profile to retry sending
> the email a couple of times with say 1 minute intervals between retries -
> same as in a sql 2005 job?
> thanks
> Ian
|||You can set the number of retires and the retry delay
interval at the server configuration level. In T-SQL, you
can use sysmail_configure_sp. If you are using the Database
Mail Configuration wizard in Management Studio, select the
option to View or Change System Parameters.
-Sue
On Mon, 16 Apr 2007 04:14:04 -0700, I.W Coetzer <I.W
Coetzer@.discussions.microsoft.com> wrote:

>Hi
>I recently had a big problem with an important job, after sending an email
>using the msdb..sp_send_dbmail stored procedure the job succeeded!
>BUT
>the emails never went out! because the email server was problematic at
>roughly the same time. This is the error that was logged in the sql 2005
>email job log.:
>No connection could be made because the target machine avtively refused it.
>mail server failure ... bla bla bla
>When i reran the job a couple of hours later the mails went out because the
>mail server was up and running.
>My question - is there not a way to tell the mail profile to retry sending
>the email a couple of times with say 1 minute intervals between retries -
>same as in a sql 2005 job?
>thanks
>Ian
|||Hi
This would not have solved the problem because the server was up and running
- but the mail service was hanging / not working i think.
bye
"Uri Dimant" wrote:

> Hi
> As I understood, you may want to PING the server
> to check whether or not the server is running up and if it succeded then
> send the email
> Some ideas, check it out
> SET NOCOUNT ON
> CREATE TABLE #t_ip (ip varchar(255))
> DECLARE @.PingSql varchar(1000)
> SELECT @.PingSql = 'ping ' + '00.00.0.0'
> INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
> IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
> BEGIN
> DROP TABLE #t_ip
> RETURN
> END
> DROP TABLE #t_ip
>
>
> "I.W Coetzer" <I.W Coetzer@.discussions.microsoft.com> wrote in message
> news:27F55D35-F6A1-437D-888D-B897A30DA9A0@.microsoft.com...
>
>
|||brilliant! thank you very much - now to wait and see what happens tomorrow
morning ...
bye
"Sue Hoegemeier" wrote:

> You can set the number of retires and the retry delay
> interval at the server configuration level. In T-SQL, you
> can use sysmail_configure_sp. If you are using the Database
> Mail Configuration wizard in Management Studio, select the
> option to View or Change System Parameters.
> -Sue
> On Mon, 16 Apr 2007 04:14:04 -0700, I.W Coetzer <I.W
> Coetzer@.discussions.microsoft.com> wrote:
>
>

Mail Profile sql 2005 : How to retry until successfully sent?

Hi
I recently had a big problem with an important job, after sending an email
using the msdb..sp_send_dbmail stored procedure the job succeeded!
BUT
the emails never went out! because the email server was problematic at
roughly the same time. This is the error that was logged in the sql 2005
email job log.:
No connection could be made because the target machine avtively refused it.
mail server failure ... bla bla bla
When i reran the job a couple of hours later the mails went out because the
mail server was up and running.
My question - is there not a way to tell the mail profile to retry sending
the email a couple of times with say 1 minute intervals between retries -
same as in a sql 2005 job'
thanks
IanHi
As I understood, you may want to PING the server
to check whether or not the server is running up and if it succeded then
send the email
Some ideas, check it out
SET NOCOUNT ON
CREATE TABLE #t_ip (ip varchar(255))
DECLARE @.PingSql varchar(1000)
SELECT @.PingSql = 'ping ' + '00.00.0.0'
INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
BEGIN
DROP TABLE #t_ip
RETURN
END
DROP TABLE #t_ip
"I.W Coetzer" <I.W Coetzer@.discussions.microsoft.com> wrote in message
news:27F55D35-F6A1-437D-888D-B897A30DA9A0@.microsoft.com...
> Hi
> I recently had a big problem with an important job, after sending an email
> using the msdb..sp_send_dbmail stored procedure the job succeeded!
> BUT
> the emails never went out! because the email server was problematic at
> roughly the same time. This is the error that was logged in the sql 2005
> email job log.:
> No connection could be made because the target machine avtively refused
> it.
> mail server failure ... bla bla bla
> When i reran the job a couple of hours later the mails went out because
> the
> mail server was up and running.
> My question - is there not a way to tell the mail profile to retry sending
> the email a couple of times with say 1 minute intervals between retries -
> same as in a sql 2005 job'
> thanks
> Ian|||You can set the number of retires and the retry delay
interval at the server configuration level. In T-SQL, you
can use sysmail_configure_sp. If you are using the Database
Mail Configuration wizard in Management Studio, select the
option to View or Change System Parameters.
-Sue
On Mon, 16 Apr 2007 04:14:04 -0700, I.W Coetzer <I.W
Coetzer@.discussions.microsoft.com> wrote:

>Hi
>I recently had a big problem with an important job, after sending an email
>using the msdb..sp_send_dbmail stored procedure the job succeeded!
>BUT
>the emails never went out! because the email server was problematic at
>roughly the same time. This is the error that was logged in the sql 2005
>email job log.:
>No connection could be made because the target machine avtively refused it.
>mail server failure ... bla bla bla
>When i reran the job a couple of hours later the mails went out because the
>mail server was up and running.
>My question - is there not a way to tell the mail profile to retry sending
>the email a couple of times with say 1 minute intervals between retries -
>same as in a sql 2005 job'
>thanks
>Ian|||Hi
This would not have solved the problem because the server was up and running
- but the mail service was hanging / not working i think.
bye
"Uri Dimant" wrote:

> Hi
> As I understood, you may want to PING the server
> to check whether or not the server is running up and if it succeded the
n
> send the email
> Some ideas, check it out
> SET NOCOUNT ON
> CREATE TABLE #t_ip (ip varchar(255))
> DECLARE @.PingSql varchar(1000)
> SELECT @.PingSql = 'ping ' + '00.00.0.0'
> INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
> IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
> BEGIN
> DROP TABLE #t_ip
> RETURN
> END
> DROP TABLE #t_ip
>
>
> "I.W Coetzer" <I.W Coetzer@.discussions.microsoft.com> wrote in message
> news:27F55D35-F6A1-437D-888D-B897A30DA9A0@.microsoft.com...
>
>|||brilliant! thank you very much - now to wait and see what happens tomorrow
morning ...
bye
"Sue Hoegemeier" wrote:

> You can set the number of retires and the retry delay
> interval at the server configuration level. In T-SQL, you
> can use sysmail_configure_sp. If you are using the Database
> Mail Configuration wizard in Management Studio, select the
> option to View or Change System Parameters.
> -Sue
> On Mon, 16 Apr 2007 04:14:04 -0700, I.W Coetzer <I.W
> Coetzer@.discussions.microsoft.com> wrote:
>
>