“A SQL job failed to complete successfully” error in SCOM

Hi,

As the error is self explainery that one of our SQL Backup Job is failed. How to fix it? Depends how much you know about SQL and you also know what to do. But let’s check which job is failed and let me show you how to check the failed “SQL Job” , I know its quite simple but sometime few SCOM new users don’t know where to find these failed jobs.

Error:

Error is showing that “SQL Job is failed on Computer 2K” and the job name is “Copy Tables From”

 

let me RDP to my SQL Server “2K” and let me open “ SQL Server Enterprise Manager”

Click on your SQL Server, expand the “Management” , beneath it expand “SQL Server Agent” and then click on “Jobs” and here is our failed SQL Job. You can see there is a red cross in the front of the the job.

To check the more details about the job, right click on it and then choose “View Job History”

now click on “Show Step Details” if you want to know more info about the job.

So here is the complete step info about the backup job. I think you can find a useful info on why this job failed.

Otherwise: go to your SQL Administrator and told him “Damn, Fix it ,”

Thanks

Aman Dhally

SCOM: Run As Account does not exist on the target system or does not have enough permissions [MS SQL server 2008]

Hi,

Yesterday  my SQL Server Management Pack reporting that it is facing some problem with “run as accounts” it says either “run as accounts does not exists” or they don't have “enough permission” to do some tasks. Both these server have SQL Server 2008 installed.

When SQL 2008 is installed by default, it no longer places BUILTIN\Administrators in the SQL security access list.  The install of SQL 2008 now prompts the installer to give SQL a user account, or Group, to grant SA (SysAdmin) rights to.  If installing on a standalone instance, NT AUTHORITY\SYSTEM (Local System) is still granted SA rights.  If installing on a clustered instance, NT AUTHORITY\SYSTEM (Local System) is granted public rights, but not SA. Kevin Holman

Error

Kevin Holman wrote an excellent post on this error.. click here

I have only few machine which are running SQL Server 2008, so i plan to give my default agent action account the rights on SA in SQL Server 2008.

Solution

Login to problematic SQL Server. Open SQL Management Studio. Got to SECURITY and then expand LOGINS,

Choose the “Default Agent Action Account” right click on it and then choose Properties.

Now click on “Server Role” and then select “SysAdmin” and click on OK..

That’s All :-) .. It should resolve the SQL Server 2008 Run As Problem.

I hope that it helps someone :)

Thanks

Aman Dhally

SCOM: Event ID “33333” in SCOM from Source “DataAccesslayer”

Hi,
From past few days i am getting Event ID code “33333” from source “DataAccesslayer” in Operations Manager Log of my RMS. This events are generating in almost every minute.

The error is indicating about to “Enable "clr" configuration option” is SQL server

Text Error Code

Event Type: Warning Event Source: DataAccessLayer Event Category: None Event ID: 33333 Date: 31/05/2011 Time: 09:28:41 User: N/A Computer: Foo-BAR Description: Data Access Layer rejected retry on SqlError: Request: MTV_SelectProperty_b87e82bc-1a85-4de3-330a-13133cf5f9c3 -- (ManagedTypeId=994bdf28-10b8-52e4-3334-d65a63d9fe52), (IsDeleted=False), (RegExPattern0=(?i)^10.0..*), (RegExPattern1=(?i)^10.1..*) Class: 16 Number: 6263 Message: Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option. For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Solution:
I searched few website and finally I got the solution. The solution is to enable “CLR” option is “OperationsManager” database so that user codes in .NET can executes

To do this Connect to your SQL server and log in to SQL Server management Console.

Right Click the “OperationsManager” database and click on “New Query”

In “New Query windows” type the below code and then click on “Execute”

sp_configure @configname=clr_enabled, @configvalue=1 GO RECONFIGURE GO

 

 

In Message Pan you will see that it run successfully and “clr” option in changed from “0” to “1”.

After i done this, the error code “33333” not occurred again … 
source: TechNet
I hope it helps someone :)

Thanks
Aman Dhally