maintenance plan error: DTSER_FAILURE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

maintenance plan error: DTSER_FAILURE

Author

Topic

brendazhangtx
Starting Member

2 Posts

Posted - 2013-12-02 : 15:50:47

Hello, when I create a maintenance plan only for integrity check, i got the error. I have tried many things, nothing worked.

Executed as user: KO\SQLServiceAcct. Microsoft (R) SQL Server Execute Package Utility Version 10.50.4000.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 12:52:59 PM Progress: 2013-12-02 12:53:00.27 Source: {B0196E8F-9241-4D80-8878-9A1A03B04080} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:52:59 PM Finished: 12:53:00 PM Elapsed: 0.656 seconds. The package execution failed. The step failed.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-12-02 : 15:56:22

I would skip a maintenance plan for integrity checks. Here is what I use:


SET NOCOUNT ON

DECLARE @dbId int, @dbName sysname, @SQL nvarchar(4000)

SELECT @dbId = 0

WHILE @dbId < (SELECT MAX(database_id) FROM master.sys.databases WHERE database_id > @dbId AND state IN (0, 4))
BEGIN
      SELECT TOP 1 @dbId = database_id, @dbName = name
      FROM master.sys.databases
      WHERE database_id > @dbId AND state IN (0, 4)
      ORDER BY database_id

      SET @SQL = 'DBCC CHECKDB(' + QUOTENAME(@dbName) + ') WITH PHYSICAL_ONLY'

      EXEC sp_executesql @statement = @SQL
END

Just create a new Agent job and then use that as the job step.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2013-12-03 : 13:16:56

To see exactly what is happening in your maintenance plan - you need to view the maintenance plan history and not the agent job history. Under the Maintenance Plan in Object Explorer, highlight your maintenance plan and View History.

This will tell you what the actual error is that is causing the plan to fail.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources