Please Help : SQLServerAgent cannot start

Hi, All,
We got power failure at last midnight, SQL server 7.0
rebooted after that but "SQLServerAgent cannot start
because the msdb database is not available for normal
access." I can see and access the MSDB database, also
DBCC CHECKDB is ok. Form the log file, I know when the
SQL server was rebooted but it cannot tell when we got
power failure. It may be the time during Jobs to run to
backup the database and failed because of power failure.
I try to reboot server this morning and got same error
message.
Here is part of the info in the log file at the first
reboot after power failure.
******
195 transactions rolled forward in database 'msdb' (4).
0 transactions rolled back in database 'msdb' (4).
Recovery complete.
*******
So, What should I do to fix it? Any suggestions?
Thanks and have a good one
ahwakHi,
Could you please check whethere the UserAccess for MSDB is set to
RESTRICTED_USER.
sp_helpdb msdb
see the status colun of the output. If it is RESTRICTED_USER then use the
below command
ALTER DATABASE msdb set MULTI_USER
After that try restarting SQLAGENT service
Thanks
Hari
MCDBA
"ahwak" <abchawk@.hotmail.com> wrote in message
news:6edd01c4839c$c72f9e20$a401280a@.phx.gbl...
> Hi, All,
> We got power failure at last midnight, SQL server 7.0
> rebooted after that but "SQLServerAgent cannot start
> because the msdb database is not available for normal
> access." I can see and access the MSDB database, also
> DBCC CHECKDB is ok. Form the log file, I know when the
> SQL server was rebooted but it cannot tell when we got
> power failure. It may be the time during Jobs to run to
> backup the database and failed because of power failure.
> I try to reboot server this morning and got same error
> message.
> Here is part of the info in the log file at the first
> reboot after power failure.
> ******
> 195 transactions rolled forward in database 'msdb' (4).
> 0 transactions rolled back in database 'msdb' (4).
> Recovery complete.
> *******
> So, What should I do to fix it? Any suggestions?
> Thanks and have a good one
> ahwak|||Hi,Hari
Our SQL server is 7.0 sp3 on Win2000 server. when i run
sp_helpdb msdb, I get status output is 'trunc. log on
chkpt., dbo use only' and when I try to run 'ALTER
DATABASE msdb set MULTI_USER', I get a syntax error.
should I use sp_dboption to change the status?
Thanks
ahawk
>--Original Message--
>Hi,
>Could you please check whethere the UserAccess for MSDB
is set to
>RESTRICTED_USER.
>sp_helpdb msdb
>see the status colun of the output. If it is
RESTRICTED_USER then use the
>below command
>ALTER DATABASE msdb set MULTI_USER
>After that try restarting SQLAGENT service
>Thanks
>Hari
>MCDBA
>|||Is it possible that the SQL Server Agent startup account has lost it
permissions to the MSdB database?
As a test set SQL Server Agnet to start using the SQL Server service
account and see if that resolves the problem. You can also try the SYSTEM
account as well.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hi,
ALTER database command is not supported in SQL 7.
The error is because the access is set to DBO user only... Run the below to
statment to make the access to normal mode for MSDB.
sp_dboption 'msdb','dbo use only',false
After execution , Start the SQL agent service
Thanks
Hari
MCDBA
"ahawk" <anonymous@.discussions.microsoft.com> wrote in message
news:6a2a01c483a3$f6e90230$a301280a@.phx.gbl...
> Hi,Hari
> Our SQL server is 7.0 sp3 on Win2000 server. when i run
> sp_helpdb msdb, I get status output is 'trunc. log on
> chkpt., dbo use only' and when I try to run 'ALTER
> DATABASE msdb set MULTI_USER', I get a syntax error.
> should I use sp_dboption to change the status?
> Thanks
> ahawk
> >--Original Message--
> >Hi,
> >
> >Could you please check whethere the UserAccess for MSDB
> is set to
> >RESTRICTED_USER.
> >
> >sp_helpdb msdb
> >
> >see the status colun of the output. If it is
> RESTRICTED_USER then use the
> >below command
> >
> >ALTER DATABASE msdb set MULTI_USER
> >
> >After that try restarting SQLAGENT service
> >
> >Thanks
> >Hari
> >MCDBA
> >
>|||Thanks, Hari,
After I remove 'DBO use only', I restart SQL agent, i got
new error message 'Password verification of
the 'SQLAgentCmdExec' proxy account failed (reason: A
required privilege is not held by the client). but good
news is SQL agent restarted!
I use the domain admin as logon info for both SQL server
and agent. also, domain admin has the dbo right. I don't
know why it works after i changed it (it worked fine
before). even it get the error message, at least sql
agent is restart now.
thanks alot and best wishes
ahawk
>--Original Message--
>Hi,
>ALTER database command is not supported in SQL 7.
>The error is because the access is set to DBO user
only... Run the below to
>statment to make the access to normal mode for MSDB.
>sp_dboption 'msdb','dbo use only',false
>After execution , Start the SQL agent service
>Thanks
>Hari
>MCDBA
>
>