Solved

SQL server 2000 transaction log maintenance plan problem

Posted on 2009-07-02
12
811 Views
Last Modified: 2012-05-07
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....
Source: SQLSERVERAGENT
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.

Any ideas??


0
Comment
Question by:Die-Tech
  • 6
  • 4
  • 2
12 Comments
 
LVL 4

Expert Comment

by:Igor-K
ID: 24765329
Have you tried backing up the transaction log yourself.  If  I am not mistaking you can go to the maintenance plan and right click there to see the history.  Also, check the SQL server log, maybe you will find something strange there.
0
 
LVL 4

Expert Comment

by:Igor-K
ID: 24765343
One more suggestion is to profile it.  Choose all Errors and warnings, Stored Procedures (RPC:Starting, RPC:Completed, SP:Stmt:Completed, SP:Stmt:Starting), TSQL(SQL:BatchStarting,SQL:BatchCompleted,SQL:StmtStarting,SQL:Stmt:Completed)
0
 
LVL 20

Accepted Solution

by:
Marten Rune earned 500 total points
ID: 24765395
A classic failure is to select all databases, then you have some that are in simple recovery mode. The job will then do the transactionlog backup of all dbs in full or bulklogged recovery mode. But when its time to delete old backups, and history the job notices that there has been errors, thus exciting with error. After a while the job fails due to lack of space on the backup storage area.

Check if you have DBs in simple recoverymode. To do this you can use this script:

/Marten
select name as "DBNAME"

from master.dbo.sysdatabases 

Where name not in ('master','model','msdb','tempdb')

and DatabasePropertyEx(name,'Status') = 'ONLINE'

and DatabasePropertyEx(name,'Recovery') = 'SIMPLE'

Open in new window

0
 
LVL 4

Author Comment

by:Die-Tech
ID: 24765768
martenrune.. you may be on to something...
I just ran the code you sent above in query analyzer and 3 databases showed up in the results.

Northwind
Pubs
FSDBDT

FSDBDT is the database that the transaction log job is failing on...

Does this mean the FSDBDT database in in simple recovery mode?

Is so, what do I do next?

0
 
LVL 4

Author Comment

by:Die-Tech
ID: 24765810
Ok.. I just checked the FSDBDT Database Properties and on the Options tab under Recovery, it shows the model is Simple

The dropdown has Full as an option... which is what my other databases show.

Is it as easy as changing it on this dropdown? or do I have to make sure everyone is out of the system first?

0
 
LVL 4

Author Comment

by:Die-Tech
ID: 24765967
Well... I went ahead and took a chance...

I changed the model to Full with the dropdown box... it took a few seconds, but it seems to have worked.

I closed the Database Properties dialog and then reopened it... on the Options tab under Recovery, it now shows the model is Full

I manually started the transaction log job and it completed without error.
There's a new .TRN file in the expected folder... I think I'm back in business!

Thanks for you help!
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 20

Expert Comment

by:Marten Rune
ID: 24766030
Glad to assist. Happy for you.

/Marten
0
 
LVL 4

Author Comment

by:Die-Tech
ID: 24790335
martenrune,

I may need to open a new question on this... I just noticed the database went back to simple recovery mode this morning... I just now changed it back to full...  any ideas what would cause this?  We do have a "night processing" of our MRP system that runs each morning.... on the same database in question.
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 24792171
Well something definately changes the recovery mode for 'FSDBDT'.

I would begin with checking the SLA. Do you need the full recovery mode. If not then the a solution is to exclude this database (FSDBDT) from the log backup part of your maintenance plan.

Then you can't use the 'all databases', witch is a drawback.

The other apporach is to trace why it's set back to simple. This is not a normal behaviour. I would look into who has the rights on this db to change the recovery mode, and definately analyze the 'night processing' to determine whats happening in the database.

/Marten
0
 
LVL 4

Author Comment

by:Die-Tech
ID: 24793222
Will do... thanks...

Since this is our MRP server, we need the database in full recovery mode...  the transaction logs are backed up to the Backup Exec server... if there is a failure, we should be able to restore the database and then restore the transaction log(s) to get us back up.

This morning when I checked.. it's the strangest thing... the transaction log job ran and didn't show that it failed.. only the schedule says the next time it's suppose to run is tomorrow morning..  The schedule is set for weekly (so I can choose Mon-Fri days only) then there's an option to "reoccure" every 1 hour between 6am and 3am... only it says the next run time is tomorrow morning at 6am

0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 24795939
Well I would run the transaction logs every second, or third hour. but thats me. But once every 24h seems way to seldom.

The job should fail if the database is in simple mode, and choosen in the trans log part of the maintenance plan. Check if you got transaction files, if so it wasn't in simple mode.

/Marten
0
 
LVL 4

Author Comment

by:Die-Tech
ID: 24796113
We're running the transaction logs every 1hr
I was trying to not run them on Sat or Sun with the schedule set to weekly (M-F checked) and then every hour checked.... it used to do this too...


0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Learn about cloud computing and its benefits for small business owners.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now