December 7, 2007 at 5:15 am
I have set up a maintenance to back up our databases every night and run the Optimizations Job on Saturdays.
The backups are working fine, but I get this message in Quest Spotlight:
************
The SQL Agent Job 'Optimizations Job for DB Maintenance Plan 'DB Maintenance Plan2'' has failed with the message, ''.
************
(I've not put any quotes round the message or the full stop - everything between the ****s is in the message.)
I can't find any info in Event Viewer, and there's no maintenance plan log file for the time when the Optimizations Job is supposed to run even though Reporting is set to write to a text file.
Any idea what's going wrong or where else to look for clues?
SQL 2000 running on W2K.
Regards,
Peter
December 7, 2007 at 8:29 am
Is the maintenance plan set up in SSMS? Is the logging checked? It should write a text file. not sure about spotlight.
What else is checked in the maintenance plan? Can you run it manually?
December 10, 2007 at 2:07 am
Steve Jones - Editor (12/7/2007)
Is the maintenance plan set up in SSMS? Is the logging checked? It should write a text file. not sure about spotlight.What else is checked in the maintenance plan? Can you run it manually?
What's the command(s) to run optimizations manually?
We don't have SSMS (I thought that was for SQL2005) - just using the Maintenance plan in SQL Enterprise Manager. Logging is turned on - we get text files when the backups run, but not when the optimizations part of the plan runs.
These are all the options checked in the plan:
Optimizations tab:
Reorganize data and index pages (change free space per page %age to 20%)
Remove unused space from database files (10% free after shrink)
Schedule: every Saturday, 6pm.
Integrity tab:
Check Database integrity
Include indexes
Schedule: every Sunday 6:30am.
Complete Backup tab:
Backup database
Disk
Use this directory xxxxxxx
Remove files older than 23 hours
Schedule: every day 4am.
Transaction Log Backup tab:
Backup the transaction log
Verify integrity of backup
Disk
Use Directory xxxxxxx
Remove files older than 1 day
Schedule: every 30 minutes.
Reporting tab:
Write report to text file in directory xxxxxxxxx
Delete reports over 4 weeks old
Write history
Limit rows to 1000.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply