Avatar of Math Tec
Math TecFlag for United States of America asked on

SQL Server 2008 R2 Error 926

Hi,
I have SQL 2008 cluster working under windows server 2008. SQL server agent has stop working and when I tried to login to SQL management studio. I got Database ‘msdb’ cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. (Microsoft SQL Server, Error: 926).

Does the server agent stopping cause this? I mean if server agent not working am I able to login to management studio or not?
How I can solve this issue?
Microsoft SQL ServerWindows OSMicrosoft SQL Server 2008Windows Server 2008SQL

Avatar of undefined
Last Comment
lcohan

8/22/2022 - Mon
lcohan

From your last comment from https://www.experts-exchange.com/questions/29177973/SQL-server-agent-stop-working.html you can see that the SQL Server is actually in a failed state so until you will bring that back to life there is no way to use SSMS or SQLCMD or anything else to connect to it.

You MUST check the SQL Logs from both nodes as one of them will have most likely the answer to why your SQL Server fails to start.

https://docs.microsoft.com/en-us/sql/tools/configuration-manager/viewing-the-sql-server-error-log?view=sql-server-ver15


And you also must upgrade both OS and SQL Server as running on non-supported 2008 versions brings sometimes unfortunate events/consequences.
ASKER
Math Tec

Thank you. Please SQL service is working. The SQL agent is not working


12.jpg
I removed SQL Agent now but I still get the error 926 when I tried to login to SQL management studio

6.jpg12.jpg
lcohan

Do you have SQL Management studio installed on both nodes? If yes, I would RDP into the active node (SQL2 if I'm not mistaken) and run SSMS from there.
Also, you do have backups of msdb right?

Also - did you checked SQL log files as I mentioned above? In the same location you should find a SQLAGENT.OUT file which is your SQL Agent log file.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
Math Tec

Hi, when I try to login to SQL management studio I got the error "Database ‘msdb’ cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. (Microsoft SQL Server, Error: 926"

This is the problem now. Any suggestion?

Below the output of SQLAGENT.OUT

2020-04-07 22:03:30 - ! [298] SQLServer Error: 18456, Login failed for user 'NIC1\administrator'. [SQLSTATE 28000]
2020-04-07 22:03:30 - ! [298] SQLServer Error: 4060, Cannot open database "msdb" requested by the login. The login failed. [SQLSTATE 42000]
2020-04-07 22:03:30 - ! [000] Unable to connect to server '(local)'; SQLServerAgent cannot start
2020-04-07 22:03:30 - ! [298] SQLServer Error: 18456, Login failed for user 'NIC1\administrator'. [SQLSTATE 28000]
2020-04-07 22:03:30 - ! [298] SQLServer Error: 4060, Cannot open database "msdb" requested by the login. The login failed. [SQLSTATE 42000]
2020-04-07 22:03:30 - ! [382] Logon to server '(local)' failed (DisableAgentXPs)
2020-04-07 22:03:31 - ? [098] SQLServerAgent terminated (normally)

7.jpg
ASKER CERTIFIED SOLUTION
lcohan

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Math Tec

Hi, Thanks.
I tried to install sqlcmd Utility but I got error

Below all the database that I have. I do not know which one is the default.

8.jpg
lcohan

As far as I'm aware the sqlcmd.exe is installed by default in older versions like you run under .\Program Files\Microsoft SQL Server\100\Tools\Binn folder

Also...did you read carefully the log entries above??
2020-04-07 22:03:30 - ! [298] SQLServer Error: 18456, Login failed for user 'NIC1\administrator'. [SQLSTATE 28000]

Do you (or anyone else) knows a SQL Server "sysadmin" valid login - whether is SQL login or Active Directory login to connect to this SQL Server that's having these issues? Without a valid known login is little you can do.

There from an older SQL Server 2008 that I still have lingering around.
SQLCMD.png
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Math Tec

Hi,

I have the Active Directory login and SA login. I did not change it. The problem happened after power failure.

Any suggestion?
lcohan

Can you try run again SSMS and use SQL Authentication - SA login + password to connect to SQL?
And "The problem happened after power failure" is most likely the reason your msdb gort corrupted.
Also you still didn't answered my question if you have any available recent backups for msdb database?
ASKER
Math Tec

I tried SQL authentication and Windows authentication and in both case I got the same error. I likely say the msdb corrupted but how I can repair it?

Any suggestion ?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
lcohan

Did you find sqlcmd.exe so you can use it to run the commands needed to fix/restore the corrupted msdb?
ASKER
Math Tec

Hi everyone,

I bought application to recover the database "SQL Recovery + SQL Backup Recovery". When I tried to restore database using the application I got error. Failed to login. I do believe because the system database is in SUSPECTED mode.

Any help with this issue? Also, if there is anyone can help with this I will be very appreciated.  Its really urgent.

Thank you for all the help
Regards
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.