SQL agent Job Logging

Question

SQL agent Job Logging

MCHPIS

Right there with Babe

Points: 761
More actions
June 11, 2010 at 5:48 pm

#141451

I have a job that keeps failing and when looking at the job history I cant make out that is actually causing the failure. It looks like the history is truncated. The job run for about 3 hours before failing it is a reorganize index job that works on several DB's.
Is there a way to enable verbose logging for a job to a text file that I can read through after the job fails?
If I open the job and go to steps and edit the step there is a logging tab, I can select the SSIS logging provider for text files and I have tried to put a file name in the configuration string box but I never see this file get created. I'm not sure if this is even what I need to do.
Here is a screenshot of the logging screen.
Im still a newb with SQL.
Thanks for any help.
Keith
-.- ..-. --... . -..- ---
SELECT * FROM management WHERE clue > 0;

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

magasvs SSCertifiable Points: 7660 More actions · Answer 1

You can check maintenance plans error logs in SQL Server "LOG" folder. You can double check the location by opening the maintenance plan and by clicking "Reporting and Logging" icon above the plan's name. You can also add detailed logging here.

MCHPIS Right there with Babe Points: 761 More actions · Answer 2

Thanks I will give that a try. Hopefully the extended info will show me whats failing.

Keith
-.- ..-. --... . -..- ---
SELECT * FROM management WHERE clue > 0;

MCHPIS Right there with Babe Points: 761 More actions · Answer 3

Ok so I got the logging sorted out. I got a useful log that tells me what the issue is. But Im not sure how to fix it.

Here is the clip of the log

Failed: (-1073548784) Executing the query "ALTER INDEX [PK_tblIntDiscoveredItems] ON [dbo].[tblIntDiscoveredItems] REORGANIZE WITH ( LOB_COMPACTION = OFF )

" failed with the following error: "The index "PK_tblIntDiscoveredItems" (partition 1) on table "tblIntDiscoveredItems" cannot be reorganized because page level locking is disabled.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Its complaining about the Page Level Locking is disabled. Im not sure how to go about enabling this or if this is even a good idea to do. The DB's that are being reorginized are transplants from SQL2000 and are running in SQL 2005 compatibility level.

Keith
-.- ..-. --... . -..- ---
SELECT * FROM management WHERE clue > 0;

magasvs SSCertifiable Points: 7660 More actions · Answer 4

This is easy to fix. Just update index option:

ALTER INDEX [PK_tblIntDiscoveredItems] ON [dbo].[tblIntDiscoveredItems] SET (

ALLOW_PAGE_LOCKS = ON

) ;

MCHPIS Right there with Babe Points: 761 More actions · Answer 5

Thanks for the help worked like a charm!

Keith
-.- ..-. --... . -..- ---
SELECT * FROM management WHERE clue > 0;