July 10, 2012 at 5:18 am
Hi,
We have scheduled Rebuild index and update stats job weekly manner. However this job was failed with below error message.
Could you please help me to resolve this issue.
Date08/07/2012 01:00:00
LogJob History (Database Optimize WEEKLY)
Step ID1
ServerSQLDCPRD
Job NameDatabase Optimize WEEKLY
Step NameDatabase optimization
Duration00:00:02
Sql Severity16
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: sqldcprd\sqlms. ...[SQLSTATE 01000] (Message 0) HealthwiseTW [SQLSTATE 01000] (Message 0) Executing Create and Drop Index on tblVDURiskResult - current fragmentation :25% [SQLSTATE 01000] (Message 0) Building Temporary Clustered Index [TMP_tblVDURiskResult_fldiVDURiskResultLink] [SQLSTATE 01000] (Message 0) Dropping Temporary Clustered Index [TMP_tblVDURiskResult_fldiVDURiskResultLink] [SQLSTATE 01000] (Message 0) Updating [dbo].[tblPersonnel] [SQLSTATE 01000] (Message 15650) has been updated... [SQLSTATE 01000] (Message 15652) has been updated... [SQLSTATE 01000] (Message 15652) has been updated... [SQLSTATE 01000] (Message 15652) has been updated... [SQLSTATE 01000] (Message 15652) has been updated... [SQLSTATE 01000] (Message 15652) has been updated... [SQLSTATE 01000] (Message 15652) has been updated... [SQLSTATE 01000] (Message 15652) 7 index(es)/statistic(s) have been updated, 0 did not require update. [SQLSTATE 01000] (Message 15651) Updating [dbo].[tblMonLaundTrain] [SQLSTATE 01000] (Message 15650) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) 0 index(es)/statistic(s) have been updated, 2 did not require update. [SQLSTATE 01000] (Message 15651) Updating [dbo].[tblManHandTrain] [SQLSTATE 01000] (Message 15650) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) 0 index(es)/statistic(s) have been updated, 2 did not require update. [SQLSTATE 01000] (Message 15651) Updating [dbo].[tblLookup] [SQLSTATE 01000] (Message 15650) , update is not necessary... [SQLSTATE 01000] (Message 15653) 0 index(es)/statistic(s) have been updated, 1 did not require update. [SQLSTATE 01000] (Message 15651) Updating [dbo].[tblFireTrain] [SQLSTATE 01000] (Message 15650) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) 0 index(es)/statistic(s) have been updated, 2 did not require update. [SQLSTATE 01000] (Message 15651) Updating [dbo].[tblDataProtTrain] [SQLSTATE 01000] (Message 15650) , update is not necessary... [SQLSTATE 01000] (Message 15653) , update is not necessary... [SQLSTATE 01000] (Message 15653) 0 index(es)/statistic(s) have been updated, 2 did not require update. [SQLSTATE 01000] (Message 15651) Updating [dbo].[tblCourseBookmark] [SQLSTATE 01000] (Message 15650) has been updated... [SQLSTATE 01000] (Message 15652) has been updated... [SQLSTATE 01000] (Message 15652) 2 index(es)/statistic(s) have been updated, 0 did not require update. [SQLSTATE 01000] (Message 15651) Updating [dbo].[tblCourse] [SQLSTATE 01000] (Message 15650) 0 index(es)/statistic(s) have been updated, 0 did not require update. [SQLSTATE 01000] (Message 15651) Updating [dbo].[tblContact] [SQLSTATE 01000] (Message 15650) has been updated... [SQLSTATE 01000] (Message 15652) has been updated... [SQLSTATE 01000] (Message 15652) has been updated... [SQLSTATE 01000] (Message 15652) has been updated... [SQLSTATE 01000] (Message 15652) has been updated... [SQLSTATE 01000] (Message 15652) has been updated... [SQLSTATE 01000] (Message 15652) has been updated... [SQLSTATE 01000] (Message 15652) has been updated... [SQLSTATE 01000] (Message 15652) has been updated... [SQLSTATE 01000] (Message 15652) has been updated... [SQLSTATE 01000] (Message 15652) has been updated... [SQLSTATE 01000] (Message 15652) has been updated... [SQLSTATE 01000] (Message 15652) has been updated... [SQLSTATE 01000] (Message 15652) has been updated... [SQLSTATE 01000] (Message 15652) has been updated... [SQLSTATE 01000] (Message 15652) has been updated... [SQLSTATE 01000] (Message 15652) has been updated... [SQLSTATE 01000] (Message 15652) has been updated... [SQLSTATE 01000] (Message 15652) has been updated... [SQLSTATE 01000] (Message 15652) has been upd... The step failed.
Thanks in Advance.
July 10, 2012 at 8:16 am
It would help if you showed the script used in the job step.
Jared
CE - Microsoft
July 22, 2012 at 11:44 am
I have Created job with below script
SET ARITHABORT OFF
SET NOCOUNT ON
PRINT ''
PRINT ''
PRINT ''
declare @DBName sysname
declare @sql nvarchar(1000)
set @DBName =''
while @DBName is not null
BEGIN
select @DBName = Min(name) from master.sys.databases
where name >@DBName
if db_id(@DBName) > 4 AND @DBName<>'Lumigent' AND DATABASEPROPERTYEX(@DBName ,'Status') = 'ONLINE'
BEGIN
PRINT ''
PRINT '**********'
SELECT GETDATE()
PRINT @DBName
set @sql = 'USE [' + @dbname + ']' +
' declare @trigger_state smallint ' +
' select @trigger_state = count(*) FROM sys.triggers WHERE parent_class_desc = ''DATABASE'' AND name = N''ddl_database_changes'' and is_disabled = 0 ' +
' IF @trigger_state > 0 disable trigger ddl_database_changes on database' +
' print db_name() exec sp_RebuildIndexes_SQL2K8 exec sp_updatestats DBCC UPDATEUSAGE ([' + @dbname + ']) WITH COUNT_ROWS' +
' IF @trigger_state > 0 enable trigger ddl_database_changes on database'
exec (@sql)
END
if @DBName is null break
END
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply