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!
>>
>>
>>
>>
>