Maintanance job

Posted on 2009-02-23
Last Modified: 2012-05-06

I am using sql server 2000. My maintanance job is failing since last 1 month. That
job has 6 databases. I want to know at which database or databases it is failing. What is the best way to see that. My application log in windws event viewer says as under :

SQL Server Scheduled Job 'Optimizations Job-Sage Maint Plan1 'DSRS, DatelWMS,csmaster,tcr,wtenc,wtlive'' (0xCDCA3BB86E8E184FBAA781375CD3A87B) - Status: Failed - Invoked on: 2009-02-21 05:30:05 - Message: The job failed.  The Job was invoked by Schedule 33 (Schedule 1).  The last step to run was step 1 (Step 1).  

Question by:lotusboy
    LVL 142

    Accepted Solution

    take the sql server job, open it, got to the steps details, and copy the command line, open a query window, and paste it there, and run it there.
    you shall get all the details.

    optional: in the job step details, set the check box to "include the command output into the job history" ...

    Author Comment

    Thanks That helped. I got following. What does this mean ? How can I solve this.

    Database DatelWMS: Removing unused space from the database files (if database size is more than 1000 MB). Reducing free space to 10 percent of data...[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3140: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not adjust the space allocation for file 'DatelWMS'.
    [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    LVL 25

    Assisted Solution

    Can you check if the auto-shrink option is on/checked on database DatelWMS?

    If autoshrink is on, turn it off and retry job..

    Does this database have text/image/blob columns in one or more tables??

    Author Comment

    I will retry the job checking auto-shrink option. Database have test and image columns...does it make any difference ?

    LVL 25

    Expert Comment

    Text/image/blob columns usually have problems releasing space during shrinking, though SQL Server 2005 does address it with the LOB_COMPACTION option which is usually set to ON by default..

    Is there any particular reason why the DatelWMS database is being shrunk on a regular basis. There are compelling arguments to not have this as a regular process, see link below -

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now