October 21, 2009 at 5:35 am
On 2 of my servers (a dev server and QA server) my databases backup jobs are failing, I was actually able to narrow the cause down to 3 databases. Now, my question is, how and what would cause a database to have the db backup job fail? The job runs at 3:00 am every morning and all but these 3 db's are being backed up.
Could the databases be corrupt, or locked somehow, or what, any ideas and suggestions is greatly appreciated.
October 21, 2009 at 5:37 am
Could be a number of reasons, including running out of disk space. Look at the job history for the job that is failing and post the results.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 21, 2009 at 5:54 am
the databases are being backed up to a 800 gb drive with 200 gb of free space on a nightly basis and the db's themselves are a total of 10 gig for all 3. The server that they are on (the mdf and ldf files) is a total of 200 gig with 120 of free space
October 21, 2009 at 6:23 am
There must be an error message reported in the maintenance job or in the logs. Find that error. If it doesn't tell you exactly what the problem is, post it here and we'll see if we can decipher it. Without the error message, it could be almost anything.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 21, 2009 at 6:30 am
At the time of the backup there is nothing running due to I reset the SQL Server at 1:00 am to make sure no one is connected to any databases, Here is the error message from the log: Also, this database backups fine on the production server and its the same database.
NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3310
Report was generated on "ServerName".
Maintenance Plan: db Backup
Duration: 00:00:00
Status: Warning: One or more tasks failed..
Details:
Back Up Database Task (ServerName)
Backup Database on Local server connection
Databases: DataBaseName
Type: Full
Append existing
Task start: 2009-10-21T07:22:12.
Task end: 2009-10-21T07:22:13.
Failed:(-1073548784) Executing the query "BACKUP DATABASE [DatabaseName]
TO DISK
= N'\\\\NASLocaton\\Development\\ServerName\\\\DatabaseName\\DataBaseName_backup_200910210722.bak'
WITH NOFORMAT, NOINIT, NAME = N'DataBase_backup_20091021072212', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "Failed to pause full-text catalog for backup. Backup was aborted. Check to make sure Microsoft
Full-text Service is running properly and SQL server service have access to full-text service.
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
October 21, 2009 at 7:40 am
That's a very odd error. I did a search on the web and this was the best that I saw.
So I went over to MSDN and found something more useful. Check this out. I think it'll resolve your issue. As with anything you get from the web, test it carefully.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 21, 2009 at 7:48 am
I read your second recommendation prior to posting, and it didn't resolve anything. If anything that led me to more questions such as, if that was the case, then why did all of my other 30 databases on that server backup successfully/
Very odd indeed
October 21, 2009 at 7:52 am
I'm not sure. I've never run into that particular error and there's very little documentation around it.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 21, 2009 at 7:54 am
yes it is, and its driving me nuts because its only 3 of my db's and only on my dev and qa servers, these same 3 databases on my prod server are backing up every night without any issues.
fun times.
October 21, 2009 at 12:19 pm
Is FullTextSearch service running under the Network Service account?
Check this-->http://qa.sqlservercentral.com/Forums/Topic309073-146-1.aspx
MJ
October 21, 2009 at 12:23 pm
No and it hasn't in the 2 years that I've been here. Its running under a domain service account. One of the databases is 2 years old and has been the same for those 2 years (access wise) the other 2 databases are 6 months old. Now, with that said, these 3 databases were backing up without any issues up until recently, when this issue starting happening with these 3.
October 22, 2009 at 6:44 am
just an FYI, I resolved this issue with my 3 db's not being backed up and the back up job failing. The suggestions that were provided were good, but didn't resolve it. I had to rebuild the catalogs for each db and then the db's were able to be backed up.
Who'd a thought something so simple would resolve 2 weeks of trouble shooting.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply