SQL server 2000 transaction log maintenance plan problem
Posted on 2009-07-02
I am having a problem with one SQL maintenance plan.
I have about 7 maintenance plans on our SQL 2000 server. All of them work fine (except for email notification.. which may end up being a different question soon)
SQL Server 2000 SP3 is running on Windows 2003 Server R2 SP2
I believe the emailing the operator problem may be resolved with SQL 2000 SP4, but I will have to wait to upgrade to SP4 over the weekend.
The maintenance plan is split into separate plans... one does the indexing and integrity checks every Sunday... one does the actual database backup every morning at 5am... the other backs up the transaction logs every hour from 6am to 2am M-F
The transaction log job keeps failing. (BTW... this used to work flawlessly a while back.. I'm not sure what may have changed, but back in 2008 it was working fine)
My reasoning behind splitting the maintenance plan duties into 3 separate jobs is so I can control the scheduling better. It also allows me to run any one of them any time I need to.
There is another database on the same SQL 2000 server using the same multiple maintenance plan scenerio... these jobs complete fine (except for the mail notification)
In SQL Server Enterprise Manager....
The Transaction Log Backup Job History shows the following...
The job failed. The Job was invoked by Schedule 31 (Schedule 1). The last step to run was step 1 (Step 1).
When I "Show step details"....
Executed as user: DIETECH\msexchange. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
The Event Viewer Application log shows the following....
Category: Job Engine
Event ID: 208
Description: SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'Maintenance Plan - 4th Shift Transaction Logs'' (0x34FE3D514358A6459614D3E66B37A8B7) - Status: Failed - Invoked on: 2009-07-02 12:00:00 - Message: The job failed. The Job was invoked by Schedule 31 (Schedule 1). The last step to run was step 1 (Step 1).
When I "Edit Job Step" this is the command...
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 169F922C-F919-4EA2-BC72-A5395EDE4501 -To "DToms" -Rpt "F:\data\SQLLogs\4thShift\Maintenance Plan - 4th Shift Transaction Logs6.txt" -DelTxtRpt 2WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog "F:\data\SQLBackup" -DelBkUps 2DAYS -CrBkSubDir -BkExt "TRN"'
I have the job writing to a file... this is the log from the failed job...
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'MRP' as 'DIETECH\msexchange' (trusted)
Starting maintenance plan 'Maintenance Plan - 4th Shift Transaction Logs' on 7/2/2009 12:00:00 PM
Backup can not be performed on database 'FSDBDT'. This sub task is ignored.
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'Maintenance Plan - 4th Shift Transaction Logs' on 7/2/2009 12:00:00 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Emailing report to operator 'DToms'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 18025: [Microsoft][ODBC SQL Server Driver][SQL Server]xp_sendmail: failed with mail error 0x80040111
The SQL server is running under a domain admin account (not Local User) The maintenance plan that runs the database backup each morning runs fine and is also using this same domain admin account.
I have tried to delete the maintenance plan and recreate it 3 times now... each time... the database backup runs fine... the transaction log backup fails.