Solved

SQL server 2008 R2 database integrity

Posted on 2013-11-19
6
561 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

930 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now