MS SQL Server - RE: SQL Server 2005 Maintenance Plans report failure although appe

Author

RE: SQL Server 2005 Maintenance Plans report failure although appe

Richard Yeo

2006-11-13, 7:13 pm

We have used SQL Profile to examine all of the SQL executed

As suspected the maintenance plan was working ok but for some reason was
failing to send the email

EXECUTE msdb.dbo.sp_notify_operator @profile_name='D1P1D
WHS1', @name=N'DBA
Alerts',@body=N'Micr
osoft(R) Server Maintenance Utility (Unicode) Version
9.0.2164 Report was generated on "D1P1DWHS1". Maintenance Plan: Backup
Business Objects Databases Duration: 00:00:23 Status: Succeeded. Details:
'

EXECUTE msdb.. sp_maintplan_update_
log
'3C16D4FD-44F5-4CEF-A47F- 6F624EE81076','','',
'','','','D1P1DWHS1'
,'0','2006-11-13
15:55:27','2006-11-13 15:55:27',0,'Could not generate mail report.An
exception occurred while executing a Transact-SQL statement or batch.No
global profile is configured. Specify a profile name in the @profile_name
parameter.',''

We turned off email alerts on the logging... options dialog and the job no
longer reports an error for the maintenance plan. we did the same to the
other server that has the same issue and the error disappeared for one of the
maintenance plans there.

However the saga is not over yet...

we still have some maintenance plans that fail.

C:\>dtexec /SQL "Maintenance Plans\Cleanup Backup Files" /SERVER D1P1DWHS1
/MAXC
ONCURRENT " -1 " /CHECKPOINTING OFF /SET "\Package\Cleanup Backup
Files.Disable"
;false /Rep V
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 17:01:44
Progress: 2006-11-13 17:01:47.46
Source: {30B3E682-634E-4F4F-A334-6BE7DC7C3E06}
Executing query "DECLARE @Guid UNIQUEIDENTIFIER

EXECUTE msdb..sp".: 100% complete
End Progress
Progress: 2006-11-13 17:01:47.76
Source: Maintenance Cleanup Task
Executing query "EXECUTE master.dbo.xp_delete_file 0,N'D:\Backup\Da".:
100% c
omplete
End Progress

Process is terminated due to StackOverflowExcepti
on.

In the windows event log...

Event Type: Error
Event Source: .NET Runtime 2.0 Error Reporting
Event Category: None
Event ID: 1000
Date: 13/11/2006
Time: 17:01:48
User: N/A
Computer: D1P1DWHS1
Description:
Faulting application dtexec.exe, version 2005.90.1399.0, stamp 43500d1a,
faulting module kernel32.dll, version 5.2.3790.2756, stamp 44c60e08, debug?
0, fault address 0x0000000000027d4d.

Richard Yeo

2006-11-16, 7:12 pm

We have given up with maintenance plans.

Wasted far too much time!!!

We have hand crafted jobs now.

The level of information reported back from maintenance plans is terrible!

Microsoft you need to do a lot more testing in this area!!!

"Richard Yeo" wrote:

> We have used SQL Profile to examine all of the SQL executed
>
> As suspected the maintenance plan was working ok but for some reason was
> failing to send the email
>
> EXECUTE msdb.dbo.sp_notify_operator @profile_name='D1P1D
WHS1', @name=N'DBA
> Alerts',@body=N'Micr
osoft(R) Server Maintenance Utility (Unicode) Version
> 9.0.2164 Report was generated on "D1P1DWHS1". Maintenance Plan: Backup
> Business Objects Databases Duration: 00:00:23 Status: Succeeded. Details:
> '
>
> EXECUTE msdb.. sp_maintplan_update_
log
> '3C16D4FD-44F5-4CEF-A47F- 6F624EE81076','','',
'','','','D1P1DWHS1'
,'0','2006-11-13
> 15:55:27','2006-11-13 15:55:27',0,'Could not generate mail report.An
> exception occurred while executing a Transact-SQL statement or batch.No
> global profile is configured. Specify a profile name in the @profile_name
> parameter.',''
>
> We turned off email alerts on the logging... options dialog and the job no
> longer reports an error for the maintenance plan. we did the same to the
> other server that has the same issue and the error disappeared for one of the
> maintenance plans there.
>
> However the saga is not over yet...
>
> we still have some maintenance plans that fail.
>
> C:\>dtexec /SQL "Maintenance Plans\Cleanup Backup Files" /SERVER D1P1DWHS1
> /MAXC
> ONCURRENT " -1 " /CHECKPOINTING OFF /SET "\Package\Cleanup Backup
> Files.Disable"
> ;false /Rep V
> Microsoft (R) SQL Server Execute Package Utility
> Version 9.00.1399.06 for 64-bit
> Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
>
> Started: 17:01:44
> Progress: 2006-11-13 17:01:47.46
> Source: {30B3E682-634E-4F4F-A334-6BE7DC7C3E06}
> Executing query "DECLARE @Guid UNIQUEIDENTIFIER
>
> EXECUTE msdb..sp".: 100% complete
> End Progress
> Progress: 2006-11-13 17:01:47.76
> Source: Maintenance Cleanup Task
> Executing query "EXECUTE master.dbo.xp_delete_file 0,N'D:\Backup\Da".:
> 100% c
> omplete
> End Progress
>
> Process is terminated due to StackOverflowExcepti
on.
>
> In the windows event log...
>
> Event Type: Error
> Event Source: .NET Runtime 2.0 Error Reporting
> Event Category: None
> Event ID: 1000
> Date: 13/11/2006
> Time: 17:01:48
> User: N/A
> Computer: D1P1DWHS1
> Description:
> Faulting application dtexec.exe, version 2005.90.1399.0, stamp 43500d1a,
> faulting module kernel32.dll, version 5.2.3790.2756, stamp 44c60e08, debug?
> 0, fault address 0x0000000000027d4d.
>
>

Sponsored Links