SQL server 2008 R2 database integrity

HI,

I configured a  maintenance plan with 3 steps under a 2008 r2 sql server.

Steps:

1º check database integrity
2º rebuild index
3º delete old logs

I scheduled the plan to be ran every sunday at 10 pm, but the task crashes. If I open a console and paste the sql commands, everything goes fine.

The database integrity is OK because of this I don´t understand why the plan stops 10 seconds after starting.


any idea?
maybe a bug?

regards
heze54Asked:
Who is Participating?
 
Steve WalesSenior Database AdministratorCommented:
I had something similar happen on one of my servers recently, actually.  When researching the problem I found a couple of possible solutions.

Run exec sp_configure.
What is the value of the parameter allow updates ?
If it is set to 1 do this:

sp_configure 'Allow Updates', 0
go
reconfigure
go

Allow updates is a hangover from SQL Server 2000, where users could directly update system tables.  In 2005 and newer, this doesn't work any more, but it allows the parameter to still be set.   If this is the case, if reconfigure is run, you get an error about ad hoc update to the system catalog is not supported.

If you google the error DTSER_FAILURE (1) you'll see that  a lot of people have made posts about this in the past - and Allow updates seems to be a common issue for many people.

While the above seems to be a common cause for the DTSER_FAILURE (1) error, my issue was a little simpler.

A maintenance plan had been defined for "All databases" at some stage and a database had been dropped.  That seemed to upset the maintenance plan somewhat.  We reselected the databases we were going to operate on, saved the plan and it worked fine.

Anyway, try out the above two things, see if any of them help.
0
 
Steve WalesSenior Database AdministratorCommented:
Right click on the job in SQL Server Agent and click View History.

You should have an error there.  What's it say ?

Failing that, look in the SQL Server log directory - there should be a log file there for the SQL Server agent job - what's in there ?

There should be more information in one of those places.
0
 
heze54Author Commented:
Hi,

As far as I remember... I´m not at home right now, could be error 0?
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Steve WalesSenior Database AdministratorCommented:
Zero is usually set aside for successful completion.  When you can find the exact error message, post it here, we can come back to it tomorrow.
0
 
heze54Author Commented:
HI,

Info attached:

Ejecutar plan de mantenimiento

- Ejecute el plan de mantenimiento. MP_Weekly (Error)
	Mensajes
	* Error de ejecución. Consulte el plan de mantenimiento y los registros de historial del trabajo del Agente SQL Server para obtener detalles.
	
	------------------------------
	INFORMACIÓN ADICIONAL:
	
	Error del trabajo 'MP_Weekly.Subplán_1'. (SqlManagerUI)
	

Open in new window


Fecha		20/11/2013 8:16:39
Registro		Historial de trabajos (MP_Weekly.Subplán_1)

Id. del paso		1
Servidor		xxxxxxx
Nombre del trabajo		MP_Weekly.Subplán_1
Nombre del paso		Subplán_1
Duración		00:00:09
Gravedad de SQL		0
Id. de mensaje de SQL		0
Operador notificado mediante correo electrónico		
Operador notificado mediante envío de red		
Operador notificado mediante buscapersonas		
Reintentos efectuados		0

Mensaje
Ejecutado como usuario: xxxxx\xxxxxx. Utilidad de ejecución de paquetes de Microsoft (R) SQL Server  Version 10.50.1600.1 for 64-bit  Copyright (C) Microsoft Corporation 2010. Reservados todos los derechos.    Iniciado: 8:16:39  Progreso: 2013-11-20 08:16:44.77     Origen: {26C214E3-ACF0-4FB9-A1AA-456A512FAA37}      Ejecutando la consulta "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% finalizados  Fin de progreso  DTExec: la ejecución del paquete devolvió DTSER_FAILURE (1).  Iniciado: 8:16:39  Finalizado: 8:16:47  Transcurrido:  7.406 segundos.  Error de ejecución del paquete.  No se pudo realizar el paso.

Open in new window

0
 
heze54Author Commented:
A++++++!!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.