Is SQL server's deadlock detection complete

Or more generally what are the basic features/restrictions of the
deadlock detection and killing mechanism of SQL server 2000.
Case in point:
I was able to simulate simple deadlocks of up to 6 processes within the
SQL Analyzer env., each time the deadlock formed it was got killed
immediately by the SQL Server, with a victim being chosen (I guess that
pattern may hold for more than 6 processes.)
On the other hand, sometimes we can find deadlocks in real production or
development testing that hang there without being auto killed.
How to explain this?
Thx for answers/comments.
cyu
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
SQL Server can only detect deadlocks on internal resources. If a deadlock
exists but some of the resources are outside of the SQL server, then you are
out of luck. SQL detects deadlocks by finding cycles within locks and lock
wait chains. A cycle or loop indicates a deadlock. The number of processes
in the loop is irrelevant as long as it is 2 or more. The victim is
supposed to be the process with the least resources expended (thus
theoretically, the easiest to rollback.)
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cyu" <cyu@.devdex.com> wrote in message
news:e107qZKBFHA.904@.TK2MSFTNGP12.phx.gbl...
> Or more generally what are the basic features/restrictions of the
> deadlock detection and killing mechanism of SQL server 2000.
> Case in point:
> I was able to simulate simple deadlocks of up to 6 processes within the
> SQL Analyzer env., each time the deadlock formed it was got killed
> immediately by the SQL Server, with a victim being chosen (I guess that
> pattern may hold for more than 6 processes.)
> On the other hand, sometimes we can find deadlocks in real production or
> development testing that hang there without being auto killed.
> How to explain this?
> Thx for answers/comments.
> cyu
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Thx for Geoff's quick reply.
But in my test env all resources seem to be in the same server (almost
certainly so), or even in the same db. So what things I may possibly
have overlooked?
BTW, deadlock detection in general woithout bounds is NP-complete, so
there is reason to think that either there are practical bounds on the
algorithm's capacity or it is infeasible in certain cases.
cyu
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Can you give us an example of an unresolved/undetected deadlock? What
resources are involved? Are you sure it is a deadlock, and not a long
blocking chain?
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"cyu" <cyu@.devdex.com> wrote in message
news:%23MBo8yLBFHA.3492@.TK2MSFTNGP12.phx.gbl...
> Thx for Geoff's quick reply.
> But in my test env all resources seem to be in the same server (almost
> certainly so), or even in the same db. So what things I may possibly
> have overlooked?
> BTW, deadlock detection in general woithout bounds is NP-complete, so
> there is reason to think that either there are practical bounds on the
> algorithm's capacity or it is infeasible in certain cases.
> cyu
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Is SQL server's deadlock detection complete

Or more generally what are the basic features/restrictions of the
deadlock detection and killing mechanism of SQL server 2000.
Case in point:
I was able to simulate simple deadlocks of up to 6 processes within the
SQL Analyzer env., each time the deadlock formed it was got killed
immediately by the SQL Server, with a victim being chosen (I guess that
pattern may hold for more than 6 processes.)
On the other hand, sometimes we can find deadlocks in real production or
development testing that hang there without being auto killed.
How to explain this?
Thx for answers/comments.
cyu
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!SQL Server can only detect deadlocks on internal resources. If a deadlock
exists but some of the resources are outside of the SQL server, then you are
out of luck. SQL detects deadlocks by finding cycles within locks and lock
wait chains. A cycle or loop indicates a deadlock. The number of processes
in the loop is irrelevant as long as it is 2 or more. The victim is
supposed to be the process with the least resources expended (thus
theoretically, the easiest to rollback.)
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cyu" <cyu@.devdex.com> wrote in message
news:e107qZKBFHA.904@.TK2MSFTNGP12.phx.gbl...
> Or more generally what are the basic features/restrictions of the
> deadlock detection and killing mechanism of SQL server 2000.
> Case in point:
> I was able to simulate simple deadlocks of up to 6 processes within the
> SQL Analyzer env., each time the deadlock formed it was got killed
> immediately by the SQL Server, with a victim being chosen (I guess that
> pattern may hold for more than 6 processes.)
> On the other hand, sometimes we can find deadlocks in real production or
> development testing that hang there without being auto killed.
> How to explain this?
> Thx for answers/comments.
> cyu
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||Thx for Geoff's quick reply.
But in my test env all resources seem to be in the same server (almost
certainly so), or even in the same db. So what things I may possibly
have overlooked?
BTW, deadlock detection in general woithout bounds is NP-complete, so
there is reason to think that either there are practical bounds on the
algorithm's capacity or it is infeasible in certain cases.
cyu
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Can you give us an example of an unresolved/undetected deadlock? What
resources are involved? Are you sure it is a deadlock, and not a long
blocking chain?
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"cyu" <cyu@.devdex.com> wrote in message
news:%23MBo8yLBFHA.3492@.TK2MSFTNGP12.phx.gbl...
> Thx for Geoff's quick reply.
> But in my test env all resources seem to be in the same server (almost
> certainly so), or even in the same db. So what things I may possibly
> have overlooked?
> BTW, deadlock detection in general woithout bounds is NP-complete, so
> there is reason to think that either there are practical bounds on the
> algorithm's capacity or it is infeasible in certain cases.
> cyu
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Is SQL server's deadlock detection complete

Or more generally what are the basic features/restrictions of the
deadlock detection and killing mechanism of SQL server 2000.
Case in point:
I was able to simulate simple deadlocks of up to 6 processes within the
SQL Analyzer env., each time the deadlock formed it was got killed
immediately by the SQL Server, with a victim being chosen (I guess that
pattern may hold for more than 6 processes.)
On the other hand, sometimes we can find deadlocks in real production or
development testing that hang there without being auto killed.
How to explain this?
Thx for answers/comments.
cyu
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!SQL Server can only detect deadlocks on internal resources. If a deadlock
exists but some of the resources are outside of the SQL server, then you are
out of luck. SQL detects deadlocks by finding cycles within locks and lock
wait chains. A cycle or loop indicates a deadlock. The number of processes
in the loop is irrelevant as long as it is 2 or more. The victim is
supposed to be the process with the least resources expended (thus
theoretically, the easiest to rollback.)
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cyu" <cyu@.devdex.com> wrote in message
news:e107qZKBFHA.904@.TK2MSFTNGP12.phx.gbl...
> Or more generally what are the basic features/restrictions of the
> deadlock detection and killing mechanism of SQL server 2000.
> Case in point:
> I was able to simulate simple deadlocks of up to 6 processes within the
> SQL Analyzer env., each time the deadlock formed it was got killed
> immediately by the SQL Server, with a victim being chosen (I guess that
> pattern may hold for more than 6 processes.)
> On the other hand, sometimes we can find deadlocks in real production or
> development testing that hang there without being auto killed.
> How to explain this?
> Thx for answers/comments.
> cyu
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Is My Backup Plan Safe?

I need to setup an online backup mechanism which allows restoration of data
up to point of failure in the event of DB file corruption or hard disk drive
failure. The current DB size is ~ 100M with a growth rate of ~15M/month for
the last 6 months. I'm thinking of :
1. Create a scheduled daily full backup from c drive to d drive
2. Create a scheduled hourly transaction log backup from c drive to d drive
3. Create a scheduled weekly full backup of backup folder in d drive to tape
drive with Windows backup utility.
4. Create scheduled daily differential backup of backup folder in d drive to
tape drive with Windows backup utility.
5. Two tapes will be rotated every alternate week for backup to tapes. Tape
not in used will be taken off-site.
Is the above plan good enough for the recovery scenario I want to address?Ong
Yep it looks pretty good. But remember that everything you did so far has
no value if you cannot restore the database. Verify that backups are not
corrupted and you can easily restore the database
"Ong" <Ong@.discussions.microsoft.com> wrote in message
news:68837F91-E41F-4487-B673-9316EE9D9037@.microsoft.com...
>I need to setup an online backup mechanism which allows restoration of data
> up to point of failure in the event of DB file corruption or hard disk
> drive
> failure. The current DB size is ~ 100M with a growth rate of ~15M/month
> for
> the last 6 months. I'm thinking of :
> 1. Create a scheduled daily full backup from c drive to d drive
> 2. Create a scheduled hourly transaction log backup from c drive to d
> drive
> 3. Create a scheduled weekly full backup of backup folder in d drive to
> tape
> drive with Windows backup utility.
> 4. Create scheduled daily differential backup of backup folder in d drive
> to
> tape drive with Windows backup utility.
> 5. Two tapes will be rotated every alternate week for backup to tapes.
> Tape
> not in used will be taken off-site.
> Is the above plan good enough for the recovery scenario I want to address?|||Thank you for your comment & precaution on backup verification.
"Uri Dimant" wrote:
> Ong
> Yep it looks pretty good. But remember that everything you did so far has
> no value if you cannot restore the database. Verify that backups are not
> corrupted and you can easily restore the database
> "Ong" <Ong@.discussions.microsoft.com> wrote in message
> news:68837F91-E41F-4487-B673-9316EE9D9037@.microsoft.com...
> >I need to setup an online backup mechanism which allows restoration of data
> > up to point of failure in the event of DB file corruption or hard disk
> > drive
> > failure. The current DB size is ~ 100M with a growth rate of ~15M/month
> > for
> > the last 6 months. I'm thinking of :
> > 1. Create a scheduled daily full backup from c drive to d drive
> > 2. Create a scheduled hourly transaction log backup from c drive to d
> > drive
> > 3. Create a scheduled weekly full backup of backup folder in d drive to
> > tape
> > drive with Windows backup utility.
> > 4. Create scheduled daily differential backup of backup folder in d drive
> > to
> > tape drive with Windows backup utility.
> > 5. Two tapes will be rotated every alternate week for backup to tapes.
> > Tape
> > not in used will be taken off-site.
> >
> > Is the above plan good enough for the recovery scenario I want to address?
>
>|||Is the C and D drive the same physical drive? Then you could lose you
database drive and your backup drive.
If C and D are separate physical drives are they on the same disk
controller? If the controller fails then you won't be able to access your
database and backups until you replace the controller or put the drive in
another box..
Imagine your server and tape drive are stolen. It sounds like you would have
to go to tape and possibly lose a week of transactions.
I would backup across the network or directly to the tape drive or get a
separate external hard drive. If your only moving data offsite once a week
get a fireproof safe.
I don't think your backup plan is safe and I don't believe you will be able
to restore to the point of failure if say your building burns down.
"Ong" <Ong@.discussions.microsoft.com> wrote in message
news:68837F91-E41F-4487-B673-9316EE9D9037@.microsoft.com...
> I need to setup an online backup mechanism which allows restoration of
data
> up to point of failure in the event of DB file corruption or hard disk
drive
> failure. The current DB size is ~ 100M with a growth rate of ~15M/month
for
> the last 6 months. I'm thinking of :
> 1. Create a scheduled daily full backup from c drive to d drive
> 2. Create a scheduled hourly transaction log backup from c drive to d
drive
> 3. Create a scheduled weekly full backup of backup folder in d drive to
tape
> drive with Windows backup utility.
> 4. Create scheduled daily differential backup of backup folder in d drive
to
> tape drive with Windows backup utility.
> 5. Two tapes will be rotated every alternate week for backup to tapes.
Tape
> not in used will be taken off-site.
> Is the above plan good enough for the recovery scenario I want to address?|||Terri ,
Thanks for your important input.
I didn't check this thread for a few days & only saw your reply today.
I hope you can clarify my questions below:
"Terri" wrote:
> Is the C and D drive the same physical drive? Then you could lose you
> database drive and your backup drive.
> If C and D are separate physical drives are they on the same disk
> controller? If the controller fails then you won't be able to access your
> database and backups until you replace the controller or put the drive in
> another box..
FYI, D is an external usb drive
> Imagine your server and tape drive are stolen. It sounds like you would have
> to go to tape and possibly lose a week of transactions.
> I would backup across the network or directly to the tape drive or get a
> separate external hard drive.
Will backup across the network be slow and cause network jam?
>If your only moving data offsite once a week
> get a fireproof safe.
Do you mean to put the tape in a fireproof safe in the office or offsite?
> I don't think your backup plan is safe and I don't believe you will be able
> to restore to the point of failure if say your building burns down.
Sounds like for solution with tape, the only safe way is to use 7 (or 5
excluding weekends) backup tapes to do full db backup daily and transaction
logs backup hourly and keep all the unused tapes offsite.