March 10, 2009 at 7:51 am
I have several maintenance plans that run on our SQL 2005 installation. One of the jobs runs every night at about 1:43 AM. This job performs Reorganize Index on several databases and then performs Update Statistics on several databases.
The job has been running fine for several months but has failed the last three nights it has run. When I look at the history for the job, and expand the log entry I see the error details but the message isn't really giving me anything to go on as follows:
Executed as user: XXXXXXXXX.
...ersion 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 1:43:00 AM
Progress: 2009-03-07 01:43:04.50
Source: {31BD5733-2050-452A-92F3-19BA9A51974B}
Executing query "DECLARE @GUID UNIQUEIDENTIFIER
EXECUTE msdb..sp".: 100% complete
End Progress
Progress: 2009-03-07 01:50:20.43
Source: Reorganize Index
Executing query "USE [BIRS] ".: 2% complete
End Progress
Progress: 2009-03-07 01:50:20.48
Source: Reorganize Index
Executing query "ALTER INDEX [aaaaatblAdminPassword_PK] ON [dbo].[t".: 4% complete
End Progress
Progress: 2009-03-07 01:50:20.48
Source: Reorganize Index
Executing query "USE [BIRS] ".: 6% complete
End Progress
Progress: 2009-03-07 01:50:20.48
Source: Reorganize Index
Executing query "ALTER INDEX [AdmPassword] ON [dbo].[tblAdminPasswo".: 8% complete
End Progress
Progress: 2009-03-07 01:50:20.50
Source: ... The package execution fa... The step failed.
As you can see all I get at the end is "The package execution failed. The step failed. I can't seem to find any more information on this error and am not sure if there is a way to dump more detail.
If anyone can help me move forward in my troubleshooting that would be fantastic. Thanks!
-L
March 10, 2009 at 8:01 am
Try specifying a log file under the job logging option and see if it logs helpful error message there.
MJ
March 10, 2009 at 8:20 am
I suspect an issue with an index. Are you running CHECKDB on here?
you definitely need logging for all packages as mentioned above. Job history doesn't give much info.
March 11, 2009 at 12:21 am
Or your database that your building index in doesn't have enough space to reorganize the index; that was the case for me when it was failing. I would look at the log file as it saves more detailed information as Manu suggested.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply