Regarding Transaction log backup

Question

Regarding Transaction log backup

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
December 4, 2015 at 4:39 pm

#399427

Hello Experts
Can you please tell me what happens in this scenario?
I have a database for which tlog backup job is configured to run in every 15 mins. Now one of the job instances was running for more than 30 mins and thats the cause the next scheduled job failed to run.
What happens in such situation?
1. Does the next schduled instance continues to wait until the present instance is completed its run?
2. Does it fail ? ( I am sure that does not fail)
3. Does it go to suspended state?>
4. Any other case which happens?
-----------------------------------------------
Also wanted to check on one more thing, I guess backup runs on a seperate thread in sql server so that it can avoid user intervention failures. On what circumstances a backup tlog job can be blocked?
Please advise....
Thanks.

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

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

SQL-DBA-01 (12/4/2015)
Hello Experts
Can you please tell me what happens in this scenario?
I have a database for which tlog backup job is configured to run in every 15 mins. Now one of the job instances was running for more than 30 mins and thats the cause the next scheduled job failed to run.
What happens in such situation?
1. Does the next schduled instance continues to wait until the present instance is completed its run?
2. Does it fail ? ( I am sure that does not fail)
3. Does it go to suspended state?>
4. Any other case which happens?
-----------------------------------------------
Also wanted to check on one more thing, I guess backup runs on a seperate thread in sql server so that it can avoid user intervention failures. On what circumstances a backup tlog job can be blocked?
Please advise....

If the job is still running when it's scheduled to start, the job is skipped. There's no suspended state, wait or anything else. The job is simply skipped.

On the separate question of backup blocks, backups do not take object-level locks, so they won't block anything. It may beat up the I/O, but it doesn't take locks. See Paul Randall's comments at http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-3030-backup-myths/.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 2

Thnx...That is how it works. I will pass on the links to d other db champs..

Thanks.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 3

Glad I could help.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 4

SQL-DBA-01 (12/4/2015)
Thnx...That is how it works. I will pass on the links to d other db champs..

I've been following a lot of your posts over the last 6 months. If you have "other DB champs" that don't know these types of things, then I recommend that you find and hire someone that knows these "basics" off the top of their head so that you don't have so many project delays.

And, no... I'm not trying to be snarky here. It's just that the nature of your questions seem to lead to that conclusion and I'm thinking of what the best thing might be for the company you good folks work for.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)