Link to home
Start Free TrialLog in
Avatar of lotusboy
lotusboyFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Maintanance job

Experts,

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



 
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lotusboy
lotusboy
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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.
SOLUTION
Avatar of reb73
reb73
Flag of Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lotusboy
lotusboy
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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

Avatar of reb73
reb73
Flag of Ireland image
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 -

http://www.karaszi.com/SQLServer/info_dont_shrink.asp