March 11, 2013 at 4:16 am
Hi,
I have a maintenance plan that runs a differential backup every 4 hours and a separate job that backs up logs every 15 mins.
Sometimes their schedules clash and the jobs tried run at the same time and 1 gets blocked for a bit.
I thought in SQL 2008 DB backups and logs backup wouldn't block each other?
Should I change the schedules so the jobs don't run at the same time?
March 11, 2013 at 5:01 am
Full and log backups don't block each other (since SQL 2005), don't know about diff and log.
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
March 11, 2013 at 5:08 am
is there anything else that your backup does apart from backing up the database?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 12, 2013 at 4:10 am
Nothing else is running. Its just a standard log backup and diff backup.
March 12, 2013 at 4:20 am
Is it a custom script, can you post details of what it does?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 12, 2013 at 4:26 am
Backup operations do not take locks on user objects. Backups do cause a really heavy read load on the I/O subsystem so it might *look* like the workload is being blocked, but it isn't really. It's just being slowed down. There's a special case where a backup that has to pick up bulk-logged extents will take a file lock which could block a checkpoint operation – but DML is never blocked.
Regards,
Kumar
March 12, 2013 at 4:47 am
KumarSQLDBA (3/12/2013)
There's a special case where a backup that has to pick up bulk-logged extents will take a file lock which could block a checkpoint operation – but DML is never blocked.
No one's saying that it is, the OP's not complaining about DML being blocked.
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
March 12, 2013 at 5:27 am
This is the info captured from redgates SQL Monitor. Both these jobs are done using maintenance plans with one step to do the backup, no custom scripts here.
Process ID:84
Database:XXXX
Host:XXXX
User:xxxxx
Start time:4:00 PM
SQL fragment:BACKUP LOG [XXXX] TO DISK = N'D:\XXXXX_backup_2013_03_10_160003_3778311.trz' WITH NOFORMAT, NOINIT, NAME = N'XXXX_backup_2013_03_10_160003_3748308', SKIP, REWIND, NOUNLOAD, STATS = 10
Blocking process:
Process ID:85
Database:XXXX
Host:XXXX
User:xxxxx
Start time:4:00 PM
SQL fragment:BACKUP DATABASE [XXXX] TO DISK = N'D:\XXXX_backup_2013_03_10_160003_3778311.dfz' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'XXXX_backup_2013_03_10_160003_3758309', SKIP, REWIND, NOUNLOAD, STATS = 10
Before this block there was a previous block 4 hours before on the main Backup:
Blocked process:
Process ID:85
Database:XXXX
Host:XXXX
User:xxxxx
Start time:12:00 PM
SQL fragment:BACKUP DATABASE [XXXX] TO DISK = N'D:\XXXX_backup_2013_03_10_120003_8706785.dfz' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'XXXX_backup_2013_03_10_120003_8686785', SKIP, REWIND, NOUNLOAD, STATS = 10
Blocking process:Unknown
Process ID:13
Database:
Host:
User:Unknown
Start time:1:47 AM
SQL fragment:Unknown
On inspection of SPID 13 this tempdb - Command CHECKPOINT
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply