Solved

SQL server 2008 R2 database integrity

Posted on 2013-11-19
6
573 Views
Last Modified: 2013-11-21
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
0
Comment
Question by:heze54
  • 3
  • 3
6 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39660499
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
 

Author Comment

by:heze54
ID: 39660699
Hi,

As far as I remember... I´m not at home right now, could be error 0?
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39660706
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:heze54
ID: 39661641
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
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 39663030
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
 

Author Closing Comment

by:heze54
ID: 39665176
A++++++!!!!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question