Hi All,
I have a database that has not recieved any type of maintenance apart from
daily backups. It has grown to over 30GB. I created some jobs to reindex
tables that I thought were high activity. Query performance increased
substantially but I believe it could be better. Database has also shruck
about 4GB.
After running a showcontig on the table, I could still see lots of
fragmentation on tables that were not included in the job above. So I
created a DB Maintenance Plan to for only optimizations and chose to
reorganize data and index pages, and keep original fillfactor that the index
was created with.
The job ran last night but did not finish because it failed on an unknown
step after 27 mins. I ran this job manually and it ran for 50 mins. before
I
manually stopped the job. The actual database space decreased another 7GB.
The transaction log backup could not be performed becuase there was not
enough disk space, the trans log was 23GB.
My questions are:
How can I enure this optimization job will successfully run in the future?
Is there a way to make the trans log smaller after the optimization job is
run?
Should I be doing a Full DB Backup AFTER the optimization job?
Can I shrink the allocated size of the database by adding to the
optimization job to "remove unused space from database files" safely? I wan
t
to ensure the database will still grow automatically if space is required.
Cheers!First off you should undo the shrink part of the maintenance plan. When you
reindex the tables you need lots of free space in the data and log files in
order for it to do it's job properly. If there isn't it has to grow the
files which is resource intensive. That process will leave free space which
the shrink part of the maintenance plan will not like and then it will
shrink the files. The shrinking process will fragment your tables and
indexes all over again. See here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
You should leave the size at what ever it needs to be to do what it has to
do. The exception would be the log file. If it just keeps growing you
probably aren't backing up the log files on a regular basis. Full backups
are not enough if you are in the full recovery mode.
Andrew J. Kelly SQL MVP
"AKing" <AKing@.discussions.microsoft.com> wrote in message
news:F9CBB7CC-A53C-498A-A678-E974558EF4C9@.microsoft.com...
> Hi All,
> I have a database that has not recieved any type of maintenance apart from
> daily backups. It has grown to over 30GB. I created some jobs to reindex
> tables that I thought were high activity. Query performance increased
> substantially but I believe it could be better. Database has also shruck
> about 4GB.
> After running a showcontig on the table, I could still see lots of
> fragmentation on tables that were not included in the job above. So I
> created a DB Maintenance Plan to for only optimizations and chose to
> reorganize data and index pages, and keep original fillfactor that the
> index
> was created with.
> The job ran last night but did not finish because it failed on an unknown
> step after 27 mins. I ran this job manually and it ran for 50 mins.
> before I
> manually stopped the job. The actual database space decreased another
> 7GB.
> The transaction log backup could not be performed becuase there was not
> enough disk space, the trans log was 23GB.
> My questions are:
> How can I enure this optimization job will successfully run in the future?
> Is there a way to make the trans log smaller after the optimization job
> is
> run?
> Should I be doing a Full DB Backup AFTER the optimization job?
> Can I shrink the allocated size of the database by adding to the
> optimization job to "remove unused space from database files" safely? I
> want
> to ensure the database will still grow automatically if space is required.
> Cheers!|||Thank you for your reply. You have provided some helpful insight.
I tried to run the optimization job (w/out shrinking db) and I got the same
behaviour. I had to cancel the job after it had been running for 50 minutes
.
Once again the translog was 21GB.
I understand that the reindex job can be intensive, this wil only multiply
if the translog grows from its usual size (~1gb) to 21GB.
Is there a way to run this job w/out the side effect above?
Will the translogs always be this big when running the optimization?
Maybe the only way is to have nightly reindex of portions of the database
until it has reindexed the entire database.
Cheers
"Andrew J. Kelly" wrote:
> First off you should undo the shrink part of the maintenance plan. When y
ou
> reindex the tables you need lots of free space in the data and log files i
n
> order for it to do it's job properly. If there isn't it has to grow the
> files which is resource intensive. That process will leave free space whi
ch
> the shrink part of the maintenance plan will not like and then it will
> shrink the files. The shrinking process will fragment your tables and
> indexes all over again. See here:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> You should leave the size at what ever it needs to be to do what it has to
> do. The exception would be the log file. If it just keeps growing you
> probably aren't backing up the log files on a regular basis. Full backups
> are not enough if you are in the full recovery mode.
> --
> Andrew J. Kelly SQL MVP
>
> "AKing" <AKing@.discussions.microsoft.com> wrote in message
> news:F9CBB7CC-A53C-498A-A678-E974558EF4C9@.microsoft.com...
>
>|||How large are the tables in the db? You might want to skip the actual
maintenance plan and just create a job with DBCC DBREINDEX instead to just
rebuild the ones that are fragmented above a certain level. Or just do a
portion of them each night. There is a script under DBCC SHOWCONTIG in
BooksOnLine to allow you to choose which tables will be reindexed or even
defragged.
Andrew J. Kelly SQL MVP
"AKing" <AKing@.discussions.microsoft.com> wrote in message
news:F06D716A-0ECA-4EDE-8EBD-547715C543CC@.microsoft.com...
> Thank you for your reply. You have provided some helpful insight.
> I tried to run the optimization job (w/out shrinking db) and I got the
> same
> behaviour. I had to cancel the job after it had been running for 50
> minutes.
> Once again the translog was 21GB.
> I understand that the reindex job can be intensive, this wil only multiply
> if the translog grows from its usual size (~1gb) to 21GB.
> Is there a way to run this job w/out the side effect above?
> Will the translogs always be this big when running the optimization?
> Maybe the only way is to have nightly reindex of portions of the database
> until it has reindexed the entire database.
> Cheers
>
> "Andrew J. Kelly" wrote:
>|||Hi Andrew,
The database allocation is ~36GB and at one time only had 50MB of free
space. Although through reindexing some high activity tables I have managed
to reduce the actual size of the database to ~25GB. I plan to shrink some o
f
the free space as I believe it is space wasted.
"Andrew J. Kelly" wrote:
> How large are the tables in the db? You might want to skip the actual
> maintenance plan and just create a job with DBCC DBREINDEX instead to just
> rebuild the ones that are fragmented above a certain level. Or just do a
> portion of them each night. There is a script under DBCC SHOWCONTIG in
> BooksOnLine to allow you to choose which tables will be reindexed or even
> defragged.
> --
> Andrew J. Kelly SQL MVP
>
> "AKing" <AKing@.discussions.microsoft.com> wrote in message
> news:F06D716A-0ECA-4EDE-8EBD-547715C543CC@.microsoft.com...
>
>
Showing posts with label optimizations. Show all posts
Showing posts with label optimizations. Show all posts
Saturday, February 25, 2012
DB Maintenance/Optimizations
When setting up a DB Maintenance plan, what do users
normally do with the options in the Optimizations tab?
Does it matter what application is using the database?
(In this case, it is Microsoft Business Solutions-
Solomon.) Thanks.It matters if the application is 24 x 7. The optimization routines for the
MP use DBCC DBREINDEX and that will take your table off line for the
duration of the reindexing. If you have a maintenance window for this then
it may not be a problem. I usually suggest creating your own scheduled
jobs that do exactly what you need done and when instead of using the MP.
--
Andrew J. Kelly
SQL Server MVP
"Roger" <anonymous@.discussions.microsoft.com> wrote in message
news:05ca01c3cef5$e589f980$a001280a@.phx.gbl...
> When setting up a DB Maintenance plan, what do users
> normally do with the options in the Optimizations tab?
> Does it matter what application is using the database?
> (In this case, it is Microsoft Business Solutions-
> Solomon.) Thanks.|||Is that Maintenance Wizard an inside joke?
Seems to me it's a great way for less-knowledgeable 'dba's' to get bollixed
up.
Other than that, it's like your appendix - it's there but it serves no
useful purpose.
James Hokes
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23fn3IwxzDHA.3900@.tk2msftngp13.phx.gbl...
> It matters if the application is 24 x 7. The optimization routines for
the
> MP use DBCC DBREINDEX and that will take your table off line for the
> duration of the reindexing. If you have a maintenance window for this
then
> it may not be a problem. I usually suggest creating your own scheduled
> jobs that do exactly what you need done and when instead of using the MP.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Roger" <anonymous@.discussions.microsoft.com> wrote in message
> news:05ca01c3cef5$e589f980$a001280a@.phx.gbl...
> > When setting up a DB Maintenance plan, what do users
> > normally do with the options in the Optimizations tab?
> > Does it matter what application is using the database?
> > (In this case, it is Microsoft Business Solutions-
> > Solomon.) Thanks.
>|||That's what I think the biggest problem of using the Maintenance Wizard is.
When someone new to sqls erver uses the wizard to get going they have no
knowledge of what is actually happening. So if something does go wrong they
are not in a good position to fix it. If they took a few minutes to learn
how to create their own job to do the task they are much more likely to have
a better handle on things overall.
--
Andrew J. Kelly
SQL Server MVP
"James Hokes" <noemail@.noway.com> wrote in message
news:eUkRxb1zDHA.2872@.TK2MSFTNGP09.phx.gbl...
> Is that Maintenance Wizard an inside joke?
> Seems to me it's a great way for less-knowledgeable 'dba's' to get
bollixed
> up.
> Other than that, it's like your appendix - it's there but it serves no
> useful purpose.
> James Hokes
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23fn3IwxzDHA.3900@.tk2msftngp13.phx.gbl...
> > It matters if the application is 24 x 7. The optimization routines for
> the
> > MP use DBCC DBREINDEX and that will take your table off line for the
> > duration of the reindexing. If you have a maintenance window for this
> then
> > it may not be a problem. I usually suggest creating your own scheduled
> > jobs that do exactly what you need done and when instead of using the
MP.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Roger" <anonymous@.discussions.microsoft.com> wrote in message
> > news:05ca01c3cef5$e589f980$a001280a@.phx.gbl...
> > > When setting up a DB Maintenance plan, what do users
> > > normally do with the options in the Optimizations tab?
> > > Does it matter what application is using the database?
> > > (In this case, it is Microsoft Business Solutions-
> > > Solomon.) Thanks.
> >
> >
>
normally do with the options in the Optimizations tab?
Does it matter what application is using the database?
(In this case, it is Microsoft Business Solutions-
Solomon.) Thanks.It matters if the application is 24 x 7. The optimization routines for the
MP use DBCC DBREINDEX and that will take your table off line for the
duration of the reindexing. If you have a maintenance window for this then
it may not be a problem. I usually suggest creating your own scheduled
jobs that do exactly what you need done and when instead of using the MP.
--
Andrew J. Kelly
SQL Server MVP
"Roger" <anonymous@.discussions.microsoft.com> wrote in message
news:05ca01c3cef5$e589f980$a001280a@.phx.gbl...
> When setting up a DB Maintenance plan, what do users
> normally do with the options in the Optimizations tab?
> Does it matter what application is using the database?
> (In this case, it is Microsoft Business Solutions-
> Solomon.) Thanks.|||Is that Maintenance Wizard an inside joke?
Seems to me it's a great way for less-knowledgeable 'dba's' to get bollixed
up.
Other than that, it's like your appendix - it's there but it serves no
useful purpose.
James Hokes
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23fn3IwxzDHA.3900@.tk2msftngp13.phx.gbl...
> It matters if the application is 24 x 7. The optimization routines for
the
> MP use DBCC DBREINDEX and that will take your table off line for the
> duration of the reindexing. If you have a maintenance window for this
then
> it may not be a problem. I usually suggest creating your own scheduled
> jobs that do exactly what you need done and when instead of using the MP.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Roger" <anonymous@.discussions.microsoft.com> wrote in message
> news:05ca01c3cef5$e589f980$a001280a@.phx.gbl...
> > When setting up a DB Maintenance plan, what do users
> > normally do with the options in the Optimizations tab?
> > Does it matter what application is using the database?
> > (In this case, it is Microsoft Business Solutions-
> > Solomon.) Thanks.
>|||That's what I think the biggest problem of using the Maintenance Wizard is.
When someone new to sqls erver uses the wizard to get going they have no
knowledge of what is actually happening. So if something does go wrong they
are not in a good position to fix it. If they took a few minutes to learn
how to create their own job to do the task they are much more likely to have
a better handle on things overall.
--
Andrew J. Kelly
SQL Server MVP
"James Hokes" <noemail@.noway.com> wrote in message
news:eUkRxb1zDHA.2872@.TK2MSFTNGP09.phx.gbl...
> Is that Maintenance Wizard an inside joke?
> Seems to me it's a great way for less-knowledgeable 'dba's' to get
bollixed
> up.
> Other than that, it's like your appendix - it's there but it serves no
> useful purpose.
> James Hokes
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23fn3IwxzDHA.3900@.tk2msftngp13.phx.gbl...
> > It matters if the application is 24 x 7. The optimization routines for
> the
> > MP use DBCC DBREINDEX and that will take your table off line for the
> > duration of the reindexing. If you have a maintenance window for this
> then
> > it may not be a problem. I usually suggest creating your own scheduled
> > jobs that do exactly what you need done and when instead of using the
MP.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Roger" <anonymous@.discussions.microsoft.com> wrote in message
> > news:05ca01c3cef5$e589f980$a001280a@.phx.gbl...
> > > When setting up a DB Maintenance plan, what do users
> > > normally do with the options in the Optimizations tab?
> > > Does it matter what application is using the database?
> > > (In this case, it is Microsoft Business Solutions-
> > > Solomon.) Thanks.
> >
> >
>
Friday, February 24, 2012
db maintanence plan failure
Hi all!
I have a DB Maintanence plan configured to run once a week that is supposed
to perform optimizations and integrity checks on all user databases. This is
split into 2 seperate jobs when I look at the Jobs node under SQL Agent. The
optimizations is set to run at 4 am & the integrity at 5 am. It appears that
the integrity check job keeps failing. The log says something about
'mosesdb' needs to be in single user mode. Any ideas? The optimization jobs
seems to complete OK
TIA!Param
Under Tab 'Integrity' uncheck 'Attempt to repair any minor problems'
SQL Server is trying to repair some problems but it requires database to be
in single user mode.
"Param R." <pr@.nospam.com> wrote in message
news:ed%23BifjEFHA.1012@.TK2MSFTNGP14.phx.gbl...
> Hi all!
> I have a DB Maintanence plan configured to run once a week that is
supposed
> to perform optimizations and integrity checks on all user databases. This
is
> split into 2 seperate jobs when I look at the Jobs node under SQL Agent.
The
> optimizations is set to run at 4 am & the integrity at 5 am. It appears
that
> the integrity check job keeps failing. The log says something about
> 'mosesdb' needs to be in single user mode. Any ideas? The optimization
jobs
> seems to complete OK
> TIA!
>|||OK. But wouldnt I want it to fix any problems? What is the best way then?
thanks!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eSii%23klEFHA.2032@.tk2msftngp13.phx.gbl...
> Param
> Under Tab 'Integrity' uncheck 'Attempt to repair any minor problems'
> SQL Server is trying to repair some problems but it requires database to
> be
> in single user mode.
>
> "Param R." <pr@.nospam.com> wrote in message
> news:ed%23BifjEFHA.1012@.TK2MSFTNGP14.phx.gbl...
>> Hi all!
>> I have a DB Maintanence plan configured to run once a week that is
> supposed
>> to perform optimizations and integrity checks on all user databases. This
> is
>> split into 2 seperate jobs when I look at the Jobs node under SQL Agent.
> The
>> optimizations is set to run at 4 am & the integrity at 5 am. It appears
> that
>> the integrity check job keeps failing. The log says something about
>> 'mosesdb' needs to be in single user mode. Any ideas? The optimization
> jobs
>> seems to complete OK
>> TIA!
>>
>|||If my car keep breaking down, I'd like to know why that happen so I can avoid it keeping breaking
down when I travel at high speed. I.e., if you get such problem, you want to do root-cause analysis
why they happen. Often it is because hardware problems.
In other words, the option in maint wiz is IMO not well thought through, and AFAIK, it will be
removed in next version of SQL Server. The option, btw makes maint wiz execute DBCC CHECKDB using
the REPAIR_FAST option.
To be able to run DBCC CHECKDB with FAST_REPAIR, the database must be in single user mode, so if you
have users in the database, the command and subsequently job will fail.
Also, you might want to check out: http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Param R." <pr@.nospam.com> wrote in message news:%23Czx7SqEFHA.1188@.tk2msftngp13.phx.gbl...
> OK. But wouldnt I want it to fix any problems? What is the best way then?
> thanks!
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:eSii%23klEFHA.2032@.tk2msftngp13.phx.gbl...
>> Param
>> Under Tab 'Integrity' uncheck 'Attempt to repair any minor problems'
>> SQL Server is trying to repair some problems but it requires database to be
>> in single user mode.
>>
>> "Param R." <pr@.nospam.com> wrote in message
>> news:ed%23BifjEFHA.1012@.TK2MSFTNGP14.phx.gbl...
>> Hi all!
>> I have a DB Maintanence plan configured to run once a week that is
>> supposed
>> to perform optimizations and integrity checks on all user databases. This
>> is
>> split into 2 seperate jobs when I look at the Jobs node under SQL Agent.
>> The
>> optimizations is set to run at 4 am & the integrity at 5 am. It appears
>> that
>> the integrity check job keeps failing. The log says something about
>> 'mosesdb' needs to be in single user mode. Any ideas? The optimization
>> jobs
>> seems to complete OK
>> TIA!
>>
>>
>|||I agree I need to know what is causing the problem. But how can I find that
out? The database seems to appear functional from the application
perspective. How can I determine if it is corrupt and if so what is causing
it to be corrupt? The link below gives me some info, but not all.
thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e20IkmqEFHA.1264@.TK2MSFTNGP12.phx.gbl...
> If my car keep breaking down, I'd like to know why that happen so I can
> avoid it keeping breaking down when I travel at high speed. I.e., if you
> get such problem, you want to do root-cause analysis why they happen.
> Often it is because hardware problems.
> In other words, the option in maint wiz is IMO not well thought through,
> and AFAIK, it will be removed in next version of SQL Server. The option,
> btw makes maint wiz execute DBCC CHECKDB using the REPAIR_FAST option.
> To be able to run DBCC CHECKDB with FAST_REPAIR, the database must be in
> single user mode, so if you have users in the database, the command and
> subsequently job will fail.
> Also, you might want to check out:
> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Param R." <pr@.nospam.com> wrote in message
> news:%23Czx7SqEFHA.1188@.tk2msftngp13.phx.gbl...
>> OK. But wouldnt I want it to fix any problems? What is the best way then?
>> thanks!
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:eSii%23klEFHA.2032@.tk2msftngp13.phx.gbl...
>> Param
>> Under Tab 'Integrity' uncheck 'Attempt to repair any minor problems'
>> SQL Server is trying to repair some problems but it requires database
>> to be
>> in single user mode.
>>
>> "Param R." <pr@.nospam.com> wrote in message
>> news:ed%23BifjEFHA.1012@.TK2MSFTNGP14.phx.gbl...
>> Hi all!
>> I have a DB Maintanence plan configured to run once a week that is
>> supposed
>> to perform optimizations and integrity checks on all user databases.
>> This
>> is
>> split into 2 seperate jobs when I look at the Jobs node under SQL
>> Agent.
>> The
>> optimizations is set to run at 4 am & the integrity at 5 am. It appears
>> that
>> the integrity check job keeps failing. The log says something about
>> 'mosesdb' needs to be in single user mode. Any ideas? The optimization
>> jobs
>> seems to complete OK
>> TIA!
>>
>>
>>
>|||Maint wiz has an option to create a report file for each execution. Here you will find the error
messages.
In your situation, the problem is most likely not corruption. The problem is that maint wiz is
trying to set the database in single user mode (because of that option is checked), and you have
users connected to the database. Uncheck the option, and that problem will go away.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Param R." <pr@.nospam.com> wrote in message news:u5DxcdrEFHA.2232@.TK2MSFTNGP14.phx.gbl...
>I agree I need to know what is causing the problem. But how can I find that out? The database seems
>to appear functional from the application perspective. How can I determine if it is corrupt and if
>so what is causing it to be corrupt? The link below gives me some info, but not all.
> thanks!
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:e20IkmqEFHA.1264@.TK2MSFTNGP12.phx.gbl...
>> If my car keep breaking down, I'd like to know why that happen so I can avoid it keeping breaking
>> down when I travel at high speed. I.e., if you get such problem, you want to do root-cause
>> analysis why they happen. Often it is because hardware problems.
>> In other words, the option in maint wiz is IMO not well thought through, and AFAIK, it will be
>> removed in next version of SQL Server. The option, btw makes maint wiz execute DBCC CHECKDB using
>> the REPAIR_FAST option.
>> To be able to run DBCC CHECKDB with FAST_REPAIR, the database must be in single user mode, so if
>> you have users in the database, the command and subsequently job will fail.
>> Also, you might want to check out: http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Param R." <pr@.nospam.com> wrote in message news:%23Czx7SqEFHA.1188@.tk2msftngp13.phx.gbl...
>> OK. But wouldnt I want it to fix any problems? What is the best way then?
>> thanks!
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:eSii%23klEFHA.2032@.tk2msftngp13.phx.gbl...
>> Param
>> Under Tab 'Integrity' uncheck 'Attempt to repair any minor problems'
>> SQL Server is trying to repair some problems but it requires database to be
>> in single user mode.
>>
>> "Param R." <pr@.nospam.com> wrote in message
>> news:ed%23BifjEFHA.1012@.TK2MSFTNGP14.phx.gbl...
>> Hi all!
>> I have a DB Maintanence plan configured to run once a week that is
>> supposed
>> to perform optimizations and integrity checks on all user databases. This
>> is
>> split into 2 seperate jobs when I look at the Jobs node under SQL Agent.
>> The
>> optimizations is set to run at 4 am & the integrity at 5 am. It appears
>> that
>> the integrity check job keeps failing. The log says something about
>> 'mosesdb' needs to be in single user mode. Any ideas? The optimization
>> jobs
>> seems to complete OK
>> TIA!
>>
>>
>>
>>
>
I have a DB Maintanence plan configured to run once a week that is supposed
to perform optimizations and integrity checks on all user databases. This is
split into 2 seperate jobs when I look at the Jobs node under SQL Agent. The
optimizations is set to run at 4 am & the integrity at 5 am. It appears that
the integrity check job keeps failing. The log says something about
'mosesdb' needs to be in single user mode. Any ideas? The optimization jobs
seems to complete OK
TIA!Param
Under Tab 'Integrity' uncheck 'Attempt to repair any minor problems'
SQL Server is trying to repair some problems but it requires database to be
in single user mode.
"Param R." <pr@.nospam.com> wrote in message
news:ed%23BifjEFHA.1012@.TK2MSFTNGP14.phx.gbl...
> Hi all!
> I have a DB Maintanence plan configured to run once a week that is
supposed
> to perform optimizations and integrity checks on all user databases. This
is
> split into 2 seperate jobs when I look at the Jobs node under SQL Agent.
The
> optimizations is set to run at 4 am & the integrity at 5 am. It appears
that
> the integrity check job keeps failing. The log says something about
> 'mosesdb' needs to be in single user mode. Any ideas? The optimization
jobs
> seems to complete OK
> TIA!
>|||OK. But wouldnt I want it to fix any problems? What is the best way then?
thanks!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eSii%23klEFHA.2032@.tk2msftngp13.phx.gbl...
> Param
> Under Tab 'Integrity' uncheck 'Attempt to repair any minor problems'
> SQL Server is trying to repair some problems but it requires database to
> be
> in single user mode.
>
> "Param R." <pr@.nospam.com> wrote in message
> news:ed%23BifjEFHA.1012@.TK2MSFTNGP14.phx.gbl...
>> Hi all!
>> I have a DB Maintanence plan configured to run once a week that is
> supposed
>> to perform optimizations and integrity checks on all user databases. This
> is
>> split into 2 seperate jobs when I look at the Jobs node under SQL Agent.
> The
>> optimizations is set to run at 4 am & the integrity at 5 am. It appears
> that
>> the integrity check job keeps failing. The log says something about
>> 'mosesdb' needs to be in single user mode. Any ideas? The optimization
> jobs
>> seems to complete OK
>> TIA!
>>
>|||If my car keep breaking down, I'd like to know why that happen so I can avoid it keeping breaking
down when I travel at high speed. I.e., if you get such problem, you want to do root-cause analysis
why they happen. Often it is because hardware problems.
In other words, the option in maint wiz is IMO not well thought through, and AFAIK, it will be
removed in next version of SQL Server. The option, btw makes maint wiz execute DBCC CHECKDB using
the REPAIR_FAST option.
To be able to run DBCC CHECKDB with FAST_REPAIR, the database must be in single user mode, so if you
have users in the database, the command and subsequently job will fail.
Also, you might want to check out: http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Param R." <pr@.nospam.com> wrote in message news:%23Czx7SqEFHA.1188@.tk2msftngp13.phx.gbl...
> OK. But wouldnt I want it to fix any problems? What is the best way then?
> thanks!
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:eSii%23klEFHA.2032@.tk2msftngp13.phx.gbl...
>> Param
>> Under Tab 'Integrity' uncheck 'Attempt to repair any minor problems'
>> SQL Server is trying to repair some problems but it requires database to be
>> in single user mode.
>>
>> "Param R." <pr@.nospam.com> wrote in message
>> news:ed%23BifjEFHA.1012@.TK2MSFTNGP14.phx.gbl...
>> Hi all!
>> I have a DB Maintanence plan configured to run once a week that is
>> supposed
>> to perform optimizations and integrity checks on all user databases. This
>> is
>> split into 2 seperate jobs when I look at the Jobs node under SQL Agent.
>> The
>> optimizations is set to run at 4 am & the integrity at 5 am. It appears
>> that
>> the integrity check job keeps failing. The log says something about
>> 'mosesdb' needs to be in single user mode. Any ideas? The optimization
>> jobs
>> seems to complete OK
>> TIA!
>>
>>
>|||I agree I need to know what is causing the problem. But how can I find that
out? The database seems to appear functional from the application
perspective. How can I determine if it is corrupt and if so what is causing
it to be corrupt? The link below gives me some info, but not all.
thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e20IkmqEFHA.1264@.TK2MSFTNGP12.phx.gbl...
> If my car keep breaking down, I'd like to know why that happen so I can
> avoid it keeping breaking down when I travel at high speed. I.e., if you
> get such problem, you want to do root-cause analysis why they happen.
> Often it is because hardware problems.
> In other words, the option in maint wiz is IMO not well thought through,
> and AFAIK, it will be removed in next version of SQL Server. The option,
> btw makes maint wiz execute DBCC CHECKDB using the REPAIR_FAST option.
> To be able to run DBCC CHECKDB with FAST_REPAIR, the database must be in
> single user mode, so if you have users in the database, the command and
> subsequently job will fail.
> Also, you might want to check out:
> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Param R." <pr@.nospam.com> wrote in message
> news:%23Czx7SqEFHA.1188@.tk2msftngp13.phx.gbl...
>> OK. But wouldnt I want it to fix any problems? What is the best way then?
>> thanks!
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:eSii%23klEFHA.2032@.tk2msftngp13.phx.gbl...
>> Param
>> Under Tab 'Integrity' uncheck 'Attempt to repair any minor problems'
>> SQL Server is trying to repair some problems but it requires database
>> to be
>> in single user mode.
>>
>> "Param R." <pr@.nospam.com> wrote in message
>> news:ed%23BifjEFHA.1012@.TK2MSFTNGP14.phx.gbl...
>> Hi all!
>> I have a DB Maintanence plan configured to run once a week that is
>> supposed
>> to perform optimizations and integrity checks on all user databases.
>> This
>> is
>> split into 2 seperate jobs when I look at the Jobs node under SQL
>> Agent.
>> The
>> optimizations is set to run at 4 am & the integrity at 5 am. It appears
>> that
>> the integrity check job keeps failing. The log says something about
>> 'mosesdb' needs to be in single user mode. Any ideas? The optimization
>> jobs
>> seems to complete OK
>> TIA!
>>
>>
>>
>|||Maint wiz has an option to create a report file for each execution. Here you will find the error
messages.
In your situation, the problem is most likely not corruption. The problem is that maint wiz is
trying to set the database in single user mode (because of that option is checked), and you have
users connected to the database. Uncheck the option, and that problem will go away.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Param R." <pr@.nospam.com> wrote in message news:u5DxcdrEFHA.2232@.TK2MSFTNGP14.phx.gbl...
>I agree I need to know what is causing the problem. But how can I find that out? The database seems
>to appear functional from the application perspective. How can I determine if it is corrupt and if
>so what is causing it to be corrupt? The link below gives me some info, but not all.
> thanks!
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:e20IkmqEFHA.1264@.TK2MSFTNGP12.phx.gbl...
>> If my car keep breaking down, I'd like to know why that happen so I can avoid it keeping breaking
>> down when I travel at high speed. I.e., if you get such problem, you want to do root-cause
>> analysis why they happen. Often it is because hardware problems.
>> In other words, the option in maint wiz is IMO not well thought through, and AFAIK, it will be
>> removed in next version of SQL Server. The option, btw makes maint wiz execute DBCC CHECKDB using
>> the REPAIR_FAST option.
>> To be able to run DBCC CHECKDB with FAST_REPAIR, the database must be in single user mode, so if
>> you have users in the database, the command and subsequently job will fail.
>> Also, you might want to check out: http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Param R." <pr@.nospam.com> wrote in message news:%23Czx7SqEFHA.1188@.tk2msftngp13.phx.gbl...
>> OK. But wouldnt I want it to fix any problems? What is the best way then?
>> thanks!
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:eSii%23klEFHA.2032@.tk2msftngp13.phx.gbl...
>> Param
>> Under Tab 'Integrity' uncheck 'Attempt to repair any minor problems'
>> SQL Server is trying to repair some problems but it requires database to be
>> in single user mode.
>>
>> "Param R." <pr@.nospam.com> wrote in message
>> news:ed%23BifjEFHA.1012@.TK2MSFTNGP14.phx.gbl...
>> Hi all!
>> I have a DB Maintanence plan configured to run once a week that is
>> supposed
>> to perform optimizations and integrity checks on all user databases. This
>> is
>> split into 2 seperate jobs when I look at the Jobs node under SQL Agent.
>> The
>> optimizations is set to run at 4 am & the integrity at 5 am. It appears
>> that
>> the integrity check job keeps failing. The log says something about
>> 'mosesdb' needs to be in single user mode. Any ideas? The optimization
>> jobs
>> seems to complete OK
>> TIA!
>>
>>
>>
>>
>
db maintanence plan failure
Hi all!
I have a DB Maintanence plan configured to run once a week that is supposed
to perform optimizations and integrity checks on all user databases. This is
split into 2 seperate jobs when I look at the Jobs node under SQL Agent. The
optimizations is set to run at 4 am & the integrity at 5 am. It appears that
the integrity check job keeps failing. The log says something about
'mosesdb' needs to be in single user mode. Any ideas? The optimization jobs
seems to complete OK
TIA!
Param
Under Tab 'Integrity' uncheck 'Attempt to repair any minor problems'
SQL Server is trying to repair some problems but it requires database to be
in single user mode.
"Param R." <pr@.nospam.com> wrote in message
news:ed%23BifjEFHA.1012@.TK2MSFTNGP14.phx.gbl...
> Hi all!
> I have a DB Maintanence plan configured to run once a week that is
supposed
> to perform optimizations and integrity checks on all user databases. This
is
> split into 2 seperate jobs when I look at the Jobs node under SQL Agent.
The
> optimizations is set to run at 4 am & the integrity at 5 am. It appears
that
> the integrity check job keeps failing. The log says something about
> 'mosesdb' needs to be in single user mode. Any ideas? The optimization
jobs
> seems to complete OK
> TIA!
>
|||OK. But wouldnt I want it to fix any problems? What is the best way then?
thanks!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eSii%23klEFHA.2032@.tk2msftngp13.phx.gbl...
> Param
> Under Tab 'Integrity' uncheck 'Attempt to repair any minor problems'
> SQL Server is trying to repair some problems but it requires database to
> be
> in single user mode.
>
> "Param R." <pr@.nospam.com> wrote in message
> news:ed%23BifjEFHA.1012@.TK2MSFTNGP14.phx.gbl...
> supposed
> is
> The
> that
> jobs
>
|||If my car keep breaking down, I'd like to know why that happen so I can avoid it keeping breaking
down when I travel at high speed. I.e., if you get such problem, you want to do root-cause analysis
why they happen. Often it is because hardware problems.
In other words, the option in maint wiz is IMO not well thought through, and AFAIK, it will be
removed in next version of SQL Server. The option, btw makes maint wiz execute DBCC CHECKDB using
the REPAIR_FAST option.
To be able to run DBCC CHECKDB with FAST_REPAIR, the database must be in single user mode, so if you
have users in the database, the command and subsequently job will fail.
Also, you might want to check out: http://www.karaszi.com/SQLServer/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Param R." <pr@.nospam.com> wrote in message news:%23Czx7SqEFHA.1188@.tk2msftngp13.phx.gbl...
> OK. But wouldnt I want it to fix any problems? What is the best way then?
> thanks!
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:eSii%23klEFHA.2032@.tk2msftngp13.phx.gbl...
>
|||I agree I need to know what is causing the problem. But how can I find that
out? The database seems to appear functional from the application
perspective. How can I determine if it is corrupt and if so what is causing
it to be corrupt? The link below gives me some info, but not all.
thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e20IkmqEFHA.1264@.TK2MSFTNGP12.phx.gbl...
> If my car keep breaking down, I'd like to know why that happen so I can
> avoid it keeping breaking down when I travel at high speed. I.e., if you
> get such problem, you want to do root-cause analysis why they happen.
> Often it is because hardware problems.
> In other words, the option in maint wiz is IMO not well thought through,
> and AFAIK, it will be removed in next version of SQL Server. The option,
> btw makes maint wiz execute DBCC CHECKDB using the REPAIR_FAST option.
> To be able to run DBCC CHECKDB with FAST_REPAIR, the database must be in
> single user mode, so if you have users in the database, the command and
> subsequently job will fail.
> Also, you might want to check out:
> http://www.karaszi.com/SQLServer/inf...suspect_db.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Param R." <pr@.nospam.com> wrote in message
> news:%23Czx7SqEFHA.1188@.tk2msftngp13.phx.gbl...
>
|||Maint wiz has an option to create a report file for each execution. Here you will find the error
messages.
In your situation, the problem is most likely not corruption. The problem is that maint wiz is
trying to set the database in single user mode (because of that option is checked), and you have
users connected to the database. Uncheck the option, and that problem will go away.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Param R." <pr@.nospam.com> wrote in message news:u5DxcdrEFHA.2232@.TK2MSFTNGP14.phx.gbl...
>I agree I need to know what is causing the problem. But how can I find that out? The database seems
>to appear functional from the application perspective. How can I determine if it is corrupt and if
>so what is causing it to be corrupt? The link below gives me some info, but not all.
> thanks!
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:e20IkmqEFHA.1264@.TK2MSFTNGP12.phx.gbl...
>
I have a DB Maintanence plan configured to run once a week that is supposed
to perform optimizations and integrity checks on all user databases. This is
split into 2 seperate jobs when I look at the Jobs node under SQL Agent. The
optimizations is set to run at 4 am & the integrity at 5 am. It appears that
the integrity check job keeps failing. The log says something about
'mosesdb' needs to be in single user mode. Any ideas? The optimization jobs
seems to complete OK
TIA!
Param
Under Tab 'Integrity' uncheck 'Attempt to repair any minor problems'
SQL Server is trying to repair some problems but it requires database to be
in single user mode.
"Param R." <pr@.nospam.com> wrote in message
news:ed%23BifjEFHA.1012@.TK2MSFTNGP14.phx.gbl...
> Hi all!
> I have a DB Maintanence plan configured to run once a week that is
supposed
> to perform optimizations and integrity checks on all user databases. This
is
> split into 2 seperate jobs when I look at the Jobs node under SQL Agent.
The
> optimizations is set to run at 4 am & the integrity at 5 am. It appears
that
> the integrity check job keeps failing. The log says something about
> 'mosesdb' needs to be in single user mode. Any ideas? The optimization
jobs
> seems to complete OK
> TIA!
>
|||OK. But wouldnt I want it to fix any problems? What is the best way then?
thanks!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eSii%23klEFHA.2032@.tk2msftngp13.phx.gbl...
> Param
> Under Tab 'Integrity' uncheck 'Attempt to repair any minor problems'
> SQL Server is trying to repair some problems but it requires database to
> be
> in single user mode.
>
> "Param R." <pr@.nospam.com> wrote in message
> news:ed%23BifjEFHA.1012@.TK2MSFTNGP14.phx.gbl...
> supposed
> is
> The
> that
> jobs
>
|||If my car keep breaking down, I'd like to know why that happen so I can avoid it keeping breaking
down when I travel at high speed. I.e., if you get such problem, you want to do root-cause analysis
why they happen. Often it is because hardware problems.
In other words, the option in maint wiz is IMO not well thought through, and AFAIK, it will be
removed in next version of SQL Server. The option, btw makes maint wiz execute DBCC CHECKDB using
the REPAIR_FAST option.
To be able to run DBCC CHECKDB with FAST_REPAIR, the database must be in single user mode, so if you
have users in the database, the command and subsequently job will fail.
Also, you might want to check out: http://www.karaszi.com/SQLServer/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Param R." <pr@.nospam.com> wrote in message news:%23Czx7SqEFHA.1188@.tk2msftngp13.phx.gbl...
> OK. But wouldnt I want it to fix any problems? What is the best way then?
> thanks!
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:eSii%23klEFHA.2032@.tk2msftngp13.phx.gbl...
>
|||I agree I need to know what is causing the problem. But how can I find that
out? The database seems to appear functional from the application
perspective. How can I determine if it is corrupt and if so what is causing
it to be corrupt? The link below gives me some info, but not all.
thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e20IkmqEFHA.1264@.TK2MSFTNGP12.phx.gbl...
> If my car keep breaking down, I'd like to know why that happen so I can
> avoid it keeping breaking down when I travel at high speed. I.e., if you
> get such problem, you want to do root-cause analysis why they happen.
> Often it is because hardware problems.
> In other words, the option in maint wiz is IMO not well thought through,
> and AFAIK, it will be removed in next version of SQL Server. The option,
> btw makes maint wiz execute DBCC CHECKDB using the REPAIR_FAST option.
> To be able to run DBCC CHECKDB with FAST_REPAIR, the database must be in
> single user mode, so if you have users in the database, the command and
> subsequently job will fail.
> Also, you might want to check out:
> http://www.karaszi.com/SQLServer/inf...suspect_db.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Param R." <pr@.nospam.com> wrote in message
> news:%23Czx7SqEFHA.1188@.tk2msftngp13.phx.gbl...
>
|||Maint wiz has an option to create a report file for each execution. Here you will find the error
messages.
In your situation, the problem is most likely not corruption. The problem is that maint wiz is
trying to set the database in single user mode (because of that option is checked), and you have
users connected to the database. Uncheck the option, and that problem will go away.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Param R." <pr@.nospam.com> wrote in message news:u5DxcdrEFHA.2232@.TK2MSFTNGP14.phx.gbl...
>I agree I need to know what is causing the problem. But how can I find that out? The database seems
>to appear functional from the application perspective. How can I determine if it is corrupt and if
>so what is causing it to be corrupt? The link below gives me some info, but not all.
> thanks!
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:e20IkmqEFHA.1264@.TK2MSFTNGP12.phx.gbl...
>
Sunday, February 19, 2012
db maintanence plan failure
Hi all!
I have a DB Maintanence plan configured to run once a week that is supposed
to perform optimizations and integrity checks on all user databases. This is
split into 2 seperate jobs when I look at the Jobs node under SQL Agent. The
optimizations is set to run at 4 am & the integrity at 5 am. It appears that
the integrity check job keeps failing. The log says something about
'mosesdb' needs to be in single user mode. Any ideas? The optimization jobs
seems to complete OK
TIA!Param
Under Tab 'Integrity' uncheck 'Attempt to repair any minor problems'
SQL Server is trying to repair some problems but it requires database to be
in single user mode.
"Param R." <pr@.nospam.com> wrote in message
news:ed%23BifjEFHA.1012@.TK2MSFTNGP14.phx.gbl...
> Hi all!
> I have a DB Maintanence plan configured to run once a week that is
supposed
> to perform optimizations and integrity checks on all user databases. This
is
> split into 2 seperate jobs when I look at the Jobs node under SQL Agent.
The
> optimizations is set to run at 4 am & the integrity at 5 am. It appears
that
> the integrity check job keeps failing. The log says something about
> 'mosesdb' needs to be in single user mode. Any ideas? The optimization
jobs
> seems to complete OK
> TIA!
>|||OK. But wouldnt I want it to fix any problems? What is the best way then?
thanks!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eSii%23klEFHA.2032@.tk2msftngp13.phx.gbl...
> Param
> Under Tab 'Integrity' uncheck 'Attempt to repair any minor problems'
> SQL Server is trying to repair some problems but it requires database to
> be
> in single user mode.
>
> "Param R." <pr@.nospam.com> wrote in message
> news:ed%23BifjEFHA.1012@.TK2MSFTNGP14.phx.gbl...
> supposed
> is
> The
> that
> jobs
>|||If my car keep breaking down, I'd like to know why that happen so I can avoi
d it keeping breaking
down when I travel at high speed. I.e., if you get such problem, you want to
do root-cause analysis
why they happen. Often it is because hardware problems.
In other words, the option in maint wiz is IMO not well thought through, and
AFAIK, it will be
removed in next version of SQL Server. The option, btw makes maint wiz execu
te DBCC CHECKDB using
the REPAIR_FAST option.
To be able to run DBCC CHECKDB with FAST_REPAIR, the database must be in sin
gle user mode, so if you
have users in the database, the command and subsequently job will fail.
Also, you might want to check out: http://www.karaszi.com/SQLServer/in...
uspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Param R." <pr@.nospam.com> wrote in message news:%23Czx7SqEFHA.1188@.tk2msftngp13.phx.gbl...[
vbcol=seagreen]
> OK. But wouldnt I want it to fix any problems? What is the best way then?
> thanks!
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:eSii%23klEFHA.2032@.t
k2msftngp13.phx.gbl...
>[/vbcol]|||I agree I need to know what is causing the problem. But how can I find that
out? The database seems to appear functional from the application
perspective. How can I determine if it is corrupt and if so what is causing
it to be corrupt? The link below gives me some info, but not all.
thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e20IkmqEFHA.1264@.TK2MSFTNGP12.phx.gbl...
> If my car keep breaking down, I'd like to know why that happen so I can
> avoid it keeping breaking down when I travel at high speed. I.e., if you
> get such problem, you want to do root-cause analysis why they happen.
> Often it is because hardware problems.
> In other words, the option in maint wiz is IMO not well thought through,
> and AFAIK, it will be removed in next version of SQL Server. The option,
> btw makes maint wiz execute DBCC CHECKDB using the REPAIR_FAST option.
> To be able to run DBCC CHECKDB with FAST_REPAIR, the database must be in
> single user mode, so if you have users in the database, the command and
> subsequently job will fail.
> Also, you might want to check out:
> http://www.karaszi.com/SQLServer/in..._suspect_db.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Param R." <pr@.nospam.com> wrote in message
> news:%23Czx7SqEFHA.1188@.tk2msftngp13.phx.gbl...
>|||Maint wiz has an option to create a report file for each execution. Here you
will find the error
messages.
In your situation, the problem is most likely not corruption. The problem i
s that maint wiz is
trying to set the database in single user mode (because of that option is ch
ecked), and you have
users connected to the database. Uncheck the option, and that problem will g
o away.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Param R." <pr@.nospam.com> wrote in message news:u5DxcdrEFHA.2232@.TK2MSFTNGP14.phx.gbl...[vb
col=seagreen]
>I agree I need to know what is causing the problem. But how can I find that
out? The database seems
>to appear functional from the application perspective. How can I determine
if it is corrupt and if
>so what is causing it to be corrupt? The link below gives me some info, but
not all.
> thanks!
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:e20IkmqEFHA.1264@.TK2MSFTNGP12.phx.gbl...
>[/vbcol]
I have a DB Maintanence plan configured to run once a week that is supposed
to perform optimizations and integrity checks on all user databases. This is
split into 2 seperate jobs when I look at the Jobs node under SQL Agent. The
optimizations is set to run at 4 am & the integrity at 5 am. It appears that
the integrity check job keeps failing. The log says something about
'mosesdb' needs to be in single user mode. Any ideas? The optimization jobs
seems to complete OK
TIA!Param
Under Tab 'Integrity' uncheck 'Attempt to repair any minor problems'
SQL Server is trying to repair some problems but it requires database to be
in single user mode.
"Param R." <pr@.nospam.com> wrote in message
news:ed%23BifjEFHA.1012@.TK2MSFTNGP14.phx.gbl...
> Hi all!
> I have a DB Maintanence plan configured to run once a week that is
supposed
> to perform optimizations and integrity checks on all user databases. This
is
> split into 2 seperate jobs when I look at the Jobs node under SQL Agent.
The
> optimizations is set to run at 4 am & the integrity at 5 am. It appears
that
> the integrity check job keeps failing. The log says something about
> 'mosesdb' needs to be in single user mode. Any ideas? The optimization
jobs
> seems to complete OK
> TIA!
>|||OK. But wouldnt I want it to fix any problems? What is the best way then?
thanks!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eSii%23klEFHA.2032@.tk2msftngp13.phx.gbl...
> Param
> Under Tab 'Integrity' uncheck 'Attempt to repair any minor problems'
> SQL Server is trying to repair some problems but it requires database to
> be
> in single user mode.
>
> "Param R." <pr@.nospam.com> wrote in message
> news:ed%23BifjEFHA.1012@.TK2MSFTNGP14.phx.gbl...
> supposed
> is
> The
> that
> jobs
>|||If my car keep breaking down, I'd like to know why that happen so I can avoi
d it keeping breaking
down when I travel at high speed. I.e., if you get such problem, you want to
do root-cause analysis
why they happen. Often it is because hardware problems.
In other words, the option in maint wiz is IMO not well thought through, and
AFAIK, it will be
removed in next version of SQL Server. The option, btw makes maint wiz execu
te DBCC CHECKDB using
the REPAIR_FAST option.
To be able to run DBCC CHECKDB with FAST_REPAIR, the database must be in sin
gle user mode, so if you
have users in the database, the command and subsequently job will fail.
Also, you might want to check out: http://www.karaszi.com/SQLServer/in...
uspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Param R." <pr@.nospam.com> wrote in message news:%23Czx7SqEFHA.1188@.tk2msftngp13.phx.gbl...[
vbcol=seagreen]
> OK. But wouldnt I want it to fix any problems? What is the best way then?
> thanks!
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:eSii%23klEFHA.2032@.t
k2msftngp13.phx.gbl...
>[/vbcol]|||I agree I need to know what is causing the problem. But how can I find that
out? The database seems to appear functional from the application
perspective. How can I determine if it is corrupt and if so what is causing
it to be corrupt? The link below gives me some info, but not all.
thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e20IkmqEFHA.1264@.TK2MSFTNGP12.phx.gbl...
> If my car keep breaking down, I'd like to know why that happen so I can
> avoid it keeping breaking down when I travel at high speed. I.e., if you
> get such problem, you want to do root-cause analysis why they happen.
> Often it is because hardware problems.
> In other words, the option in maint wiz is IMO not well thought through,
> and AFAIK, it will be removed in next version of SQL Server. The option,
> btw makes maint wiz execute DBCC CHECKDB using the REPAIR_FAST option.
> To be able to run DBCC CHECKDB with FAST_REPAIR, the database must be in
> single user mode, so if you have users in the database, the command and
> subsequently job will fail.
> Also, you might want to check out:
> http://www.karaszi.com/SQLServer/in..._suspect_db.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Param R." <pr@.nospam.com> wrote in message
> news:%23Czx7SqEFHA.1188@.tk2msftngp13.phx.gbl...
>|||Maint wiz has an option to create a report file for each execution. Here you
will find the error
messages.
In your situation, the problem is most likely not corruption. The problem i
s that maint wiz is
trying to set the database in single user mode (because of that option is ch
ecked), and you have
users connected to the database. Uncheck the option, and that problem will g
o away.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Param R." <pr@.nospam.com> wrote in message news:u5DxcdrEFHA.2232@.TK2MSFTNGP14.phx.gbl...[vb
col=seagreen]
>I agree I need to know what is causing the problem. But how can I find that
out? The database seems
>to appear functional from the application perspective. How can I determine
if it is corrupt and if
>so what is causing it to be corrupt? The link below gives me some info, but
not all.
> thanks!
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:e20IkmqEFHA.1264@.TK2MSFTNGP12.phx.gbl...
>[/vbcol]
Subscribe to:
Posts (Atom)