Need Help with Maintenance Job Errors
-
I have a SQL 2005 database server that has been in place for almost a year now and the maintenance plans that were created for the server have been running a long time without error. I recently created a DDL trigger on one of the databases in order to monitor any DDL activity on this production DB so I could record it into a new table I placed in that DB.
Here is how i made the table:
use PRODDB
go
create table dbo.DDLAuditLog
(command nvarchar(1000),
posttime nvarchar(24),
hostname nvarchar(100),
loginname nvarchar(100)
)
go
Now here is the Trigger:
/* Creat the trigger */
create trigger [ddl_auditoperations]
on database
for ddl_database_level_events
as
declare @data XML
declare @cmd nvarchar(1000)
declare @posttime nvarchar(24)
declare @spid nvarchar(6)
declare @hostname nvarchar(100)
declare @loginname nvarchar(100)
set @data = eventdata()
set @cmd = convert(nvarchar(100),@data.query('data(//TSQLCommand//CommandText)'))
set @posttime = convert(nvarchar(24),@data.query('data(//PostTime)'))
set @spid = convert(nvarchar(6),@data.query('data(//SPID)'))
set @hostname = HOST_NAME()
set @loginname = SYSTEM_USER
insert into dbo.ddlauditlog(command,posttime,HostName,LoginName)
values (@cmd, @posttime, @hostname, @loginname)
Now what happens is when the daily and weekly maintenance runs on the server it fails when it tries to rebuild the indexes as well as when it tries to update statistics. I have disabled and even dropped the trigger but that didnt help any. Could it be the table? Any ideas would be great. The job history log just shows the job failing but no real detail. Same with Event Log.
-
Write the job out put file to a file and see what is happenning.
At the same time you can run the profiler also with error events.
MohammedU
Microsoft SQL Server MVP -
Have you checked the Show Job Steps box in the Job History window? That will show you the individual steps and usually gives more information on failures.
-SQLBill
-
Ok...here is what the log says.
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2153
Report was generated on "ibis".
Maintenance Plan: Weekly-Rebuild All User Indexes
Duration: 00:00:11
Status: Warning: One or more tasks failed..
Details:
Rebuild Index (ibis)
Rebuild index on Target server connection
Databases: All user databases
Object: Tables and views
Original amount of free space
Task start: 1/11/2007 9:23 AM.
Task end: 1/11/2007 9:23 AM.
Failed
-1073548784) Executing the query "ALTER INDEX [PK_CORE_LABEL_GROUP] ON [dbo].[CORE_LABEL_GROUP] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )" failed with the following error: "Target string size is too small to represent the XML instance". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
-
Hello
The problem is the conversion of the xml type to text type. A fast solution way is to increase varchar fields in ddlauditlog and include SET ANSI_PADDING in the trigger.
This solved my problem
Regards
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply