Maintanance job


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).  

Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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" ...
lotusboyAuthor Commented:
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.
reb73Connect With a Mentor Commented:
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??
lotusboyAuthor Commented:
I will retry the job checking auto-shrink option. Database have test and image columns...does it make any difference ?

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 -
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.