Problem with Maintenance script

Question

Problem with Maintenance script

Gary Wright-488446

Valued Member

Points: 69
More actions
September 11, 2007 at 8:06 am

#197824

Hello,
I am new to the site would like to say hey and thank everyone for the useful info I have gotten so far!   That being said I have a problem that maybe some of the gurus maybe able to help with.

I have a SQL 2k5 server that is throwing errors while running a db maintenance plan. Funny thing is that it happens 35% of the time when it launches but I cant figure out why. I will paste the script and the error log for you to look over.
TSQL SCRIPT
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 846C91F0-E6C4-47A5-BABA-2F57F3D2BB5F -Rpt "H:\oPlates\oplatesStaging\LOG\oPlatesStaging DB Maintenance Plan4.txt" -DelTxtRpt 3DAYS -WriteHistory -BkUpMedia DISK -BkUpDB "h:\oPlates" -DelBkUps 1DAYS -CrBkSubDir -BkExt "BAK"'
ERROR LOG:
Date  9/11/2007 12:00:23 AM
Log  Job History (DB Backup Job for DB Maintenance Plan 'oPlatesStaging DB Maintenance Plan')
Step ID  1
Server  MONSTER5
Job Name  DB Backup Job for DB Maintenance Plan 'oPlatesStaging DB Maintenance Plan'
Step Name  Step 1
Duration  00:21:24
Sql Severity  16
Sql Message ID  22029
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted  0
Message
Executed as user: WORKGROUP\sqlagentm5. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
-----------------
[SQLSTATE 42000] (Error 22029). The step failed.
I have done research on this and I know it has to do with the maintenance service and it mentions something about being short on drive space (Which cant be there is more than a TB on the partitions that SQL is working with.)

Help?!?

Viewing 7 posts - 1 through 6 (of 6 total)

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

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

Gary,

with maintenance plans the jobhistory is usually not very useful when it comes to fiding the reason for a failure. The logfile "H:\oPlates\oplatesStaging\LOG\oPlatesStaging DB Maintenance Plan4.txt" should give youa more detailed error.

Markus

[font="Verdana"]Markus Bohse[/font]

Gary Wright-488446 Valued Member Points: 69 More actions · Answer 2

Your right.

I have been trying to remote in to get to them but I am unable to do so at the moment. I have my jr admin mailing them to me in a few minutes though, so I will post them soon!

Thanks for the response!

EDIT

Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'MONSTER5' as 'WORKGROUP\sqlservicem5' (trusted)

Starting maintenance plan 'oPlatesStaging DB Maintenance Plan' on 9/11/2007 9:52:41 AM

[1] Database oplatesStaging: Database Backup...

Destination: [h:\oPlates\oplatesStaging\oplatesStaging_db_200709110952.BAK]

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft][ODBC SQL Server Driver][SQL Server]Write on 'h:\oPlates\oplatesStaging\oplatesStaging_db_200709110952.BAK' failed, status = 112. See the SQL Server error log for more details.

[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.

Deleting old text reports... 0 file(s) deleted.

End of maintenance plan 'oPlatesStaging DB Maintenance Plan' on 9/11/2007 10:14:12 AM

SQLMAINT.EXE Process Exit Code: 1 (Failed)

Bob Fazio SSChampion Points: 10794 More actions · Answer 3

From what I recall, that error is basically an I/O type of error. usually related to either being unable to access the database that is being backed up (offline), or the device it is trying to write too. Do you have network issues? Or perhaps being out of space on the backup filesystem?

Gary Wright-488446 Valued Member Points: 69 More actions · Answer 4

Found out what was wrong... I feel stupid now as well.

I started looking at this problem a couple of days and found right away that the error code that I was getting was supposed to mean something along the lines of being out of media space. Well I asked the Network Administrator if that was the case on this server (because at the time I didnt have direct access to it). He tells me "You have PLENTY of space so you must have another problem..." Come to find out it had space but not enough for this 25gb database backup So now I have put another drive in the beast and all is right in my world (for the moment)! Thanks for all your responses and I am sure I will have more questions soon.

Gary

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 5

Yeah,

windows administrators ust don't realize that databases are a lot bigger than most files on a fileserver. Had this type of issues too in the past, that's why I usually monitor the drivespace myself.

Markus

[font="Verdana"]Markus Bohse[/font]

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 6

HI Gary... yeah... I have a number of development databases that are quite large. All of a sudden one of the backups stopped working (of course the largest). I found out that my boss put a 35g csv file on that same drive and didn't tell me about it.

Thanks for the feedback.

Kurt

DBA

RHWI

Poughkeepsie, NY

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman