December 27, 2012 at 10:42 pm
Hi,
Tlog backup task failed on particular time only on every day at 12:00 AM..Rest of time whole day every 30 mints successfull taken.
Please suggestion me, how to aviod this issues.
Duration: 00:00:11
Status: Warning: One or more tasks failed.
Details:
Back Up Database (Transaction Log) (server1)
Backup Database on Local server connection
Databases: Database name
Type: Transaction Log
Append existing
Task start: 2012-12-28T00:00:01.
Task end: 2012-12-28T00:00:07.
Failed:(-1073548784) Executing the query "BACKUP LOG [Database name] TO DISK = N'E:\\..." failed with the following error: "Transaction (Process ID 167) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful.
11 percent processed.
22 percent processed.
33 percent processed.
44 percent processed.
55 percent processed.
60 percent processed.
71 percent processed.
83 percent processed.
94 percent processed.
100 percent processed.
Processed 2313 pages for database 'Database name', file 'database_log' on file 1.
BACKUP LOG successfully processed 2313 pages in 0.065 seconds (277.899 MB/sec).". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
thanks
ananda
December 28, 2012 at 12:55 am
You have another process going on at the same time that has a lock on a resource that is deadlocking with your log backups and SQL deemed it was easier to kill your process to resolve the deadlock. If this is happening repeatedly you need to find out what other process is running at that time that is causing the deadlock.
You can enable trace flag 1222 to capture deadlock information to the SQL error log.
Joie Andrew
"Since 1982"
December 28, 2012 at 1:20 am
ok, at the same time 12:00 Database full backup also running, should I change the full backup time 12:15 AM, and let it be leave Tlogbackup every 30 mints?
December 28, 2012 at 1:24 am
I would say it depends on how long your full backup takes. If it takes 20 minutes to do a full moving the full backup to 1215 will just cause an issue with the log backup scheduled for 1230. If the full backup does not take that long to complete, yes you could move it to a little later. You could also schedule your transaction log backups around the time of the full so it keeps the 1200 backup time but a log backup does not start at that time.
Joie Andrew
"Since 1982"
December 28, 2012 at 1:48 am
Backup uses LSN marker to avoid concurrency and locking issue (
Remember transaction log backup takes resources. So if resources are maxed out during peak usage for example you may notice deterioration of performance due to resource contention.
) with other database transactions . if you are experiencing issues there might be because you dont have inadequate IO resources.
Are you taking backup on disk (may be faster one OR not in used for tempdb/log etc) ?
In your case you can figureout what other processes running (like jobs ) ? you can re-scehdule it temporarily on other time and see if it works and check the responsible code.
OR last but not least set the trace flag for that time (from 12:00 to 13:00 ) and see who are the other palyers there ?:-)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 28, 2012 at 3:53 am
Can't believe Backup operation will block insert process. It could be other issue.
December 28, 2012 at 12:36 pm
The dead lock might be caused by backup history cleanup process.
The default proc is slow and causes a lot of locking.
Is a database being dropped with delete of backup history around that time?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply