November 20, 2012 at 1:24 pm
guys:
When i am running the differential back up using the same maintanace plans i had earlier its erroring saying:
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. End Error DTExec: The package execution returned DTSER_FAILURE (1).
But the back up files are created on the disk..
I wonder because i never had this earlier (i used the same maintance plan to back up all the db)
any help is very much appreciated.
Thanks
November 20, 2012 at 1:34 pm
Some questions:
1. Is the backup occuring to a network share or local disk / SAN ?
2. Since this is a maintenance plan running on SQL Agent, did the serviuce account change recently ?
3. Any other errors in the event logs around the same time ?
4. Was any database restored on this instance that has not been backed up using a full backup ?
November 20, 2012 at 1:41 pm
Try running the actual TSQL needed to run the backup from SSMS, the error outputted by the SQL Agent job isn't always very descriptive. Also in the past, I've noticed this issue happening because 1) we were using a network attached storage that had some network connectivity issues 2) the share/location being written to was out of space, and 3) Permissions were lost on the folder the backup was being be written to (it wasn't a windows box).
What happens when you run it from SSMS? EXECUTE master.dbo.xp_create_subdir N'\\NAS-DEVICE\BackupFolder\MyDatabaseName'
GO
BACKUP DATABASE [MyDatabase]
TO DISK = N'\\NAS-DEVICE\BackupFolder\MyDatabaseName_2012_11_22_123456_0000001.bak'
WITH NOFORMAT, NOINIT, NAME = N'MyDatabaseName_2012_11_22_123456_0000001', SKIP, REWIND, NOUNLOAD, NO_COMPRESSION, STATS = 10
GO
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 20, 2012 at 1:42 pm
1. the back up is on local disk
2. the maintanace plan is running sql agent
3.hm no erros were occured except for the err i posted in regarding to diff back up failure
4.the full back up which takes place every wekend is sucessfull. there was no err then
November 20, 2012 at 1:44 pm
Try executing it via a query window, also, have you verified no errors in the System Event Viewer? One last thing, are you 100% positive there is adequate space available?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 20, 2012 at 1:47 pm
hey
ty for your response
but as i said i can see the back up files are being created but i see the job being failed.
with the err msg
November 20, 2012 at 1:48 pm
ya there is enough space available for the back up
November 20, 2012 at 1:50 pm
penu.v8 (11/20/2012)
heyty for your response
but as i said i can see the back up files are being created but i see the job being failed.
with the err msg
Yes, but what is the error output when you run it manually through the query window (and NOT through the job?)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 20, 2012 at 1:52 pm
58 percent processed.
95 percent processed.
Processed 216 pages for database 'amMaintenance', file 'amMaintenance' on file 1.
100 percent processed.
Processed 1 pages for database 'amMaintenance', file 'amMaintenance_log' on file 1.
BACKUP DATABASE successfully processed 217 pages in 0.678 seconds (2.500 MB/sec).
this is the msg i get wen i run manually
(which is a sucess)
November 20, 2012 at 2:06 pm
So this used to work before? Sorry if i misunderstood your original post...
Since it works when you run it manually, it makes me think that the execution/proxy account for your SQL Server Agent doesn't have the required permissions needed to write locally (which is probably a silly assumption since you said it's worked for full backups in the past)
If you run a FULL backup via the SQL agent, it works? Does other Differential backups work as well?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 20, 2012 at 2:10 pm
yes it was the same maintance plan earlier too and worked good
and there are no issues with full back up jobs
and i can take a back up manually with out any error but when the job is running i get the error.
November 20, 2012 at 2:10 pm
Did any permissions change? Perhaps with your Tech Services team? Was the account being used to execute your SQL Agent jobs recently change? Perhaps it was in the local administrators group on the server, but not anymore?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 20, 2012 at 2:11 pm
Also, which Service Pack do you have installed?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 20, 2012 at 2:15 pm
hmm.. welll need to dig into that since the full back up too writing to the same folder..
i dont understand err:
Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
any idea where can i configure this..?
November 20, 2012 at 2:21 pm
That's just a generic 'something went wrong' message.
So the scheduled backup succeeds (backup file written, correctly, complete and restorable) but the job is marked as failed?
Does the job step succeed?
If you write the job output to a file, is there any more useful information?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply