Maintanance job

Posted on 2009-02-23
Medium Priority
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
  • 2
  • 2
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 1600 total points
ID: 23709803
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

ID: 23709877
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

reb73 earned 400 total points
ID: 23709922
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

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

LVL 25

Expert Comment

ID: 23710959
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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
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…
Suggested Courses

830 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