Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL server 2008 R2 database integrity

Posted on 2013-11-19
6
Medium Priority
?
610 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 23

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 23

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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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 23

Accepted Solution

by:
Steve Wales earned 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Backup & Restore 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.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

610 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