Transaction Log Backup Job fails with Timeout error

Question

Transaction Log Backup Job fails with Timeout error

smirabella

Mr or Mrs. 500

Points: 519
More actions
August 9, 2005 at 7:11 am

#165554

I'm running SQL Server 2000 SP3 and my transaction log backup job fails with the following error:
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
Copyright (C) Microsoft Corporation, 1995 - 1998
(null)
[Microsoft SQL-DMO (ODBC SQLState: HYT00)] Error 0: [Microsoft][ODBC SQL Server Driver]Timeout expired
(null)
Msg 22029, Sev 16: sqlmaint.exe failed. [SQLSTATE 42000]
The job is run at 1:00 in the morning when not much else is going on. There's plenty of disk space.   Is there a configuration setting I can look at or reset that might alleviate this problem?
I'll appreciate any help offered. Thanks!

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

sswords SSCrazy Eights Points: 8207 More actions · Answer 1

One or two things come to mind right away. Do you have any third party backup software running on this server? When we have had these types of problems, that has usually been the issue. I am assuming also that you're backing up to a local drive. If not, backing up to network drives can be troublesome and is not recommended.

Also, are there any messages in your server event logs? You may also want to check your NIC settings and make sure that it's configured and functioning correctly. Whenever we've seen timeout issues in our environment, it was either a network problem of some sort, or a conflict between SQL Server and third party software.

Hope this helps.

My hovercraft is full of eels.

smirabella Mr or Mrs. 500 Points: 519 More actions · Answer 2

No 3rd pty tools are used and yes, it's being backed up to a local drive. I checked the event logs for any entries before and after the failure to see if there was anything that might hint at what the problem was but I didn't see anything.

Thanks for your suggestions.

Ghanta Bro Ten Centuries Points: 1079 More actions · Answer 3

Is it the Master Database you are trying to take back up... if it so then u can only take full backup.

sswords SSCrazy Eights Points: 8207 More actions · Answer 4

Well, that's true of any database that's in Simple recovery mode but then they would be receiving a different error message than timeout expired.

If you're backing up to a local drive then, I'm somewhat at a loss to explain this as that tends to rule out any network problems. What protocols do you have enabled on this server? Typically you want Named Pipes and TCP/IP only. If Named Pipes had been disabled that might explain this but I'm not positive that's the answer. What about any other processes running on the server at the time the backup takes place?

My hovercraft is full of eels.

sswords SSCrazy Eights Points: 8207 More actions · Answer 5

Another thought. Does firing off the job manually work? You may want to start Profiler beforehand to capture what's going on at the server and attempt to identify the culprit.

If you set this job up using a maintenance plan, make sure you have the Reporting tab make sure you have the Text Report option checked on the Reporting tab to save a copy of your job logs in a specific folder. You can review this when the job fails to see if it provides any other information.

My hovercraft is full of eels.

smirabella Mr or Mrs. 500 Points: 519 More actions · Answer 6

Hi Folks,

This response hopefully answers the various questions that have been asked since the last time I logged in...

No it's not the Master db and yes, the db I'm trying to backup is in FULL Recovery mode. This Transaction Log backup job was created via the Maintenance Plan wizard and runs ok most of the time. Its failures (always with this same error) occur at random times including when there's little if anything else going on.

Firing the job manually will generally work. Again the failures aren't constant... they're sporadic. I just can't figure out what could be causing the timeout. *** Is there a configuration setting that I need to examine?***

I have a job log (.txt file) set up for the job which is how I captured this error msg.

sswords SSCrazy Eights Points: 8207 More actions · Answer 7

Okay, then here's a few more things to check or try:

1. If you're not already doing so, run a DBCC_CheckDB on the database in question and check for any problems/corruption. If you selected the Integrity checks option when creating the plan, this should have set up a job to do this for you and it will report this in the logs. Look for any error messages on the database in question.

2. What account are the Agent and MSSQLServer services running under? This account needs to have administrative priveleges in that context.

3. Try right-clicking on the job properties from the Maintenance plan and see who the job belongs to. It should also belong to an account with administrative priveleges.

4. Do you have the option "Attempt to repair any minor problems" checked in the Integrity Checks portion of the maintenance plan? If so, this option will attempt to put the DB in single user mode before backing up and cause the job to timeout or fail if it can't do so.

5. Try tweaking your job schedule. If it's hourly try setting it to 1:17, 2:17, or something similar. It could be some other scheduled activity that's sometimes conflicting with your job. The point is not to have it too close to the start or the end of the hour.

6. Generally, you only want to create one plan for each database. Was that the case here? If not, you may want to exclude other databases from the plan to single out which one is causing the problem. Make sure you don't have an old non-existant database attached to the plan.

7. If none of the above works, you may want to delete your plan and create a new one to see if that helps.

Anyone else feel free to jump in if they can think of anything. Thanks.

My hovercraft is full of eels.

sswords SSCrazy Eights Points: 8207 More actions · Answer 8

Sorry...having trouble with my posts disappearing today.

As to your question regarding configuration settings, you can try changing the Query Timeout setting to 0 (Unlimited) on the Connections tab from the Server Properties in Enterprise Manager. I seriously doubt that this would be causing the problem though, and can't think of anything else settings-wise that would.

My hovercraft is full of eels.

smirabella Mr or Mrs. 500 Points: 519 More actions · Answer 9

I found another job that was scheduled hourly and failing every time (it was trying to back up a non-existent database). The job is no longer needed so I dropped it and so far my Transaction Log backup job hasn't failed once today.

Thank you all for your suggestions, all of which I was able to use to eliminate or else check into some possibilities. I don't know if that other failing job was the cause of this problem but so far so good.

sswords SSCrazy Eights Points: 8207 More actions · Answer 10

Cool, I'll keep my fingers crossed. I'm pretty confident that the non-existant database was the problem and hopefully you're home free now.

My hovercraft is full of eels.

smirabella Mr or Mrs. 500 Points: 519 More actions · Answer 11

I appreciate your willingness to take the time to provide thoughtful and detailed suggestions. It's nice to know there's a resource like this site that I can turn to with helpful participants.

sswords SSCrazy Eights Points: 8207 More actions · Answer 12

I'm glad that I could be of help and appreciate the comments.

I think the more you get to know SSC.com the more you'll realize what a great site this is and how many talented and experienced people there are here that are invariably willing to assist you. I know I've had my fat pulled from the fire more than once thanks to the help of many others here. It's truly a community and I believe the best SQL Server resource on the web by far.

My hovercraft is full of eels.