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 integrity. Show all posts
Showing posts with label integrity. 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?
Thursday, March 8, 2012
database maintenance plan - integrity check
I've set up a monthly maint. plan that will re-org the db. I'm hoping that since this is a very high tran vol that I'm being proactive.
But, when I do this against a matching test db (exact same one but the name) I get a SET QUOTED IDENTIFIER incorrect on the optimization step. I've checked the properties of the DB and the 'Use quoted identifiers' box is checked and needs to be. I created the maint plan using the wizard and the option I've tried it with the option of reorg to orig amt of free space and change free space selected.
Help!!!! Oh, this is a PeopleSoft DB, so I can't change the prop of the DB..
Try putting the following statements at the top of the step with the SQL Agent Job that the DB Maintenance Plan creates to schedule execution of the plan:
set nocount on
set arithabort on
set ansi_nulls on
set ansi_null_dflt_on on
set ansi_padding on
set ansi_warnings on
set cursor_close_on_commit off
set implicit_transactions off
set quoted_identifier on
set concat_null_yields_null on
set numeric_roundabort off
That should be directly followed by the statement that already existed within the step to execute the master.dbo.xp_sqlmaint procedure.
HTH
|||Sorry to take so long, I had to find out how to add the commands.Which I did, but I'm still getting the same error. I c&p'd
them into the 'edit' of the job step and applied. Then I went
back and checked that they were still there. I started the job
(which I've set up on a copy of the prod db) and it died w/the
same error.
As you can tell, this is new stuff to me.
What else can I try?|||Well, try changing the 'on' in the 'set quoted identifier on' statement to 'off' instead...all comes down to differences between these settings in the msdb, master, and your user database...|||I couldn't chg the flag. But did get the suggestion you sent to work. I removed the line returns at the end of each line and replaced them with just a space, so that the entries where all strung together when I edited the step. So that worked.
So thank you. Now I just have to learn how to keep them when the job is refreshed.
as you've probably guessed, I'm learning as I go.|||
I'm back! Thought I had this resolved, but am still having the same error. The bad thing is that it only occurs on some of the DBs in the same instance of SQL Server. I've checked the options on the DBs and they match between the ones that can be integrity checked and the ones that can not. The ones that can be i ckd will run w/o any of the overrides. For the ones that the job gets an error, I've tried quoted_identifier on and off, with the other options and w/o. All DBs have ANSI NULL default, Auto update statistics, torn page, auto create statistics and use quoted identifiers checked, others not. (sql 2000). Db compatibility level 80 is selected on the compatibility. Some Dbs are model simple, some are full. That doesn't seem to matter.
I'd appreciate any more ideas.........
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.
Database Maintenance
We are running SQL 2000 Enterprise Edition on Windows 2003 Standard Edition
Server. We setup a database maintenance (Database Integrity and Index,
Reorganize data and indexes, etc. ) to run every week. However, the report
shows that the maintenance job did not run because the database needs to be
in a single user mode. How do you setup a single user mode to run the
database maintenance? When the database maintenance is done, how do you
remove the single user mode? Thanks.
Uncheck the "Attempt to repair minor problems" checkbox. All this does is
hide problems until they become unfixable.
Run DBCC CHECKDB manually. You will find you have a problem. Fix the
problem. You will likely have to take the system offline to fix it.
Database corruption issues are very often due to disk subsystem failures.
Check on that and repair if necessary.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:ubtudFMOGHA.2128@.TK2MSFTNGP10.phx.gbl...
> We are running SQL 2000 Enterprise Edition on Windows 2003 Standard
> Edition Server. We setup a database maintenance (Database Integrity and
> Index, Reorganize data and indexes, etc. ) to run every week. However,
> the report shows that the maintenance job did not run because the database
> needs to be in a single user mode. How do you setup a single user mode to
> run the database maintenance? When the database maintenance is done, how
> do you remove the single user mode? Thanks.
>
|||Thanks very much for your prompt response, Geoff.
If I run DBCC CHECKDB manually, what kind of problems do you expect that I
will encounter? Normally, are these problems easy to fix? Thanks.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23h63CRMOGHA.2268@.TK2MSFTNGP09.phx.gbl...
> Uncheck the "Attempt to repair minor problems" checkbox. All this does is
> hide problems until they become unfixable.
> Run DBCC CHECKDB manually. You will find you have a problem. Fix the
> problem. You will likely have to take the system offline to fix it.
> Database corruption issues are very often due to disk subsystem failures.
> Check on that and repair if necessary.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:ubtudFMOGHA.2128@.TK2MSFTNGP10.phx.gbl...
>
|||How do you take the system offline to fix a problem?
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23h63CRMOGHA.2268@.TK2MSFTNGP09.phx.gbl...
> Uncheck the "Attempt to repair minor problems" checkbox. All this does is
> hide problems until they become unfixable.
> Run DBCC CHECKDB manually. You will find you have a problem. Fix the
> problem. You will likely have to take the system offline to fix it.
> Database corruption issues are very often due to disk subsystem failures.
> Check on that and repair if necessary.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:ubtudFMOGHA.2128@.TK2MSFTNGP10.phx.gbl...
>
|||Answering both responses.
I don't know what kinds of problems you will find. You can run DBCC CHECKDB
anytime, but it does create a significant load on the system. I would do it
at a low activity time. To do a repair, you first have to take the database
into single-user mode. Both topics are covered in BOL under DBCC CHECKDB
and DBCC DBREPAIR. I suggest you do a LOT of reading before trying
anything.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:OLSGtcMOGHA.3924@.TK2MSFTNGP14.phx.gbl...
> How do you take the system offline to fix a problem?
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:%23h63CRMOGHA.2268@.TK2MSFTNGP09.phx.gbl...
>
|||Thanks very much for your prompt response, Geoff.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OKBZgjMOGHA.3576@.TK2MSFTNGP15.phx.gbl...
> Answering both responses.
> I don't know what kinds of problems you will find. You can run DBCC
> CHECKDB anytime, but it does create a significant load on the system. I
> would do it at a low activity time. To do a repair, you first have to
> take the database into single-user mode. Both topics are covered in BOL
> under DBCC CHECKDB and DBCC DBREPAIR. I suggest you do a LOT of reading
> before trying anything.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:OLSGtcMOGHA.3924@.TK2MSFTNGP14.phx.gbl...
>
Server. We setup a database maintenance (Database Integrity and Index,
Reorganize data and indexes, etc. ) to run every week. However, the report
shows that the maintenance job did not run because the database needs to be
in a single user mode. How do you setup a single user mode to run the
database maintenance? When the database maintenance is done, how do you
remove the single user mode? Thanks.
Uncheck the "Attempt to repair minor problems" checkbox. All this does is
hide problems until they become unfixable.
Run DBCC CHECKDB manually. You will find you have a problem. Fix the
problem. You will likely have to take the system offline to fix it.
Database corruption issues are very often due to disk subsystem failures.
Check on that and repair if necessary.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:ubtudFMOGHA.2128@.TK2MSFTNGP10.phx.gbl...
> We are running SQL 2000 Enterprise Edition on Windows 2003 Standard
> Edition Server. We setup a database maintenance (Database Integrity and
> Index, Reorganize data and indexes, etc. ) to run every week. However,
> the report shows that the maintenance job did not run because the database
> needs to be in a single user mode. How do you setup a single user mode to
> run the database maintenance? When the database maintenance is done, how
> do you remove the single user mode? Thanks.
>
|||Thanks very much for your prompt response, Geoff.
If I run DBCC CHECKDB manually, what kind of problems do you expect that I
will encounter? Normally, are these problems easy to fix? Thanks.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23h63CRMOGHA.2268@.TK2MSFTNGP09.phx.gbl...
> Uncheck the "Attempt to repair minor problems" checkbox. All this does is
> hide problems until they become unfixable.
> Run DBCC CHECKDB manually. You will find you have a problem. Fix the
> problem. You will likely have to take the system offline to fix it.
> Database corruption issues are very often due to disk subsystem failures.
> Check on that and repair if necessary.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:ubtudFMOGHA.2128@.TK2MSFTNGP10.phx.gbl...
>
|||How do you take the system offline to fix a problem?
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23h63CRMOGHA.2268@.TK2MSFTNGP09.phx.gbl...
> Uncheck the "Attempt to repair minor problems" checkbox. All this does is
> hide problems until they become unfixable.
> Run DBCC CHECKDB manually. You will find you have a problem. Fix the
> problem. You will likely have to take the system offline to fix it.
> Database corruption issues are very often due to disk subsystem failures.
> Check on that and repair if necessary.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:ubtudFMOGHA.2128@.TK2MSFTNGP10.phx.gbl...
>
|||Answering both responses.
I don't know what kinds of problems you will find. You can run DBCC CHECKDB
anytime, but it does create a significant load on the system. I would do it
at a low activity time. To do a repair, you first have to take the database
into single-user mode. Both topics are covered in BOL under DBCC CHECKDB
and DBCC DBREPAIR. I suggest you do a LOT of reading before trying
anything.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:OLSGtcMOGHA.3924@.TK2MSFTNGP14.phx.gbl...
> How do you take the system offline to fix a problem?
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:%23h63CRMOGHA.2268@.TK2MSFTNGP09.phx.gbl...
>
|||Thanks very much for your prompt response, Geoff.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OKBZgjMOGHA.3576@.TK2MSFTNGP15.phx.gbl...
> Answering both responses.
> I don't know what kinds of problems you will find. You can run DBCC
> CHECKDB anytime, but it does create a significant load on the system. I
> would do it at a low activity time. To do a repair, you first have to
> take the database into single-user mode. Both topics are covered in BOL
> under DBCC CHECKDB and DBCC DBREPAIR. I suggest you do a LOT of reading
> before trying anything.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:OLSGtcMOGHA.3924@.TK2MSFTNGP14.phx.gbl...
>
Database Maintenance
We are running SQL 2000 Enterprise Edition on Windows 2003 Standard Edition
Server. We setup a database maintenance (Database Integrity and Index,
Reorganize data and indexes, etc. ) to run every week. However, the report
shows that the maintenance job did not run because the database needs to be
in a single user mode. How do you setup a single user mode to run the
database maintenance? When the database maintenance is done, how do you
remove the single user mode? Thanks.Uncheck the "Attempt to repair minor problems" checkbox. All this does is
hide problems until they become unfixable.
Run DBCC CHECKDB manually. You will find you have a problem. Fix the
problem. You will likely have to take the system offline to fix it.
Database corruption issues are very often due to disk subsystem failures.
Check on that and repair if necessary.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:ubtudFMOGHA.2128@.TK2MSFTNGP10.phx.gbl...
> We are running SQL 2000 Enterprise Edition on Windows 2003 Standard
> Edition Server. We setup a database maintenance (Database Integrity and
> Index, Reorganize data and indexes, etc. ) to run every week. However,
> the report shows that the maintenance job did not run because the database
> needs to be in a single user mode. How do you setup a single user mode to
> run the database maintenance? When the database maintenance is done, how
> do you remove the single user mode? Thanks.
>|||Thanks very much for your prompt response, Geoff.
If I run DBCC CHECKDB manually, what kind of problems do you expect that I
will encounter? Normally, are these problems easy to fix? Thanks.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23h63CRMOGHA.2268@.TK2MSFTNGP09.phx.gbl...
> Uncheck the "Attempt to repair minor problems" checkbox. All this does is
> hide problems until they become unfixable.
> Run DBCC CHECKDB manually. You will find you have a problem. Fix the
> problem. You will likely have to take the system offline to fix it.
> Database corruption issues are very often due to disk subsystem failures.
> Check on that and repair if necessary.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:ubtudFMOGHA.2128@.TK2MSFTNGP10.phx.gbl...
>> We are running SQL 2000 Enterprise Edition on Windows 2003 Standard
>> Edition Server. We setup a database maintenance (Database Integrity and
>> Index, Reorganize data and indexes, etc. ) to run every week. However,
>> the report shows that the maintenance job did not run because the
>> database needs to be in a single user mode. How do you setup a single
>> user mode to run the database maintenance? When the database maintenance
>> is done, how do you remove the single user mode? Thanks.
>|||How do you take the system offline to fix a problem?
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23h63CRMOGHA.2268@.TK2MSFTNGP09.phx.gbl...
> Uncheck the "Attempt to repair minor problems" checkbox. All this does is
> hide problems until they become unfixable.
> Run DBCC CHECKDB manually. You will find you have a problem. Fix the
> problem. You will likely have to take the system offline to fix it.
> Database corruption issues are very often due to disk subsystem failures.
> Check on that and repair if necessary.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:ubtudFMOGHA.2128@.TK2MSFTNGP10.phx.gbl...
>> We are running SQL 2000 Enterprise Edition on Windows 2003 Standard
>> Edition Server. We setup a database maintenance (Database Integrity and
>> Index, Reorganize data and indexes, etc. ) to run every week. However,
>> the report shows that the maintenance job did not run because the
>> database needs to be in a single user mode. How do you setup a single
>> user mode to run the database maintenance? When the database maintenance
>> is done, how do you remove the single user mode? Thanks.
>|||Answering both responses.
I don't know what kinds of problems you will find. You can run DBCC CHECKDB
anytime, but it does create a significant load on the system. I would do it
at a low activity time. To do a repair, you first have to take the database
into single-user mode. Both topics are covered in BOL under DBCC CHECKDB
and DBCC DBREPAIR. I suggest you do a LOT of reading before trying
anything.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:OLSGtcMOGHA.3924@.TK2MSFTNGP14.phx.gbl...
> How do you take the system offline to fix a problem?
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:%23h63CRMOGHA.2268@.TK2MSFTNGP09.phx.gbl...
>> Uncheck the "Attempt to repair minor problems" checkbox. All this does
>> is hide problems until they become unfixable.
>> Run DBCC CHECKDB manually. You will find you have a problem. Fix the
>> problem. You will likely have to take the system offline to fix it.
>> Database corruption issues are very often due to disk subsystem failures.
>> Check on that and repair if necessary.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>>
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:ubtudFMOGHA.2128@.TK2MSFTNGP10.phx.gbl...
>> We are running SQL 2000 Enterprise Edition on Windows 2003 Standard
>> Edition Server. We setup a database maintenance (Database Integrity and
>> Index, Reorganize data and indexes, etc. ) to run every week. However,
>> the report shows that the maintenance job did not run because the
>> database needs to be in a single user mode. How do you setup a single
>> user mode to run the database maintenance? When the database
>> maintenance is done, how do you remove the single user mode? Thanks.
>>
>|||Thanks very much for your prompt response, Geoff.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OKBZgjMOGHA.3576@.TK2MSFTNGP15.phx.gbl...
> Answering both responses.
> I don't know what kinds of problems you will find. You can run DBCC
> CHECKDB anytime, but it does create a significant load on the system. I
> would do it at a low activity time. To do a repair, you first have to
> take the database into single-user mode. Both topics are covered in BOL
> under DBCC CHECKDB and DBCC DBREPAIR. I suggest you do a LOT of reading
> before trying anything.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:OLSGtcMOGHA.3924@.TK2MSFTNGP14.phx.gbl...
>> How do you take the system offline to fix a problem?
>> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
>> news:%23h63CRMOGHA.2268@.TK2MSFTNGP09.phx.gbl...
>> Uncheck the "Attempt to repair minor problems" checkbox. All this does
>> is hide problems until they become unfixable.
>> Run DBCC CHECKDB manually. You will find you have a problem. Fix the
>> problem. You will likely have to take the system offline to fix it.
>> Database corruption issues are very often due to disk subsystem
>> failures. Check on that and repair if necessary.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>>
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:ubtudFMOGHA.2128@.TK2MSFTNGP10.phx.gbl...
>> We are running SQL 2000 Enterprise Edition on Windows 2003 Standard
>> Edition Server. We setup a database maintenance (Database Integrity
>> and Index, Reorganize data and indexes, etc. ) to run every week.
>> However, the report shows that the maintenance job did not run because
>> the database needs to be in a single user mode. How do you setup a
>> single user mode to run the database maintenance? When the database
>> maintenance is done, how do you remove the single user mode? Thanks.
>>
>>
>
Server. We setup a database maintenance (Database Integrity and Index,
Reorganize data and indexes, etc. ) to run every week. However, the report
shows that the maintenance job did not run because the database needs to be
in a single user mode. How do you setup a single user mode to run the
database maintenance? When the database maintenance is done, how do you
remove the single user mode? Thanks.Uncheck the "Attempt to repair minor problems" checkbox. All this does is
hide problems until they become unfixable.
Run DBCC CHECKDB manually. You will find you have a problem. Fix the
problem. You will likely have to take the system offline to fix it.
Database corruption issues are very often due to disk subsystem failures.
Check on that and repair if necessary.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:ubtudFMOGHA.2128@.TK2MSFTNGP10.phx.gbl...
> We are running SQL 2000 Enterprise Edition on Windows 2003 Standard
> Edition Server. We setup a database maintenance (Database Integrity and
> Index, Reorganize data and indexes, etc. ) to run every week. However,
> the report shows that the maintenance job did not run because the database
> needs to be in a single user mode. How do you setup a single user mode to
> run the database maintenance? When the database maintenance is done, how
> do you remove the single user mode? Thanks.
>|||Thanks very much for your prompt response, Geoff.
If I run DBCC CHECKDB manually, what kind of problems do you expect that I
will encounter? Normally, are these problems easy to fix? Thanks.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23h63CRMOGHA.2268@.TK2MSFTNGP09.phx.gbl...
> Uncheck the "Attempt to repair minor problems" checkbox. All this does is
> hide problems until they become unfixable.
> Run DBCC CHECKDB manually. You will find you have a problem. Fix the
> problem. You will likely have to take the system offline to fix it.
> Database corruption issues are very often due to disk subsystem failures.
> Check on that and repair if necessary.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:ubtudFMOGHA.2128@.TK2MSFTNGP10.phx.gbl...
>> We are running SQL 2000 Enterprise Edition on Windows 2003 Standard
>> Edition Server. We setup a database maintenance (Database Integrity and
>> Index, Reorganize data and indexes, etc. ) to run every week. However,
>> the report shows that the maintenance job did not run because the
>> database needs to be in a single user mode. How do you setup a single
>> user mode to run the database maintenance? When the database maintenance
>> is done, how do you remove the single user mode? Thanks.
>|||How do you take the system offline to fix a problem?
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23h63CRMOGHA.2268@.TK2MSFTNGP09.phx.gbl...
> Uncheck the "Attempt to repair minor problems" checkbox. All this does is
> hide problems until they become unfixable.
> Run DBCC CHECKDB manually. You will find you have a problem. Fix the
> problem. You will likely have to take the system offline to fix it.
> Database corruption issues are very often due to disk subsystem failures.
> Check on that and repair if necessary.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:ubtudFMOGHA.2128@.TK2MSFTNGP10.phx.gbl...
>> We are running SQL 2000 Enterprise Edition on Windows 2003 Standard
>> Edition Server. We setup a database maintenance (Database Integrity and
>> Index, Reorganize data and indexes, etc. ) to run every week. However,
>> the report shows that the maintenance job did not run because the
>> database needs to be in a single user mode. How do you setup a single
>> user mode to run the database maintenance? When the database maintenance
>> is done, how do you remove the single user mode? Thanks.
>|||Answering both responses.
I don't know what kinds of problems you will find. You can run DBCC CHECKDB
anytime, but it does create a significant load on the system. I would do it
at a low activity time. To do a repair, you first have to take the database
into single-user mode. Both topics are covered in BOL under DBCC CHECKDB
and DBCC DBREPAIR. I suggest you do a LOT of reading before trying
anything.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:OLSGtcMOGHA.3924@.TK2MSFTNGP14.phx.gbl...
> How do you take the system offline to fix a problem?
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:%23h63CRMOGHA.2268@.TK2MSFTNGP09.phx.gbl...
>> Uncheck the "Attempt to repair minor problems" checkbox. All this does
>> is hide problems until they become unfixable.
>> Run DBCC CHECKDB manually. You will find you have a problem. Fix the
>> problem. You will likely have to take the system offline to fix it.
>> Database corruption issues are very often due to disk subsystem failures.
>> Check on that and repair if necessary.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>>
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:ubtudFMOGHA.2128@.TK2MSFTNGP10.phx.gbl...
>> We are running SQL 2000 Enterprise Edition on Windows 2003 Standard
>> Edition Server. We setup a database maintenance (Database Integrity and
>> Index, Reorganize data and indexes, etc. ) to run every week. However,
>> the report shows that the maintenance job did not run because the
>> database needs to be in a single user mode. How do you setup a single
>> user mode to run the database maintenance? When the database
>> maintenance is done, how do you remove the single user mode? Thanks.
>>
>|||Thanks very much for your prompt response, Geoff.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OKBZgjMOGHA.3576@.TK2MSFTNGP15.phx.gbl...
> Answering both responses.
> I don't know what kinds of problems you will find. You can run DBCC
> CHECKDB anytime, but it does create a significant load on the system. I
> would do it at a low activity time. To do a repair, you first have to
> take the database into single-user mode. Both topics are covered in BOL
> under DBCC CHECKDB and DBCC DBREPAIR. I suggest you do a LOT of reading
> before trying anything.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:OLSGtcMOGHA.3924@.TK2MSFTNGP14.phx.gbl...
>> How do you take the system offline to fix a problem?
>> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
>> news:%23h63CRMOGHA.2268@.TK2MSFTNGP09.phx.gbl...
>> Uncheck the "Attempt to repair minor problems" checkbox. All this does
>> is hide problems until they become unfixable.
>> Run DBCC CHECKDB manually. You will find you have a problem. Fix the
>> problem. You will likely have to take the system offline to fix it.
>> Database corruption issues are very often due to disk subsystem
>> failures. Check on that and repair if necessary.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>>
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:ubtudFMOGHA.2128@.TK2MSFTNGP10.phx.gbl...
>> We are running SQL 2000 Enterprise Edition on Windows 2003 Standard
>> Edition Server. We setup a database maintenance (Database Integrity
>> and Index, Reorganize data and indexes, etc. ) to run every week.
>> However, the report shows that the maintenance job did not run because
>> the database needs to be in a single user mode. How do you setup a
>> single user mode to run the database maintenance? When the database
>> maintenance is done, how do you remove the single user mode? Thanks.
>>
>>
>
Database Maintenance
We are running SQL 2000 Enterprise Edition on Windows 2003 Standard Edition
Server. We setup a database maintenance (Database Integrity and Index,
Reorganize data and indexes, etc. ) to run every week. However, the report
shows that the maintenance job did not run because the database needs to be
in a single user mode. How do you setup a single user mode to run the
database maintenance? When the database maintenance is done, how do you
remove the single user mode? Thanks.Uncheck the "Attempt to repair minor problems" checkbox. All this does is
hide problems until they become unfixable.
Run DBCC CHECKDB manually. You will find you have a problem. Fix the
problem. You will likely have to take the system offline to fix it.
Database corruption issues are very often due to disk subsystem failures.
Check on that and repair if necessary.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:ubtudFMOGHA.2128@.TK2MSFTNGP10.phx.gbl...
> We are running SQL 2000 Enterprise Edition on Windows 2003 Standard
> Edition Server. We setup a database maintenance (Database Integrity and
> Index, Reorganize data and indexes, etc. ) to run every week. However,
> the report shows that the maintenance job did not run because the database
> needs to be in a single user mode. How do you setup a single user mode to
> run the database maintenance? When the database maintenance is done, how
> do you remove the single user mode? Thanks.
>|||Thanks very much for your prompt response, Geoff.
If I run DBCC CHECKDB manually, what kind of problems do you expect that I
will encounter? Normally, are these problems easy to fix? Thanks.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23h63CRMOGHA.2268@.TK2MSFTNGP09.phx.gbl...
> Uncheck the "Attempt to repair minor problems" checkbox. All this does is
> hide problems until they become unfixable.
> Run DBCC CHECKDB manually. You will find you have a problem. Fix the
> problem. You will likely have to take the system offline to fix it.
> Database corruption issues are very often due to disk subsystem failures.
> Check on that and repair if necessary.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:ubtudFMOGHA.2128@.TK2MSFTNGP10.phx.gbl...
>|||How do you take the system offline to fix a problem?
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23h63CRMOGHA.2268@.TK2MSFTNGP09.phx.gbl...
> Uncheck the "Attempt to repair minor problems" checkbox. All this does is
> hide problems until they become unfixable.
> Run DBCC CHECKDB manually. You will find you have a problem. Fix the
> problem. You will likely have to take the system offline to fix it.
> Database corruption issues are very often due to disk subsystem failures.
> Check on that and repair if necessary.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:ubtudFMOGHA.2128@.TK2MSFTNGP10.phx.gbl...
>|||Answering both responses.
I don't know what kinds of problems you will find. You can run DBCC CHECKDB
anytime, but it does create a significant load on the system. I would do it
at a low activity time. To do a repair, you first have to take the database
into single-user mode. Both topics are covered in BOL under DBCC CHECKDB
and DBCC DBREPAIR. I suggest you do a LOT of reading before trying
anything.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:OLSGtcMOGHA.3924@.TK2MSFTNGP14.phx.gbl...
> How do you take the system offline to fix a problem?
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:%23h63CRMOGHA.2268@.TK2MSFTNGP09.phx.gbl...
>|||Thanks very much for your prompt response, Geoff.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OKBZgjMOGHA.3576@.TK2MSFTNGP15.phx.gbl...
> Answering both responses.
> I don't know what kinds of problems you will find. You can run DBCC
> CHECKDB anytime, but it does create a significant load on the system. I
> would do it at a low activity time. To do a repair, you first have to
> take the database into single-user mode. Both topics are covered in BOL
> under DBCC CHECKDB and DBCC DBREPAIR. I suggest you do a LOT of reading
> before trying anything.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:OLSGtcMOGHA.3924@.TK2MSFTNGP14.phx.gbl...
>
Server. We setup a database maintenance (Database Integrity and Index,
Reorganize data and indexes, etc. ) to run every week. However, the report
shows that the maintenance job did not run because the database needs to be
in a single user mode. How do you setup a single user mode to run the
database maintenance? When the database maintenance is done, how do you
remove the single user mode? Thanks.Uncheck the "Attempt to repair minor problems" checkbox. All this does is
hide problems until they become unfixable.
Run DBCC CHECKDB manually. You will find you have a problem. Fix the
problem. You will likely have to take the system offline to fix it.
Database corruption issues are very often due to disk subsystem failures.
Check on that and repair if necessary.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:ubtudFMOGHA.2128@.TK2MSFTNGP10.phx.gbl...
> We are running SQL 2000 Enterprise Edition on Windows 2003 Standard
> Edition Server. We setup a database maintenance (Database Integrity and
> Index, Reorganize data and indexes, etc. ) to run every week. However,
> the report shows that the maintenance job did not run because the database
> needs to be in a single user mode. How do you setup a single user mode to
> run the database maintenance? When the database maintenance is done, how
> do you remove the single user mode? Thanks.
>|||Thanks very much for your prompt response, Geoff.
If I run DBCC CHECKDB manually, what kind of problems do you expect that I
will encounter? Normally, are these problems easy to fix? Thanks.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23h63CRMOGHA.2268@.TK2MSFTNGP09.phx.gbl...
> Uncheck the "Attempt to repair minor problems" checkbox. All this does is
> hide problems until they become unfixable.
> Run DBCC CHECKDB manually. You will find you have a problem. Fix the
> problem. You will likely have to take the system offline to fix it.
> Database corruption issues are very often due to disk subsystem failures.
> Check on that and repair if necessary.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:ubtudFMOGHA.2128@.TK2MSFTNGP10.phx.gbl...
>|||How do you take the system offline to fix a problem?
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23h63CRMOGHA.2268@.TK2MSFTNGP09.phx.gbl...
> Uncheck the "Attempt to repair minor problems" checkbox. All this does is
> hide problems until they become unfixable.
> Run DBCC CHECKDB manually. You will find you have a problem. Fix the
> problem. You will likely have to take the system offline to fix it.
> Database corruption issues are very often due to disk subsystem failures.
> Check on that and repair if necessary.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:ubtudFMOGHA.2128@.TK2MSFTNGP10.phx.gbl...
>|||Answering both responses.
I don't know what kinds of problems you will find. You can run DBCC CHECKDB
anytime, but it does create a significant load on the system. I would do it
at a low activity time. To do a repair, you first have to take the database
into single-user mode. Both topics are covered in BOL under DBCC CHECKDB
and DBCC DBREPAIR. I suggest you do a LOT of reading before trying
anything.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:OLSGtcMOGHA.3924@.TK2MSFTNGP14.phx.gbl...
> How do you take the system offline to fix a problem?
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:%23h63CRMOGHA.2268@.TK2MSFTNGP09.phx.gbl...
>|||Thanks very much for your prompt response, Geoff.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OKBZgjMOGHA.3576@.TK2MSFTNGP15.phx.gbl...
> Answering both responses.
> I don't know what kinds of problems you will find. You can run DBCC
> CHECKDB anytime, but it does create a significant load on the system. I
> would do it at a low activity time. To do a repair, you first have to
> take the database into single-user mode. Both topics are covered in BOL
> under DBCC CHECKDB and DBCC DBREPAIR. I suggest you do a LOT of reading
> before trying anything.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:OLSGtcMOGHA.3924@.TK2MSFTNGP14.phx.gbl...
>
Subscribe to:
Posts (Atom)