I set up a Database Maintenance plan for backing 3 databases, their
transaction logs, running optimizations, and to perform integrity checks.
I've been noticing that when I don't receive a notice when my jobs have
completed via email, the Status of the jobs are in "Performing completion
actions". The jobs will not re-run until I either restart SQL server or do a
reboot. Then it works for a while, then the status gets stuck again on
"Performing completion actions".
Does anyone know what could be causing this? It's happening on the database
and transaction log backups and the optimization job. Again, the maintenance
plan is configured for 3 databases, all of which are set to "Full" for their
recovery mode, and the total size of all 3 is around 7GB.
Should I maybe create a separate maintenance plan for each individual
database?
Thank you!
Hi
This may be an issue with sending the notification itself see the thread
http://tinyurl.com/yay4yj and
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b328197&Product=sql.
You could use SMTP emails instead see
http://www.sqldev.net/xp/xpsmtp.htminstead and use the job flow logic of the
job to either send a success or failure message.
John
"Saral6978" wrote:
> I set up a Database Maintenance plan for backing 3 databases, their
> transaction logs, running optimizations, and to perform integrity checks.
> I've been noticing that when I don't receive a notice when my jobs have
> completed via email, the Status of the jobs are in "Performing completion
> actions". The jobs will not re-run until I either restart SQL server or do a
> reboot. Then it works for a while, then the status gets stuck again on
> "Performing completion actions".
> Does anyone know what could be causing this? It's happening on the database
> and transaction log backups and the optimization job. Again, the maintenance
> plan is configured for 3 databases, all of which are set to "Full" for their
> recovery mode, and the total size of all 3 is around 7GB.
> Should I maybe create a separate maintenance plan for each individual
> database?
> Thank you!
|||Thank you, John for your response. However, can you double-check the last
link you posted? It doesn't seem to find the page.
Thanks!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> This may be an issue with sending the notification itself see the thread
> http://tinyurl.com/yay4yj and
> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b328197&Product=sql.
> You could use SMTP emails instead see
> http://www.sqldev.net/xp/xpsmtp.htminstead and use the job flow logic of the
> job to either send a success or failure message.
> John
> "Saral6978" wrote:
|||Hi
There should have been a space before instead try
http://www.sqldev.net/xp/xpsmtp.htm
John
"Saral6978" wrote:
[vbcol=seagreen]
> Thank you, John for your response. However, can you double-check the last
> link you posted? It doesn't seem to find the page.
> Thanks!
> "John Bell" wrote:
|||Thank you!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> There should have been a space before instead try
> http://www.sqldev.net/xp/xpsmtp.htm
> John
> "Saral6978" wrote:
Showing posts with label checks. Show all posts
Showing posts with label checks. Show all posts
Monday, March 19, 2012
Database Maintenance question...
I set up a Database Maintenance plan for backing 3 databases, their
transaction logs, running optimizations, and to perform integrity checks.
I've been noticing that when I don't receive a notice when my jobs have
completed via email, the Status of the jobs are in "Performing completion
actions". The jobs will not re-run until I either restart SQL server or do
a
reboot. Then it works for a while, then the status gets stuck again on
"Performing completion actions".
Does anyone know what could be causing this? It's happening on the database
and transaction log backups and the optimization job. Again, the maintenanc
e
plan is configured for 3 databases, all of which are set to "Full" for their
recovery mode, and the total size of all 3 is around 7GB.
Should I maybe create a separate maintenance plan for each individual
database?
Thank you!Hi
This may be an issue with sending the notification itself see the thread
http://tinyurl.com/yay4yj and
http://support.microsoft.com/defaul...smtp.htminstead and use the job flow logic of the
job to either send a success or failure message.
John
"Saral6978" wrote:
> I set up a Database Maintenance plan for backing 3 databases, their
> transaction logs, running optimizations, and to perform integrity checks.
> I've been noticing that when I don't receive a notice when my jobs have
> completed via email, the Status of the jobs are in "Performing completion
> actions". The jobs will not re-run until I either restart SQL server or d
o a
> reboot. Then it works for a while, then the status gets stuck again on
> "Performing completion actions".
> Does anyone know what could be causing this? It's happening on the databa
se
> and transaction log backups and the optimization job. Again, the maintena
nce
> plan is configured for 3 databases, all of which are set to "Full" for the
ir
> recovery mode, and the total size of all 3 is around 7GB.
> Should I maybe create a separate maintenance plan for each individual
> database?
> Thank you!|||Thank you, John for your response. However, can you double-check the last
link you posted? It doesn't seem to find the page.
Thanks!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> This may be an issue with sending the notification itself see the thread
> http://tinyurl.com/yay4yj and
> http://support.microsoft.com/defaul...smtp.htminstead and use the job flow logic of t
he
> job to either send a success or failure message.
> John
> "Saral6978" wrote:
>|||Hi
There should have been a space before instead try
http://www.sqldev.net/xp/xpsmtp.htm
John
"Saral6978" wrote:
[vbcol=seagreen]
> Thank you, John for your response. However, can you double-check the last
> link you posted? It doesn't seem to find the page.
> Thanks!
> "John Bell" wrote:
>|||Thank you!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> There should have been a space before instead try
> http://www.sqldev.net/xp/xpsmtp.htm
> John
> "Saral6978" wrote:
>
transaction logs, running optimizations, and to perform integrity checks.
I've been noticing that when I don't receive a notice when my jobs have
completed via email, the Status of the jobs are in "Performing completion
actions". The jobs will not re-run until I either restart SQL server or do
a
reboot. Then it works for a while, then the status gets stuck again on
"Performing completion actions".
Does anyone know what could be causing this? It's happening on the database
and transaction log backups and the optimization job. Again, the maintenanc
e
plan is configured for 3 databases, all of which are set to "Full" for their
recovery mode, and the total size of all 3 is around 7GB.
Should I maybe create a separate maintenance plan for each individual
database?
Thank you!Hi
This may be an issue with sending the notification itself see the thread
http://tinyurl.com/yay4yj and
http://support.microsoft.com/defaul...smtp.htminstead and use the job flow logic of the
job to either send a success or failure message.
John
"Saral6978" wrote:
> I set up a Database Maintenance plan for backing 3 databases, their
> transaction logs, running optimizations, and to perform integrity checks.
> I've been noticing that when I don't receive a notice when my jobs have
> completed via email, the Status of the jobs are in "Performing completion
> actions". The jobs will not re-run until I either restart SQL server or d
o a
> reboot. Then it works for a while, then the status gets stuck again on
> "Performing completion actions".
> Does anyone know what could be causing this? It's happening on the databa
se
> and transaction log backups and the optimization job. Again, the maintena
nce
> plan is configured for 3 databases, all of which are set to "Full" for the
ir
> recovery mode, and the total size of all 3 is around 7GB.
> Should I maybe create a separate maintenance plan for each individual
> database?
> Thank you!|||Thank you, John for your response. However, can you double-check the last
link you posted? It doesn't seem to find the page.
Thanks!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> This may be an issue with sending the notification itself see the thread
> http://tinyurl.com/yay4yj and
> http://support.microsoft.com/defaul...smtp.htminstead and use the job flow logic of t
he
> job to either send a success or failure message.
> John
> "Saral6978" wrote:
>|||Hi
There should have been a space before instead try
http://www.sqldev.net/xp/xpsmtp.htm
John
"Saral6978" wrote:
[vbcol=seagreen]
> Thank you, John for your response. However, can you double-check the last
> link you posted? It doesn't seem to find the page.
> Thanks!
> "John Bell" wrote:
>|||Thank you!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> There should have been a space before instead try
> http://www.sqldev.net/xp/xpsmtp.htm
> John
> "Saral6978" wrote:
>
Sunday, March 11, 2012
Database Maintenance Plans
I've set up a Database Maintenance Plan that Checks Database Integrity,
Reorganizes Index, Rebuilds Index, Update Statistics and Cleans Up History.
If I set the plan to perform these tasks on either All Databases or All User
Databases, will new databases be detected? Or would I need to recreate the
plan any time a new database is added?
You'll need to modify your maintanence plan to include the new ones. They'll
not be added to your old plan automatically.
However, performing all these jobs (I don't know your schedule however
something makes me think it's everyday) may not be appropriate for every
environment.
It's also not a good idea to include all of these jobs only in one plan as
one of these jobs would fail, the others after that failed one would not
work.
Ekrem ?nsoy
"cc.shine" <cc.shine@.discussions.microsoft.com> wrote in message
news:0011FCDF-395D-4F01-912C-90C189011EBD@.microsoft.com...
> I've set up a Database Maintenance Plan that Checks Database Integrity,
> Reorganizes Index, Rebuilds Index, Update Statistics and Cleans Up
> History.
> If I set the plan to perform these tasks on either All Databases or All
> User
> Databases, will new databases be detected? Or would I need to recreate
> the
> plan any time a new database is added?
|||Actually Ekrem .. I found maintenance plans pick up new databases without any
issue without me having to recreate it in 2000 and 2005. But then again I
only use that for backing up ;-).
I have found if I change the maintenace plan name after created it onces,
the jobs don't updated properly. And if I add new tasks then they don't get
reflected properly; but for backups it's been good but it's the nightly full
backups only.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Ekrem ?nsoy" wrote:
> You'll need to modify your maintanence plan to include the new ones. They'll
> not be added to your old plan automatically.
> However, performing all these jobs (I don't know your schedule however
> something makes me think it's everyday) may not be appropriate for every
> environment.
> It's also not a good idea to include all of these jobs only in one plan as
> one of these jobs would fail, the others after that failed one would not
> work.
> --
> Ekrem ?nsoy
>
> "cc.shine" <cc.shine@.discussions.microsoft.com> wrote in message
> news:0011FCDF-395D-4F01-912C-90C189011EBD@.microsoft.com...
>
|||Actually, it would be the most correct to say new databases would be
included automatically if you do not choose particular databases from the
list and choose All User Databases =)
Ekrem ?nsoy
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:09E5DB39-C9E5-4D2D-BBF1-AB5303143EFC@.microsoft.com...[vbcol=seagreen]
> Actually Ekrem .. I found maintenance plans pick up new databases without
> any
> issue without me having to recreate it in 2000 and 2005. But then again I
> only use that for backing up ;-).
> I have found if I change the maintenace plan name after created it onces,
> the jobs don't updated properly. And if I add new tasks then they don't
> get
> reflected properly; but for backups it's been good but it's the nightly
> full
> backups only.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Ekrem ?nsoy" wrote:
Reorganizes Index, Rebuilds Index, Update Statistics and Cleans Up History.
If I set the plan to perform these tasks on either All Databases or All User
Databases, will new databases be detected? Or would I need to recreate the
plan any time a new database is added?
You'll need to modify your maintanence plan to include the new ones. They'll
not be added to your old plan automatically.
However, performing all these jobs (I don't know your schedule however
something makes me think it's everyday) may not be appropriate for every
environment.
It's also not a good idea to include all of these jobs only in one plan as
one of these jobs would fail, the others after that failed one would not
work.
Ekrem ?nsoy
"cc.shine" <cc.shine@.discussions.microsoft.com> wrote in message
news:0011FCDF-395D-4F01-912C-90C189011EBD@.microsoft.com...
> I've set up a Database Maintenance Plan that Checks Database Integrity,
> Reorganizes Index, Rebuilds Index, Update Statistics and Cleans Up
> History.
> If I set the plan to perform these tasks on either All Databases or All
> User
> Databases, will new databases be detected? Or would I need to recreate
> the
> plan any time a new database is added?
|||Actually Ekrem .. I found maintenance plans pick up new databases without any
issue without me having to recreate it in 2000 and 2005. But then again I
only use that for backing up ;-).
I have found if I change the maintenace plan name after created it onces,
the jobs don't updated properly. And if I add new tasks then they don't get
reflected properly; but for backups it's been good but it's the nightly full
backups only.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Ekrem ?nsoy" wrote:
> You'll need to modify your maintanence plan to include the new ones. They'll
> not be added to your old plan automatically.
> However, performing all these jobs (I don't know your schedule however
> something makes me think it's everyday) may not be appropriate for every
> environment.
> It's also not a good idea to include all of these jobs only in one plan as
> one of these jobs would fail, the others after that failed one would not
> work.
> --
> Ekrem ?nsoy
>
> "cc.shine" <cc.shine@.discussions.microsoft.com> wrote in message
> news:0011FCDF-395D-4F01-912C-90C189011EBD@.microsoft.com...
>
|||Actually, it would be the most correct to say new databases would be
included automatically if you do not choose particular databases from the
list and choose All User Databases =)
Ekrem ?nsoy
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:09E5DB39-C9E5-4D2D-BBF1-AB5303143EFC@.microsoft.com...[vbcol=seagreen]
> Actually Ekrem .. I found maintenance plans pick up new databases without
> any
> issue without me having to recreate it in 2000 and 2005. But then again I
> only use that for backing up ;-).
> I have found if I change the maintenace plan name after created it onces,
> the jobs don't updated properly. And if I add new tasks then they don't
> get
> reflected properly; but for backups it's been good but it's the nightly
> full
> backups only.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Ekrem ?nsoy" wrote:
Database Maintenance Plans
I've set up a Database Maintenance Plan that Checks Database Integrity,
Reorganizes Index, Rebuilds Index, Update Statistics and Cleans Up History.
If I set the plan to perform these tasks on either All Databases or All User
Databases, will new databases be detected? Or would I need to recreate the
plan any time a new database is added?You'll need to modify your maintanence plan to include the new ones. They'll
not be added to your old plan automatically.
However, performing all these jobs (I don't know your schedule however
something makes me think it's everyday) may not be appropriate for every
environment.
It's also not a good idea to include all of these jobs only in one plan as
one of these jobs would fail, the others after that failed one would not
work.
--
Ekrem Ã?nsoy
"cc.shine" <cc.shine@.discussions.microsoft.com> wrote in message
news:0011FCDF-395D-4F01-912C-90C189011EBD@.microsoft.com...
> I've set up a Database Maintenance Plan that Checks Database Integrity,
> Reorganizes Index, Rebuilds Index, Update Statistics and Cleans Up
> History.
> If I set the plan to perform these tasks on either All Databases or All
> User
> Databases, will new databases be detected? Or would I need to recreate
> the
> plan any time a new database is added?|||Actually Ekrem .. I found maintenance plans pick up new databases without any
issue without me having to recreate it in 2000 and 2005. But then again I
only use that for backing up ;-).
I have found if I change the maintenace plan name after created it onces,
the jobs don't updated properly. And if I add new tasks then they don't get
reflected properly; but for backups it's been good but it's the nightly full
backups only.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Ekrem Ã?nsoy" wrote:
> You'll need to modify your maintanence plan to include the new ones. They'll
> not be added to your old plan automatically.
> However, performing all these jobs (I don't know your schedule however
> something makes me think it's everyday) may not be appropriate for every
> environment.
> It's also not a good idea to include all of these jobs only in one plan as
> one of these jobs would fail, the others after that failed one would not
> work.
> --
> Ekrem Ã?nsoy
>
> "cc.shine" <cc.shine@.discussions.microsoft.com> wrote in message
> news:0011FCDF-395D-4F01-912C-90C189011EBD@.microsoft.com...
> > I've set up a Database Maintenance Plan that Checks Database Integrity,
> > Reorganizes Index, Rebuilds Index, Update Statistics and Cleans Up
> > History.
> > If I set the plan to perform these tasks on either All Databases or All
> > User
> > Databases, will new databases be detected? Or would I need to recreate
> > the
> > plan any time a new database is added?
>|||Actually, it would be the most correct to say new databases would be
included automatically if you do not choose particular databases from the
list and choose All User Databases =)
--
Ekrem Ã?nsoy
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:09E5DB39-C9E5-4D2D-BBF1-AB5303143EFC@.microsoft.com...
> Actually Ekrem .. I found maintenance plans pick up new databases without
> any
> issue without me having to recreate it in 2000 and 2005. But then again I
> only use that for backing up ;-).
> I have found if I change the maintenace plan name after created it onces,
> the jobs don't updated properly. And if I add new tasks then they don't
> get
> reflected properly; but for backups it's been good but it's the nightly
> full
> backups only.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Ekrem Ã?nsoy" wrote:
>> You'll need to modify your maintanence plan to include the new ones.
>> They'll
>> not be added to your old plan automatically.
>> However, performing all these jobs (I don't know your schedule however
>> something makes me think it's everyday) may not be appropriate for every
>> environment.
>> It's also not a good idea to include all of these jobs only in one plan
>> as
>> one of these jobs would fail, the others after that failed one would not
>> work.
>> --
>> Ekrem Ã?nsoy
>>
>> "cc.shine" <cc.shine@.discussions.microsoft.com> wrote in message
>> news:0011FCDF-395D-4F01-912C-90C189011EBD@.microsoft.com...
>> > I've set up a Database Maintenance Plan that Checks Database Integrity,
>> > Reorganizes Index, Rebuilds Index, Update Statistics and Cleans Up
>> > History.
>> > If I set the plan to perform these tasks on either All Databases or All
>> > User
>> > Databases, will new databases be detected? Or would I need to recreate
>> > the
>> > plan any time a new database is added?
Reorganizes Index, Rebuilds Index, Update Statistics and Cleans Up History.
If I set the plan to perform these tasks on either All Databases or All User
Databases, will new databases be detected? Or would I need to recreate the
plan any time a new database is added?You'll need to modify your maintanence plan to include the new ones. They'll
not be added to your old plan automatically.
However, performing all these jobs (I don't know your schedule however
something makes me think it's everyday) may not be appropriate for every
environment.
It's also not a good idea to include all of these jobs only in one plan as
one of these jobs would fail, the others after that failed one would not
work.
--
Ekrem Ã?nsoy
"cc.shine" <cc.shine@.discussions.microsoft.com> wrote in message
news:0011FCDF-395D-4F01-912C-90C189011EBD@.microsoft.com...
> I've set up a Database Maintenance Plan that Checks Database Integrity,
> Reorganizes Index, Rebuilds Index, Update Statistics and Cleans Up
> History.
> If I set the plan to perform these tasks on either All Databases or All
> User
> Databases, will new databases be detected? Or would I need to recreate
> the
> plan any time a new database is added?|||Actually Ekrem .. I found maintenance plans pick up new databases without any
issue without me having to recreate it in 2000 and 2005. But then again I
only use that for backing up ;-).
I have found if I change the maintenace plan name after created it onces,
the jobs don't updated properly. And if I add new tasks then they don't get
reflected properly; but for backups it's been good but it's the nightly full
backups only.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Ekrem Ã?nsoy" wrote:
> You'll need to modify your maintanence plan to include the new ones. They'll
> not be added to your old plan automatically.
> However, performing all these jobs (I don't know your schedule however
> something makes me think it's everyday) may not be appropriate for every
> environment.
> It's also not a good idea to include all of these jobs only in one plan as
> one of these jobs would fail, the others after that failed one would not
> work.
> --
> Ekrem Ã?nsoy
>
> "cc.shine" <cc.shine@.discussions.microsoft.com> wrote in message
> news:0011FCDF-395D-4F01-912C-90C189011EBD@.microsoft.com...
> > I've set up a Database Maintenance Plan that Checks Database Integrity,
> > Reorganizes Index, Rebuilds Index, Update Statistics and Cleans Up
> > History.
> > If I set the plan to perform these tasks on either All Databases or All
> > User
> > Databases, will new databases be detected? Or would I need to recreate
> > the
> > plan any time a new database is added?
>|||Actually, it would be the most correct to say new databases would be
included automatically if you do not choose particular databases from the
list and choose All User Databases =)
--
Ekrem Ã?nsoy
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:09E5DB39-C9E5-4D2D-BBF1-AB5303143EFC@.microsoft.com...
> Actually Ekrem .. I found maintenance plans pick up new databases without
> any
> issue without me having to recreate it in 2000 and 2005. But then again I
> only use that for backing up ;-).
> I have found if I change the maintenace plan name after created it onces,
> the jobs don't updated properly. And if I add new tasks then they don't
> get
> reflected properly; but for backups it's been good but it's the nightly
> full
> backups only.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Ekrem Ã?nsoy" wrote:
>> You'll need to modify your maintanence plan to include the new ones.
>> They'll
>> not be added to your old plan automatically.
>> However, performing all these jobs (I don't know your schedule however
>> something makes me think it's everyday) may not be appropriate for every
>> environment.
>> It's also not a good idea to include all of these jobs only in one plan
>> as
>> one of these jobs would fail, the others after that failed one would not
>> work.
>> --
>> Ekrem Ã?nsoy
>>
>> "cc.shine" <cc.shine@.discussions.microsoft.com> wrote in message
>> news:0011FCDF-395D-4F01-912C-90C189011EBD@.microsoft.com...
>> > I've set up a Database Maintenance Plan that Checks Database Integrity,
>> > Reorganizes Index, Rebuilds Index, Update Statistics and Cleans Up
>> > History.
>> > If I set the plan to perform these tasks on either All Databases or All
>> > User
>> > Databases, will new databases be detected? Or would I need to recreate
>> > the
>> > plan any time a new database is added?
Wednesday, March 7, 2012
Database Maintenance Plan
Overview of my database: size - 45 GB, Full Recovery Mode, Log shipping ever 15 minutes; Full Backup nightly; Optimization and integrity checks on Sunday night.
The Database takes a transactional log every 15 minutes and ships it to our failover database. Usually these are small logs and are executed in matter of seconds.
On sunday night there is a problem. The optimization job (part of DB maintenance plan) runs for 3 hours and during this time the transactional log backup continues to runs and takes close to 4 hours of complete. When the tran log completes its close to a 30 GB file and this is causing problems with space and shipping. This also causes the failover server to be out of sync and at times we have to re setup log shipping.
Does anyone know if there is a way to get around this problem. Appreciate all help and comments.Its common to bulge Tlog when db maintenance plan perform DB optimization process and its better to pause Log shipping during this operation and take full backup once Maint.plan finishes the task and apply to standby server, then re-enable LS process.|||Thanks.
Is there a way to automate the pause of the TLog backup? I hate to be up at 3 in the morning to disable/enable the jobs manually.
The Database takes a transactional log every 15 minutes and ships it to our failover database. Usually these are small logs and are executed in matter of seconds.
On sunday night there is a problem. The optimization job (part of DB maintenance plan) runs for 3 hours and during this time the transactional log backup continues to runs and takes close to 4 hours of complete. When the tran log completes its close to a 30 GB file and this is causing problems with space and shipping. This also causes the failover server to be out of sync and at times we have to re setup log shipping.
Does anyone know if there is a way to get around this problem. Appreciate all help and comments.Its common to bulge Tlog when db maintenance plan perform DB optimization process and its better to pause Log shipping during this operation and take full backup once Maint.plan finishes the task and apply to standby server, then re-enable LS process.|||Thanks.
Is there a way to automate the pause of the TLog backup? I hate to be up at 3 in the morning to disable/enable the jobs manually.
Subscribe to:
Posts (Atom)