hot backup: log

Showing posts with label log. Show all posts

Friday, March 23, 2012

How can I back up a log-shipped database?

Hi,
You can not backup a database or log that is standby mode
with regular backups.
As a work around, you have to restore the database and
then back it up.
Here is something you could use:
USE MASTER
RESTORE DATABASE DB_NAME
WITH RECOVERY
This changes the standby status to normal db use and then
you can back it up.
The only thing that I am not sure is what happens at the
next log shipped/restored because it depends how you have
it setup.
hth
DeeJay
>--Original Message--
>(SQL Server 2000, SP3a)
>Hello all!
>I've got a database that is the secondary server in a log-
shipped pair. Whenever I try
>and do a BACKUP on this database, I get an error message
that the database is in a
>READ-ONLY STANDBY mode.
>Is there any way to circumvent this, temporarily, and
make a database backup of a
>log-shipped database?
>Thanks!
>
>.
>Thanks DeeJay!
What we're thinking (if you'll humor us for a moment):
* Temporarily disable the Job that's responsible for processing the log-ship
ped
transaction logs.
* Change the status of the database to get it out of STANDBY mode (as per yo
ur
recommendation).
* Back up the database.
* Change the status of the database back to STANDBY (dunno how to do this ye
t).
* Re-enable the Job.
I'm not sure if this is advisable, though. For instance, what does the Log-
Shipping
Monitor service do? Is it sensitive to any of the proposed elements above?
Thanks for any additional help you can provide! :-)
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:3a6001c48f88$24958740$a301280a@.phx.gbl...[vbcol=seagreen]
> Hi,
> You can not backup a database or log that is standby mode
> with regular backups.
> As a work around, you have to restore the database and
> then back it up.
> Here is something you could use:
> USE MASTER
> RESTORE DATABASE DB_NAME
> WITH RECOVERY
> This changes the standby status to normal db use and then
> you can back it up.
> The only thing that I am not sure is what happens at the
> next log shipped/restored because it depends how you have
> it setup.
> hth
> DeeJay
> shipped pair. Whenever I try
> that the database is in a
> make a database backup of a|||Hi John,

> * Change the status of the database back to STANDBY (dunno how to do this yet).[/v
bcol]
No can do. The recovery procedures etc in SQL Server aren't written to handl
e this scenario, quite simply. Any
way you can grab the log backups already on the fallback machine?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Peterson" <j0hnp@.comcast.net> wrote in message news:uXJ9wm4jEHA.632@.TK2MSFTNGP12.phx.g
bl...[vbcol=seagreen]
> Thanks DeeJay!
> What we're thinking (if you'll humor us for a moment):
> * Temporarily disable the Job that's responsible for processing the log-sh
ipped
> transaction logs.
> * Change the status of the database to get it out of STANDBY mode (as per
your
> recommendation).
> * Back up the database.
> * Change the status of the database back to STANDBY (dunno how to do this
yet).
> * Re-enable the Job.
> I'm not sure if this is advisable, though. For instance, what does the Lo
g-Shipping
> Monitor service do? Is it sensitive to any of the proposed elements above
?
> Thanks for any additional help you can provide! :-)
>
> "DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> news:3a6001c48f88$24958740$a301280a@.phx.gbl...
>|||Thanks Tibor. I don't think that we have the main database backup available
from the
fallback machine.
Any way we can use an undocumented "flag" in some system table to toggle the
DB back in
STANDBY mode? ;-)
Just to give you the heads up: we've got our production databases log-shipp
ed into our
corporate network. If we can leverage these log-shipped databases, we won't
have to pay
the network price to copy from production again.
Thanks for any additional help you might be able to provide! :-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:uDrJTt4jEHA.2908@.TK2MSFTNGP10.phx.gbl...
> Hi John,
>
> No can do. The recovery procedures etc in SQL Server aren't written to han
dle this
> scenario, quite simply. Any
> way you can grab the log backups already on the fallback machine?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:uXJ9wm4jEHA.632@.TK2MSFTNGP12.phx.gbl...
>|||You might want to test this out in dev first.
I am not sure if this is supported by MS or if your log
shipping will work properly again.
You might have to re-configure log-shipping.
DeeJay
>--Original Message--
>Thanks Tibor. I don't think that we have the main
database backup available from the
>fallback machine.
>Any way we can use an undocumented "flag" in some system
table to toggle the DB back in
>STANDBY mode? ;-)
>Just to give you the heads up: we've got our production
databases log-shipped into our
>corporate network. If we can leverage these log-shipped
databases, we won't have to pay
>the network price to copy from production again.
>Thanks for any additional help you might be able to
provide! :-)
>
>"Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in message
>news:uDrJTt4jEHA.2908@.TK2MSFTNGP10.phx.gbl...
(dunno how to do this yet).[vbcol=seagreen]
aren't written to handle this[vbcol=seagreen]
fallback machine?[vbcol=seagreen]
processing the log-shipped[vbcol=seagreen]
STANDBY mode (as per your[vbcol=seagreen]
(dunno how to do this yet).[vbcol=seagreen]
instance, what does the Log-Shipping[vbcol=seagreen]
proposed elements above?[vbcol=seagreen]
mode[vbcol=seagreen]
and[vbcol=seagreen]
then[vbcol=seagreen]
the[vbcol=seagreen]
have[vbcol=seagreen]
a log-[vbcol=seagreen]
message[vbcol=seagreen]
>
>.
>|||> Any way we can use an undocumented "flag" in some system table to toggle the DB back in[vb
col=seagreen]
> STANDBY mode? ;-)[/vbcol]
Nope, none that I know of. Just think about it. When you bring a db out of s
tandby mode, you get the recovery
work persisted. Stuff has been rolled forward and rolled back. Period. How w
ould you be able to apply a later
log backup onto this, as the log records in that log backup are totally out-
of sync with the database you have
performed a permanent recovery on?
For this to work, MS would need to do some changes in how the recovery proce
ss work or give us some other
option for recovery (NO_RECOVERY, RECOVERY, STANDBY, QUASI_PERMANENT_RECOVER
Y).
Or MS would need to change SQL Server so it allow us to do a backup of a dat
abase in STANDBY mode. (And here
I'm too tired right now to consider what ramifications that would have on lo
g record sequencing and recovery
;-) ).
I know this question has been on the table before, so you might want to chec
k the archives to see if someone
came up with anything. I have a feeling that you are out of luck, though...
Perhaps you should opt for a home-grown log shipping solution, to give you b
etter control of handling of the
backup files?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Peterson" <j0hnp@.comcast.net> wrote in message news:%23UXfE54jEHA.2412@.TK2MSFTNGP15.ph
x.gbl...
> Thanks Tibor. I don't think that we have the main database backup availab
le from the
> fallback machine.
> Any way we can use an undocumented "flag" in some system table to toggle t
he DB back in
> STANDBY mode? ;-)
> Just to give you the heads up: we've got our production databases log-shi
pped into our
> corporate network. If we can leverage these log-shipped databases, we won
't have to pay
> the network price to copy from production again.
> Thanks for any additional help you might be able to provide! :-)
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:uDrJTt4jEHA.2908@.TK2MSFTNGP10.phx.gbl...
>|||Thanks Tibor! It's clear I don't understand the whole RECOVERY business.
I had *hoped* that, by temporarily suspending the log file processing, I cou
ld somehow get
the DB in a state where it was backup-able. But, if I'm understanding you c
orrectly, it
sounds as if, by virtue of performing a backup on the DB, I'd be "marking" t
he transaction
log in such a way as to be incompatible with the normal log files when they'
re later
resumed.
Could I, then, do a detach and copy the underlying .MDF/.LDF files? Or woul
d that break
the whole log-shipping "linkage"?
Thanks again!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:%23tykrd5jEHA.3896@.TK2MSFTNGP15.phx.gbl...
> Nope, none that I know of. Just think about it. When you bring a db out of
standby mode,
> you get the recovery
> work persisted. Stuff has been rolled forward and rolled back. Period. How
would you be
> able to apply a later
> log backup onto this, as the log records in that log backup are totally ou
t-of sync with
> the database you have
> performed a permanent recovery on?
> For this to work, MS would need to do some changes in how the recovery pro
cess work or
> give us some other
> option for recovery (NO_RECOVERY, RECOVERY, STANDBY, QUASI_PERMANENT_RECOV
ERY).
> Or MS would need to change SQL Server so it allow us to do a backup of a d
atabase in
> STANDBY mode. (And here
> I'm too tired right now to consider what ramifications that would have on
log record
> sequencing and recovery
> ;-) ).
> I know this question has been on the table before, so you might want to ch
eck the
> archives to see if someone
> came up with anything. I have a feeling that you are out of luck, though..
.
> Perhaps you should opt for a home-grown log shipping solution, to give you
better
> control of handling of the
> backup files?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:%23UXfE54jEHA.2412@.TK2MSFTNGP15.phx.gbl...
>|||I think that when you do recovery, log records are either removed or added (
possibly both) to the
transaction log. This means that a later log backup from the production data
base will not just be
able to add the log records to the log-shipped database, because the transac
tion log has been
changed. The LSN (log sequence numbers) doesn't match anymore.
I haven't tested whether you can detach and attach a database a database in
STANDBY mode. Give it a
try. If not, you might consider stopping SQL server and just grabbing the fi
les. Not supported and
not guaranteed that you can attach such files, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Peterson" <j0hnp@.comcast.net> wrote in message news:O991LI6jEHA.1348@.TK2MSFTNGP15.phx.
gbl...
> Thanks Tibor! It's clear I don't understand the whole RECOVERY business.
> I had *hoped* that, by temporarily suspending the log file processing, I c
ould somehow get
> the DB in a state where it was backup-able. But, if I'm understanding you
correctly, it
> sounds as if, by virtue of performing a backup on the DB, I'd be "marking"
the transaction
> log in such a way as to be incompatible with the normal log files when the
y're later
> resumed.
> Could I, then, do a detach and copy the underlying .MDF/.LDF files? Or wo
uld that break
> the whole log-shipping "linkage"?
> Thanks again!
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23tykrd5jEHA.3896@.TK2MSFTNGP15.phx.gbl...
>sql

Monday, March 19, 2012

how can attack database from sqlserver management studio express?

I have created a database named s_office,now i want add file data.MDF and log.LDF into it. .How can i do it?Could you show me the script that attack file .MDF and .LDF into database.thankxhttp://msdn2.microsoft.com/en-us/library/ms179877.aspx

Monday, March 12, 2012

How big is my log?

i'm trying to do a bcp load of a database. i'm *trying* to get the bcp load
to be non-logged. But there is no way to tell if it actually is logging the
load or not.
So i assume i should continously run some query to tell me the transaction
log size/used/free.
What is the command to get transaction log size?
You can run the next DBCC statment and you can get the size for the T-logs
dbcc SQLPERF(LOGSPACE)
if you want to know only the size of the current t-log , then you should to
use this:
SP_helpfile <LOG_FILE_NAME>
Pancho
"Ian Boyd" wrote:

> i'm trying to do a bcp load of a database. i'm *trying* to get the bcp load
> to be non-logged. But there is no way to tell if it actually is logging the
> load or not.
> So i assume i should continously run some query to tell me the transaction
> log size/used/free.
>
> What is the command to get transaction log size?
>
>
|||Hi Ian,
If its possible, you can change the recovery mode of the database to simple,
which will not log the BCP transactions. And you can revert back the recovery
mode.
The other alternative is, let the transaction log grow (if you have enough
space), and then truncate the log at the later stage.
The above solution is based on the assumption that currently you have Full
recovery mode on the database.
Thanks
Yogish

How big is my log?

i'm trying to do a bcp load of a database. i'm *trying* to get the bcp load
to be non-logged. But there is no way to tell if it actually is logging the
load or not.
So i assume i should continously run some query to tell me the transaction
log size/used/free.
What is the command to get transaction log size?You can run the next DBCC statment and you can get the size for the T-logs
dbcc SQLPERF(LOGSPACE)
if you want to know only the size of the current t-log , then you should to
use this:
SP_helpfile <LOG_FILE_NAME>
Pancho
"Ian Boyd" wrote:

> i'm trying to do a bcp load of a database. i'm *trying* to get the bcp loa
d
> to be non-logged. But there is no way to tell if it actually is logging th
e
> load or not.
> So i assume i should continously run some query to tell me the transaction
> log size/used/free.
>
> What is the command to get transaction log size?
>
>|||Hi Ian,
If its possible, you can change the recovery mode of the database to simple,
which will not log the BCP transactions. And you can revert back the recover
y
mode.
The other alternative is, let the transaction log grow (if you have enough
space), and then truncate the log at the later stage.
The above solution is based on the assumption that currently you have Full
recovery mode on the database.
Thanks
Yogish

How big is my log?

i'm trying to do a bcp load of a database. i'm *trying* to get the bcp load
to be non-logged. But there is no way to tell if it actually is logging the
load or not.
So i assume i should continously run some query to tell me the transaction
log size/used/free.
What is the command to get transaction log size?You can run the next DBCC statment and you can get the size for the T-logs
dbcc SQLPERF(LOGSPACE)
if you want to know only the size of the current t-log , then you should to
use this:
SP_helpfile <LOG_FILE_NAME>
Pancho
"Ian Boyd" wrote:
> i'm trying to do a bcp load of a database. i'm *trying* to get the bcp load
> to be non-logged. But there is no way to tell if it actually is logging the
> load or not.
> So i assume i should continously run some query to tell me the transaction
> log size/used/free.
>
> What is the command to get transaction log size?
>
>|||Hi Ian,
If its possible, you can change the recovery mode of the database to simple,
which will not log the BCP transactions. And you can revert back the recovery
mode.
The other alternative is, let the transaction log grow (if you have enough
space), and then truncate the log at the later stage.
The above solution is based on the assumption that currently you have Full
recovery mode on the database.
--
Thanks
Yogish

Friday, March 9, 2012

How analysing SQL Server log to correct Navision code

0x5D98D550) Value:0x5cc8d000 Cost

0/0)
2006-10-23 15:41:48.62 spid4
2006-10-23 15:41:48.62 spid4 Node:2
2006-10-23 15:41:48.62 spid4 Port: 0x42bf0100 Xid Slot: 0, EC:
0x72dd7550, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeOpenWorkers
2006-10-23 15:41:48.62 spid4 Coordinator: EC = 0x72dd7550, SPID: 56,
ECID: 0, Not Blocking
2006-10-23 15:41:48.62 spid4 Consumer List::
2006-10-23 15:41:48.62 spid4 Consumer: Xid Slot: 0, EC = 0x72dd7550,
SPID: 56, ECID: 0, Blocking
2006-10-23 15:41:48.62 spid4 Producer List::
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 1, EC = 0x23790098,
SPID: 56, ECID: 2, Blocking
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 2, EC = 0x23786098,
SPID: 56, ECID: 4, Blocking
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 3, EC = 0x2379e098,
SPID: 56, ECID: 1, Blocking
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 4, EC = 0x2378e098,
SPID: 56, ECID: 3, Blocking
2006-10-23 15:41:48.62 spid4
2006-10-23 15:41:48.62 spid4 Node:3
2006-10-23 15:41:48.62 spid4 PAG: 7:3:526003 CleanCnt:4
Mode: IU Flags: 0x2
2006-10-23 15:41:48.62 spid4 Wait List:
2006-10-23 15:41:48.62 spid4 Owner:0x5cbd0de0 Mode: U Flg:0x0
Ref:1 Life:02000000 SPID:56 ECID:1
2006-10-23 15:41:48.62 spid4 Requested By:
2006-10-23 15:41:48.62 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:56 ECID:2 Ec

0x23790098) Value:0x5cbd0e20 Cost

0/1A68)
2006-10-23 15:41:48.62 spid4
2006-10-23 15:41:48.62 spid4 Node:4
2006-10-23 15:41:48.62 spid4 PAG: 7:3:526003 CleanCnt:4
Mode: IU Flags: 0x2
2006-10-23 15:41:48.62 spid4 Grant List 0::
2006-10-23 15:41:48.62 spid4 Owner:0x5d007180 Mode: IU Flg:0x0
Ref:1 Life:02000000 SPID:63 ECID:0
2006-10-23 15:41:48.62 spid4 SPID: 63 ECID: 0 Statement Type: UPDATE
Line #: 1
2006-10-23 15:41:48.62 spid4 Input Buf: RPC Event: sp_execute;1
2006-10-23 15:41:48.62 spid4 Grant List 1::
2006-10-23 15:41:48.62 spid4 Requested By:
2006-10-23 15:41:48.62 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:56 ECID:1 Ec

0x2379E098) Value:0x5cbd0de0 Cost

0x23786098) Value:0x5cbd0e40 Cost

0/1A68)
2006-10-23 15:41:48.62 spid4
2006-10-23 15:41:48.62 spid4 -- next branch --
2006-10-23 15:41:48.62 spid4
2006-10-23 15:41:48.62 spid4
2006-10-23 15:41:48.62 spid4 Node:2
2006-10-23 15:41:48.62 spid4 Port: 0x42bf0100 Xid Slot: 0, EC:
0x72dd7550, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeOpenWorkers
2006-10-23 15:41:48.62 spid4 Coordinator: EC = 0x72dd7550, SPID: 56,
ECID: 0, Not Blocking
2006-10-23 15:41:48.62 spid4 Consumer List::
2006-10-23 15:41:48.62 spid4 Consumer: Xid Slot: 0, EC = 0x72dd7550,
SPID: 56, ECID: 0, Blocking
2006-10-23 15:41:48.62 spid4 Producer List::
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 1, EC = 0x23790098,
SPID: 56, ECID: 2, Blocking
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 2, EC = 0x23786098,
SPID: 56, ECID: 4, Blocking
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 3, EC = 0x2379e098,
SPID: 56, ECID: 1, Blocking
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 4, EC = 0x2378e098,
SPID: 56, ECID: 3, Blocking
2006-10-23 15:41:48.62 spid4
2006-10-23 15:41:48.62 spid4 -- next branch --
2006-10-23 15:41:48.62 spid4
2006-10-23 15:41:48.62 spid4
2006-10-23 15:41:48.62 spid4 Node:2
2006-10-23 15:41:48.62 spid4 Port: 0x42bf0100 Xid Slot: 0, EC:
0x72dd7550, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeOpenWorkers
2006-10-23 15:41:48.62 spid4 Coordinator: EC = 0x72dd7550, SPID: 56,
ECID: 0, Not Blocking
2006-10-23 15:41:48.62 spid4 Consumer List::
2006-10-23 15:41:48.62 spid4 Consumer: Xid Slot: 0, EC = 0x72dd7550,
SPID: 56, ECID: 0, Blocking
2006-10-23 15:41:48.62 spid4 Producer List::
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 1, EC = 0x23790098,
SPID: 56, ECID: 2, Blocking
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 2, EC = 0x23786098,
SPID: 56, ECID: 4, Blocking
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 3, EC = 0x2379e098,
SPID: 56, ECID: 1, Blocking
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 4, EC = 0x2378e098,
SPID: 56, ECID: 3, Blocking
2006-10-23 15:41:48.62 spid4
2006-10-23 15:41:48.62 spid4 Node:12
2006-10-23 15:41:48.62 spid4 PAG: 7:3:526003 CleanCnt:4
Mode: IU Flags: 0x2
2006-10-23 15:41:48.62 spid4 Wait List:
2006-10-23 15:41:48.62 spid4 Owner:0x5cbd0de0 Mode: U Flg:0x0
Ref:1 Life:02000000 SPID:56 ECID:1
2006-10-23 15:41:48.62 spid4 Requested By:
2006-10-23 15:41:48.62 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:56 ECID:3 Ec

0x2378E098) Value:0x5cbd0e00 Cost

0x5D98D550) Value:0x5cc8d000 Cost

0/0)
I don't know the meanings or the goal of some terms: Life? Stype? EC? Xid
Slot? e_etypeOpenWorkers?
I want to help my Navision programmers and so I want to indicate some
suggests to solve this issue.
Many thanks
What is Navision 3.70a?
Its more info about SQL Server 2005, but there are some links to SQL Server
2000 as well
http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:1E5B01B8-62BE-4BB8-9855-8782B5BC7875@.microsoft.com...
>I need to exam the SQL Server log to find the reasons of some deadlocks
> occured for a Navision application.
> I use SQL Server 2000 and Navision 3.70a. I have experience about SQL
> Server
> but not Navision.
> I have already some documentation about SQL Server log. I have used the
> parameters /T1204 e /T3605 to enable a trace in the log.
> Now, I want to find the rigth point inside environment Navision where the
> code must be modified.
> Below, I put the deadlock information registered:
> Node:1
> 2006-10-23 15:41:48.62 spid4 KEY: 7:1829581556:1 (1500fdbebb1d)
> CleanCnt:1 Mode: Range-S-U Flags: 0x0
> 2006-10-23 15:41:48.62 spid4 Grant List 1::
> 2006-10-23 15:41:48.62 spid4 Owner:0x5d140580 Mode: Range-S-U
> Flg:0x0
> Ref:1 Life:02000000 SPID:56 ECID:0
> 2006-10-23 15:41:48.62 spid4 SPID: 56 ECID: 0 Statement Type:
> SELECT
> Line #: 1
> 2006-10-23 15:41:48.62 spid4 Input Buf: RPC Event: sp_prepexec;1
> 2006-10-23 15:41:48.62 spid4 Requested By:
> 2006-10-23 15:41:48.62 spid4 ResType:LockOwner Stype:'OR' Mode:
> Range-S-U SPID:63 ECID:0 Ec

0x5D98D550) Value:0x5cc8d000 Cost

0/0)
> 2006-10-23 15:41:48.62 spid4
> 2006-10-23 15:41:48.62 spid4 Node:2
> 2006-10-23 15:41:48.62 spid4 Port: 0x42bf0100 Xid Slot: 0, EC:
> 0x72dd7550, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeOpenWorkers
> 2006-10-23 15:41:48.62 spid4 Coordinator: EC = 0x72dd7550, SPID: 56,
> ECID: 0, Not Blocking
> 2006-10-23 15:41:48.62 spid4 Consumer List::
> 2006-10-23 15:41:48.62 spid4 Consumer: Xid Slot: 0, EC = 0x72dd7550,
> SPID: 56, ECID: 0, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer List::
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 1, EC = 0x23790098,
> SPID: 56, ECID: 2, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 2, EC = 0x23786098,
> SPID: 56, ECID: 4, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 3, EC = 0x2379e098,
> SPID: 56, ECID: 1, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 4, EC = 0x2378e098,
> SPID: 56, ECID: 3, Blocking
> 2006-10-23 15:41:48.62 spid4
> 2006-10-23 15:41:48.62 spid4 Node:3
> 2006-10-23 15:41:48.62 spid4 PAG: 7:3:526003 CleanCnt:4
> Mode: IU Flags: 0x2
> 2006-10-23 15:41:48.62 spid4 Wait List:
> 2006-10-23 15:41:48.62 spid4 Owner:0x5cbd0de0 Mode: U
> Flg:0x0
> Ref:1 Life:02000000 SPID:56 ECID:1
> 2006-10-23 15:41:48.62 spid4 Requested By:
> 2006-10-23 15:41:48.62 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:56 ECID:2 Ec

0x23790098) Value:0x5cbd0e20 Cost

0/1A68)
> 2006-10-23 15:41:48.62 spid4
> 2006-10-23 15:41:48.62 spid4 Node:4
> 2006-10-23 15:41:48.62 spid4 PAG: 7:3:526003 CleanCnt:4
> Mode: IU Flags: 0x2
> 2006-10-23 15:41:48.62 spid4 Grant List 0::
> 2006-10-23 15:41:48.62 spid4 Owner:0x5d007180 Mode: IU
> Flg:0x0
> Ref:1 Life:02000000 SPID:63 ECID:0
> 2006-10-23 15:41:48.62 spid4 SPID: 63 ECID: 0 Statement Type:
> UPDATE
> Line #: 1
> 2006-10-23 15:41:48.62 spid4 Input Buf: RPC Event: sp_execute;1
> 2006-10-23 15:41:48.62 spid4 Grant List 1::
> 2006-10-23 15:41:48.62 spid4 Requested By:
> 2006-10-23 15:41:48.62 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:56 ECID:1 Ec

0x2379E098) Value:0x5cbd0de0 Cost

0x23786098) Value:0x5cbd0e40 Cost

0/1A68)
> 2006-10-23 15:41:48.62 spid4
> 2006-10-23 15:41:48.62 spid4 -- next branch --
> 2006-10-23 15:41:48.62 spid4
> 2006-10-23 15:41:48.62 spid4
> 2006-10-23 15:41:48.62 spid4 Node:2
> 2006-10-23 15:41:48.62 spid4 Port: 0x42bf0100 Xid Slot: 0, EC:
> 0x72dd7550, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeOpenWorkers
> 2006-10-23 15:41:48.62 spid4 Coordinator: EC = 0x72dd7550, SPID: 56,
> ECID: 0, Not Blocking
> 2006-10-23 15:41:48.62 spid4 Consumer List::
> 2006-10-23 15:41:48.62 spid4 Consumer: Xid Slot: 0, EC = 0x72dd7550,
> SPID: 56, ECID: 0, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer List::
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 1, EC = 0x23790098,
> SPID: 56, ECID: 2, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 2, EC = 0x23786098,
> SPID: 56, ECID: 4, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 3, EC = 0x2379e098,
> SPID: 56, ECID: 1, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 4, EC = 0x2378e098,
> SPID: 56, ECID: 3, Blocking
> 2006-10-23 15:41:48.62 spid4
> 2006-10-23 15:41:48.62 spid4 -- next branch --
> 2006-10-23 15:41:48.62 spid4
> 2006-10-23 15:41:48.62 spid4
> 2006-10-23 15:41:48.62 spid4 Node:2
> 2006-10-23 15:41:48.62 spid4 Port: 0x42bf0100 Xid Slot: 0, EC:
> 0x72dd7550, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeOpenWorkers
> 2006-10-23 15:41:48.62 spid4 Coordinator: EC = 0x72dd7550, SPID: 56,
> ECID: 0, Not Blocking
> 2006-10-23 15:41:48.62 spid4 Consumer List::
> 2006-10-23 15:41:48.62 spid4 Consumer: Xid Slot: 0, EC = 0x72dd7550,
> SPID: 56, ECID: 0, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer List::
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 1, EC = 0x23790098,
> SPID: 56, ECID: 2, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 2, EC = 0x23786098,
> SPID: 56, ECID: 4, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 3, EC = 0x2379e098,
> SPID: 56, ECID: 1, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 4, EC = 0x2378e098,
> SPID: 56, ECID: 3, Blocking
> 2006-10-23 15:41:48.62 spid4
> 2006-10-23 15:41:48.62 spid4 Node:12
> 2006-10-23 15:41:48.62 spid4 PAG: 7:3:526003 CleanCnt:4
> Mode: IU Flags: 0x2
> 2006-10-23 15:41:48.62 spid4 Wait List:
> 2006-10-23 15:41:48.62 spid4 Owner:0x5cbd0de0 Mode: U
> Flg:0x0
> Ref:1 Life:02000000 SPID:56 ECID:1
> 2006-10-23 15:41:48.62 spid4 Requested By:
> 2006-10-23 15:41:48.62 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:56 ECID:3 Ec

0x2378E098) Value:0x5cbd0e00 Cost

0x5D98D550) Value:0x5cc8d000 Cost

0/0)
> I don't know the meanings or the goal of some terms: Life? Stype? EC? Xid
> Slot? e_etypeOpenWorkers?
> I want to help my Navision programmers and so I want to indicate some
> suggests to solve this issue.
> Many thanks
>
>
|||Navision is the Microsoft ERP. Now its name is Dynamics NAV.
"Uri Dimant" wrote:

> What is Navision 3.70a?
> Its more info about SQL Server 2005, but there are some links to SQL Server
> 2000 as well
> http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
>
>
>
> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
> news:1E5B01B8-62BE-4BB8-9855-8782B5BC7875@.microsoft.com...
>
>

How analysing SQL Server log to correct Navision code

0x5D98D550) Value:0x5cc8d000 Cost

0x23790098) Value:0x5cbd0e20 Cost

0x2379E098) Value:0x5cbd0de0 Cost

0x23786098) Value:0x5cbd0e40 Cost

0x2378E098) Value:0x5cbd0e00 Cost

0x5D98D550) Value:0x5cc8d000 Cost

0/0)
I don't know the meanings or the goal of some terms: Life? Stype? EC? Xid
Slot? e_etypeOpenWorkers?
I want to help my Navision programmers and so I want to indicate some
suggests to solve this issue.
Many thanksWhat is Navision 3.70a?
Its more info about SQL Server 2005, but there are some links to SQL Server
2000 as well
http://blogs.msdn.com/bartd/archive...
-1.aspx
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:1E5B01B8-62BE-4BB8-9855-8782B5BC7875@.microsoft.com...
>I need to exam the SQL Server log to find the reasons of some deadlocks
> occured for a Navision application.
> I use SQL Server 2000 and Navision 3.70a. I have experience about SQL
> Server
> but not Navision.
> I have already some documentation about SQL Server log. I have used the
> parameters /T1204 e /T3605 to enable a trace in the log.
> Now, I want to find the rigth point inside environment Navision where the
> code must be modified.
> Below, I put the deadlock information registered:
> Node:1
> 2006-10-23 15:41:48.62 spid4 KEY: 7:1829581556:1 (1500fdbebb1d)
> CleanCnt:1 Mode: Range-S-U Flags: 0x0
> 2006-10-23 15:41:48.62 spid4 Grant List 1::
> 2006-10-23 15:41:48.62 spid4 Owner:0x5d140580 Mode: Range-S-U
> Flg:0x0
> Ref:1 Life:02000000 SPID:56 ECID:0
> 2006-10-23 15:41:48.62 spid4 SPID: 56 ECID: 0 Statement Type:
> SELECT
> Line #: 1
> 2006-10-23 15:41:48.62 spid4 Input Buf: RPC Event: sp_prepexec;1
> 2006-10-23 15:41:48.62 spid4 Requested By:
> 2006-10-23 15:41:48.62 spid4 ResType:LockOwner Stype:'OR' Mode:
> Range-S-U SPID:63 ECID:0 Ec

0x5D98D550) Value:0x5cc8d000 Cost

0x23790098) Value:0x5cbd0e20 Cost

0x2379E098) Value:0x5cbd0de0 Cost

0x23786098) Value:0x5cbd0e40 Cost

0x2378E098) Value:0x5cbd0e00 Cost

0x5D98D550) Value:0x5cc8d000 Cost

0/0)
> I don't know the meanings or the goal of some terms: Life? Stype? EC? Xid
> Slot? e_etypeOpenWorkers?
> I want to help my Navision programmers and so I want to indicate some
> suggests to solve this issue.
> Many thanks
>
>|||Navision is the Microsoft ERP. Now its name is Dynamics NAV.
"Uri Dimant" wrote:

> What is Navision 3.70a?
> Its more info about SQL Server 2005, but there are some links to SQL Serv
er
> 2000 as well
> http://blogs.msdn.com/bartd/archive...r />
rt-1.aspx
>
>
>
> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
> news:1E5B01B8-62BE-4BB8-9855-8782B5BC7875@.microsoft.com...
>
>

How analysing SQL Server log to correct Navision code

I need to exam the SQL Server log to find the reasons of some deadlocks
occured for a Navision application.
I use SQL Server 2000 and Navision 3.70a. I have experience about SQL Server
but not Navision.
I have already some documentation about SQL Server log. I have used the
parameters /T1204 e /T3605 to enable a trace in the log.
Now, I want to find the rigth point inside environment Navision where the
code must be modified.
Below, I put the deadlock information registered:
Node:1
2006-10-23 15:41:48.62 spid4 KEY: 7:1829581556:1 (1500fdbebb1d)
CleanCnt:1 Mode: Range-S-U Flags: 0x0
2006-10-23 15:41:48.62 spid4 Grant List 1::
2006-10-23 15:41:48.62 spid4 Owner:0x5d140580 Mode: Range-S-U Flg:0x0
Ref:1 Life:02000000 SPID:56 ECID:0
2006-10-23 15:41:48.62 spid4 SPID: 56 ECID: 0 Statement Type: SELECT
Line #: 1
2006-10-23 15:41:48.62 spid4 Input Buf: RPC Event: sp_prepexec;1
2006-10-23 15:41:48.62 spid4 Requested By:
2006-10-23 15:41:48.62 spid4 ResType:LockOwner Stype:'OR' Mode:
Range-S-U SPID:63 ECID:0 Ec:(0x5D98D550) Value:0x5cc8d000 Cost:(0/0)
2006-10-23 15:41:48.62 spid4
2006-10-23 15:41:48.62 spid4 Node:2
2006-10-23 15:41:48.62 spid4 Port: 0x42bf0100 Xid Slot: 0, EC:
0x72dd7550, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeOpenWorkers
2006-10-23 15:41:48.62 spid4 Coordinator: EC = 0x72dd7550, SPID: 56,
ECID: 0, Not Blocking
2006-10-23 15:41:48.62 spid4 Consumer List::
2006-10-23 15:41:48.62 spid4 Consumer: Xid Slot: 0, EC = 0x72dd7550,
SPID: 56, ECID: 0, Blocking
2006-10-23 15:41:48.62 spid4 Producer List::
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 1, EC = 0x23790098,
SPID: 56, ECID: 2, Blocking
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 2, EC = 0x23786098,
SPID: 56, ECID: 4, Blocking
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 3, EC = 0x2379e098,
SPID: 56, ECID: 1, Blocking
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 4, EC = 0x2378e098,
SPID: 56, ECID: 3, Blocking
2006-10-23 15:41:48.62 spid4
2006-10-23 15:41:48.62 spid4 Node:3
2006-10-23 15:41:48.62 spid4 PAG: 7:3:526003 CleanCnt:4
Mode: IU Flags: 0x2
2006-10-23 15:41:48.62 spid4 Wait List:
2006-10-23 15:41:48.62 spid4 Owner:0x5cbd0de0 Mode: U Flg:0x0
Ref:1 Life:02000000 SPID:56 ECID:1
2006-10-23 15:41:48.62 spid4 Requested By:
2006-10-23 15:41:48.62 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:56 ECID:2 Ec:(0x23790098) Value:0x5cbd0e20 Cost:(0/1A68)
2006-10-23 15:41:48.62 spid4
2006-10-23 15:41:48.62 spid4 Node:4
2006-10-23 15:41:48.62 spid4 PAG: 7:3:526003 CleanCnt:4
Mode: IU Flags: 0x2
2006-10-23 15:41:48.62 spid4 Grant List 0::
2006-10-23 15:41:48.62 spid4 Owner:0x5d007180 Mode: IU Flg:0x0
Ref:1 Life:02000000 SPID:63 ECID:0
2006-10-23 15:41:48.62 spid4 SPID: 63 ECID: 0 Statement Type: UPDATE
Line #: 1
2006-10-23 15:41:48.62 spid4 Input Buf: RPC Event: sp_execute;1
2006-10-23 15:41:48.62 spid4 Grant List 1::
2006-10-23 15:41:48.62 spid4 Requested By:
2006-10-23 15:41:48.62 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:56 ECID:1 Ec:(0x2379E098) Value:0x5cbd0de0 Cost:(0/1A68)
2006-10-23 15:41:48.62 spid4
2006-10-23 15:41:48.62 spid4 -- next branch --
2006-10-23 15:41:48.62 spid4
2006-10-23 15:41:48.62 spid4
2006-10-23 15:41:48.62 spid4 Node:2
2006-10-23 15:41:48.62 spid4 Port: 0x42bf0100 Xid Slot: 0, EC:
0x72dd7550, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeOpenWorkers
2006-10-23 15:41:48.62 spid4 Coordinator: EC = 0x72dd7550, SPID: 56,
ECID: 0, Not Blocking
2006-10-23 15:41:48.62 spid4 Consumer List::
2006-10-23 15:41:48.62 spid4 Consumer: Xid Slot: 0, EC = 0x72dd7550,
SPID: 56, ECID: 0, Blocking
2006-10-23 15:41:48.62 spid4 Producer List::
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 1, EC = 0x23790098,
SPID: 56, ECID: 2, Blocking
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 2, EC = 0x23786098,
SPID: 56, ECID: 4, Blocking
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 3, EC = 0x2379e098,
SPID: 56, ECID: 1, Blocking
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 4, EC = 0x2378e098,
SPID: 56, ECID: 3, Blocking
2006-10-23 15:41:48.62 spid4
2006-10-23 15:41:48.62 spid4 Node:7
2006-10-23 15:41:48.62 spid4 PAG: 7:3:526003 CleanCnt:4
Mode: IU Flags: 0x2
2006-10-23 15:41:48.62 spid4 Wait List:
2006-10-23 15:41:48.62 spid4 Owner:0x5cbd0de0 Mode: U Flg:0x0
Ref:1 Life:02000000 SPID:56 ECID:1
2006-10-23 15:41:48.62 spid4 Requested By:
2006-10-23 15:41:48.62 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:56 ECID:4 Ec:(0x23786098) Value:0x5cbd0e40 Cost:(0/1A68)
2006-10-23 15:41:48.62 spid4
2006-10-23 15:41:48.62 spid4 -- next branch --
2006-10-23 15:41:48.62 spid4
2006-10-23 15:41:48.62 spid4
2006-10-23 15:41:48.62 spid4 Node:2
2006-10-23 15:41:48.62 spid4 Port: 0x42bf0100 Xid Slot: 0, EC:
0x72dd7550, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeOpenWorkers
2006-10-23 15:41:48.62 spid4 Coordinator: EC = 0x72dd7550, SPID: 56,
ECID: 0, Not Blocking
2006-10-23 15:41:48.62 spid4 Consumer List::
2006-10-23 15:41:48.62 spid4 Consumer: Xid Slot: 0, EC = 0x72dd7550,
SPID: 56, ECID: 0, Blocking
2006-10-23 15:41:48.62 spid4 Producer List::
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 1, EC = 0x23790098,
SPID: 56, ECID: 2, Blocking
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 2, EC = 0x23786098,
SPID: 56, ECID: 4, Blocking
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 3, EC = 0x2379e098,
SPID: 56, ECID: 1, Blocking
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 4, EC = 0x2378e098,
SPID: 56, ECID: 3, Blocking
2006-10-23 15:41:48.62 spid4
2006-10-23 15:41:48.62 spid4 -- next branch --
2006-10-23 15:41:48.62 spid4
2006-10-23 15:41:48.62 spid4
2006-10-23 15:41:48.62 spid4 Node:2
2006-10-23 15:41:48.62 spid4 Port: 0x42bf0100 Xid Slot: 0, EC:
0x72dd7550, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeOpenWorkers
2006-10-23 15:41:48.62 spid4 Coordinator: EC = 0x72dd7550, SPID: 56,
ECID: 0, Not Blocking
2006-10-23 15:41:48.62 spid4 Consumer List::
2006-10-23 15:41:48.62 spid4 Consumer: Xid Slot: 0, EC = 0x72dd7550,
SPID: 56, ECID: 0, Blocking
2006-10-23 15:41:48.62 spid4 Producer List::
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 1, EC = 0x23790098,
SPID: 56, ECID: 2, Blocking
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 2, EC = 0x23786098,
SPID: 56, ECID: 4, Blocking
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 3, EC = 0x2379e098,
SPID: 56, ECID: 1, Blocking
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 4, EC = 0x2378e098,
SPID: 56, ECID: 3, Blocking
2006-10-23 15:41:48.62 spid4
2006-10-23 15:41:48.62 spid4 Node:12
2006-10-23 15:41:48.62 spid4 PAG: 7:3:526003 CleanCnt:4
Mode: IU Flags: 0x2
2006-10-23 15:41:48.62 spid4 Wait List:
2006-10-23 15:41:48.62 spid4 Owner:0x5cbd0de0 Mode: U Flg:0x0
Ref:1 Life:02000000 SPID:56 ECID:1
2006-10-23 15:41:48.62 spid4 Requested By:
2006-10-23 15:41:48.62 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:56 ECID:3 Ec:(0x2378E098) Value:0x5cbd0e00 Cost:(0/1A68)
2006-10-23 15:41:48.62 spid4
2006-10-23 15:41:48.62 spid4 -- next branch --
2006-10-23 15:41:48.62 spid4
2006-10-23 15:41:48.62 spid4
2006-10-23 15:41:48.62 spid4 Node:2
2006-10-23 15:41:48.62 spid4 Port: 0x42bf0100 Xid Slot: 0, EC:
0x72dd7550, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeOpenWorkers
2006-10-23 15:41:48.62 spid4 Coordinator: EC = 0x72dd7550, SPID: 56,
ECID: 0, Not Blocking
2006-10-23 15:41:48.62 spid4 Consumer List::
2006-10-23 15:41:48.62 spid4 Consumer: Xid Slot: 0, EC = 0x72dd7550,
SPID: 56, ECID: 0, Blocking
2006-10-23 15:41:48.62 spid4 Producer List::
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 1, EC = 0x23790098,
SPID: 56, ECID: 2, Blocking
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 2, EC = 0x23786098,
SPID: 56, ECID: 4, Blocking
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 3, EC = 0x2379e098,
SPID: 56, ECID: 1, Blocking
2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 4, EC = 0x2378e098,
SPID: 56, ECID: 3, Blocking
2006-10-23 15:41:48.62 spid4 Victim Resource Owner:
2006-10-23 15:41:48.62 spid4 ResType:LockOwner Stype:'OR' Mode:
Range-S-U SPID:63 ECID:0 Ec:(0x5D98D550) Value:0x5cc8d000 Cost:(0/0)
I don't know the meanings or the goal of some terms: Life? Stype? EC? Xid
Slot? e_etypeOpenWorkers?
I want to help my Navision programmers and so I want to indicate some
suggests to solve this issue.
Many thanksWhat is Navision 3.70a?
Its more info about SQL Server 2005, but there are some links to SQL Server
2000 as well
http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:1E5B01B8-62BE-4BB8-9855-8782B5BC7875@.microsoft.com...
>I need to exam the SQL Server log to find the reasons of some deadlocks
> occured for a Navision application.
> I use SQL Server 2000 and Navision 3.70a. I have experience about SQL
> Server
> but not Navision.
> I have already some documentation about SQL Server log. I have used the
> parameters /T1204 e /T3605 to enable a trace in the log.
> Now, I want to find the rigth point inside environment Navision where the
> code must be modified.
> Below, I put the deadlock information registered:
> Node:1
> 2006-10-23 15:41:48.62 spid4 KEY: 7:1829581556:1 (1500fdbebb1d)
> CleanCnt:1 Mode: Range-S-U Flags: 0x0
> 2006-10-23 15:41:48.62 spid4 Grant List 1::
> 2006-10-23 15:41:48.62 spid4 Owner:0x5d140580 Mode: Range-S-U
> Flg:0x0
> Ref:1 Life:02000000 SPID:56 ECID:0
> 2006-10-23 15:41:48.62 spid4 SPID: 56 ECID: 0 Statement Type:
> SELECT
> Line #: 1
> 2006-10-23 15:41:48.62 spid4 Input Buf: RPC Event: sp_prepexec;1
> 2006-10-23 15:41:48.62 spid4 Requested By:
> 2006-10-23 15:41:48.62 spid4 ResType:LockOwner Stype:'OR' Mode:
> Range-S-U SPID:63 ECID:0 Ec:(0x5D98D550) Value:0x5cc8d000 Cost:(0/0)
> 2006-10-23 15:41:48.62 spid4
> 2006-10-23 15:41:48.62 spid4 Node:2
> 2006-10-23 15:41:48.62 spid4 Port: 0x42bf0100 Xid Slot: 0, EC:
> 0x72dd7550, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeOpenWorkers
> 2006-10-23 15:41:48.62 spid4 Coordinator: EC = 0x72dd7550, SPID: 56,
> ECID: 0, Not Blocking
> 2006-10-23 15:41:48.62 spid4 Consumer List::
> 2006-10-23 15:41:48.62 spid4 Consumer: Xid Slot: 0, EC = 0x72dd7550,
> SPID: 56, ECID: 0, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer List::
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 1, EC = 0x23790098,
> SPID: 56, ECID: 2, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 2, EC = 0x23786098,
> SPID: 56, ECID: 4, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 3, EC = 0x2379e098,
> SPID: 56, ECID: 1, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 4, EC = 0x2378e098,
> SPID: 56, ECID: 3, Blocking
> 2006-10-23 15:41:48.62 spid4
> 2006-10-23 15:41:48.62 spid4 Node:3
> 2006-10-23 15:41:48.62 spid4 PAG: 7:3:526003 CleanCnt:4
> Mode: IU Flags: 0x2
> 2006-10-23 15:41:48.62 spid4 Wait List:
> 2006-10-23 15:41:48.62 spid4 Owner:0x5cbd0de0 Mode: U
> Flg:0x0
> Ref:1 Life:02000000 SPID:56 ECID:1
> 2006-10-23 15:41:48.62 spid4 Requested By:
> 2006-10-23 15:41:48.62 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:56 ECID:2 Ec:(0x23790098) Value:0x5cbd0e20 Cost:(0/1A68)
> 2006-10-23 15:41:48.62 spid4
> 2006-10-23 15:41:48.62 spid4 Node:4
> 2006-10-23 15:41:48.62 spid4 PAG: 7:3:526003 CleanCnt:4
> Mode: IU Flags: 0x2
> 2006-10-23 15:41:48.62 spid4 Grant List 0::
> 2006-10-23 15:41:48.62 spid4 Owner:0x5d007180 Mode: IU
> Flg:0x0
> Ref:1 Life:02000000 SPID:63 ECID:0
> 2006-10-23 15:41:48.62 spid4 SPID: 63 ECID: 0 Statement Type:
> UPDATE
> Line #: 1
> 2006-10-23 15:41:48.62 spid4 Input Buf: RPC Event: sp_execute;1
> 2006-10-23 15:41:48.62 spid4 Grant List 1::
> 2006-10-23 15:41:48.62 spid4 Requested By:
> 2006-10-23 15:41:48.62 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:56 ECID:1 Ec:(0x2379E098) Value:0x5cbd0de0 Cost:(0/1A68)
> 2006-10-23 15:41:48.62 spid4
> 2006-10-23 15:41:48.62 spid4 -- next branch --
> 2006-10-23 15:41:48.62 spid4
> 2006-10-23 15:41:48.62 spid4
> 2006-10-23 15:41:48.62 spid4 Node:2
> 2006-10-23 15:41:48.62 spid4 Port: 0x42bf0100 Xid Slot: 0, EC:
> 0x72dd7550, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeOpenWorkers
> 2006-10-23 15:41:48.62 spid4 Coordinator: EC = 0x72dd7550, SPID: 56,
> ECID: 0, Not Blocking
> 2006-10-23 15:41:48.62 spid4 Consumer List::
> 2006-10-23 15:41:48.62 spid4 Consumer: Xid Slot: 0, EC = 0x72dd7550,
> SPID: 56, ECID: 0, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer List::
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 1, EC = 0x23790098,
> SPID: 56, ECID: 2, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 2, EC = 0x23786098,
> SPID: 56, ECID: 4, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 3, EC = 0x2379e098,
> SPID: 56, ECID: 1, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 4, EC = 0x2378e098,
> SPID: 56, ECID: 3, Blocking
> 2006-10-23 15:41:48.62 spid4
> 2006-10-23 15:41:48.62 spid4 Node:7
> 2006-10-23 15:41:48.62 spid4 PAG: 7:3:526003 CleanCnt:4
> Mode: IU Flags: 0x2
> 2006-10-23 15:41:48.62 spid4 Wait List:
> 2006-10-23 15:41:48.62 spid4 Owner:0x5cbd0de0 Mode: U
> Flg:0x0
> Ref:1 Life:02000000 SPID:56 ECID:1
> 2006-10-23 15:41:48.62 spid4 Requested By:
> 2006-10-23 15:41:48.62 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:56 ECID:4 Ec:(0x23786098) Value:0x5cbd0e40 Cost:(0/1A68)
> 2006-10-23 15:41:48.62 spid4
> 2006-10-23 15:41:48.62 spid4 -- next branch --
> 2006-10-23 15:41:48.62 spid4
> 2006-10-23 15:41:48.62 spid4
> 2006-10-23 15:41:48.62 spid4 Node:2
> 2006-10-23 15:41:48.62 spid4 Port: 0x42bf0100 Xid Slot: 0, EC:
> 0x72dd7550, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeOpenWorkers
> 2006-10-23 15:41:48.62 spid4 Coordinator: EC = 0x72dd7550, SPID: 56,
> ECID: 0, Not Blocking
> 2006-10-23 15:41:48.62 spid4 Consumer List::
> 2006-10-23 15:41:48.62 spid4 Consumer: Xid Slot: 0, EC = 0x72dd7550,
> SPID: 56, ECID: 0, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer List::
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 1, EC = 0x23790098,
> SPID: 56, ECID: 2, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 2, EC = 0x23786098,
> SPID: 56, ECID: 4, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 3, EC = 0x2379e098,
> SPID: 56, ECID: 1, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 4, EC = 0x2378e098,
> SPID: 56, ECID: 3, Blocking
> 2006-10-23 15:41:48.62 spid4
> 2006-10-23 15:41:48.62 spid4 -- next branch --
> 2006-10-23 15:41:48.62 spid4
> 2006-10-23 15:41:48.62 spid4
> 2006-10-23 15:41:48.62 spid4 Node:2
> 2006-10-23 15:41:48.62 spid4 Port: 0x42bf0100 Xid Slot: 0, EC:
> 0x72dd7550, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeOpenWorkers
> 2006-10-23 15:41:48.62 spid4 Coordinator: EC = 0x72dd7550, SPID: 56,
> ECID: 0, Not Blocking
> 2006-10-23 15:41:48.62 spid4 Consumer List::
> 2006-10-23 15:41:48.62 spid4 Consumer: Xid Slot: 0, EC = 0x72dd7550,
> SPID: 56, ECID: 0, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer List::
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 1, EC = 0x23790098,
> SPID: 56, ECID: 2, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 2, EC = 0x23786098,
> SPID: 56, ECID: 4, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 3, EC = 0x2379e098,
> SPID: 56, ECID: 1, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 4, EC = 0x2378e098,
> SPID: 56, ECID: 3, Blocking
> 2006-10-23 15:41:48.62 spid4
> 2006-10-23 15:41:48.62 spid4 Node:12
> 2006-10-23 15:41:48.62 spid4 PAG: 7:3:526003 CleanCnt:4
> Mode: IU Flags: 0x2
> 2006-10-23 15:41:48.62 spid4 Wait List:
> 2006-10-23 15:41:48.62 spid4 Owner:0x5cbd0de0 Mode: U
> Flg:0x0
> Ref:1 Life:02000000 SPID:56 ECID:1
> 2006-10-23 15:41:48.62 spid4 Requested By:
> 2006-10-23 15:41:48.62 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:56 ECID:3 Ec:(0x2378E098) Value:0x5cbd0e00 Cost:(0/1A68)
> 2006-10-23 15:41:48.62 spid4
> 2006-10-23 15:41:48.62 spid4 -- next branch --
> 2006-10-23 15:41:48.62 spid4
> 2006-10-23 15:41:48.62 spid4
> 2006-10-23 15:41:48.62 spid4 Node:2
> 2006-10-23 15:41:48.62 spid4 Port: 0x42bf0100 Xid Slot: 0, EC:
> 0x72dd7550, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeOpenWorkers
> 2006-10-23 15:41:48.62 spid4 Coordinator: EC = 0x72dd7550, SPID: 56,
> ECID: 0, Not Blocking
> 2006-10-23 15:41:48.62 spid4 Consumer List::
> 2006-10-23 15:41:48.62 spid4 Consumer: Xid Slot: 0, EC = 0x72dd7550,
> SPID: 56, ECID: 0, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer List::
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 1, EC = 0x23790098,
> SPID: 56, ECID: 2, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 2, EC = 0x23786098,
> SPID: 56, ECID: 4, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 3, EC = 0x2379e098,
> SPID: 56, ECID: 1, Blocking
> 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 4, EC = 0x2378e098,
> SPID: 56, ECID: 3, Blocking
> 2006-10-23 15:41:48.62 spid4 Victim Resource Owner:
> 2006-10-23 15:41:48.62 spid4 ResType:LockOwner Stype:'OR' Mode:
> Range-S-U SPID:63 ECID:0 Ec:(0x5D98D550) Value:0x5cc8d000 Cost:(0/0)
> I don't know the meanings or the goal of some terms: Life? Stype? EC? Xid
> Slot? e_etypeOpenWorkers?
> I want to help my Navision programmers and so I want to indicate some
> suggests to solve this issue.
> Many thanks
>
>|||Navision is the Microsoft ERP. Now its name is Dynamics NAV.
"Uri Dimant" wrote:
> What is Navision 3.70a?
> Its more info about SQL Server 2005, but there are some links to SQL Server
> 2000 as well
> http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
>
>
>
> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
> news:1E5B01B8-62BE-4BB8-9855-8782B5BC7875@.microsoft.com...
> >I need to exam the SQL Server log to find the reasons of some deadlocks
> > occured for a Navision application.
> > I use SQL Server 2000 and Navision 3.70a. I have experience about SQL
> > Server
> > but not Navision.
> > I have already some documentation about SQL Server log. I have used the
> > parameters /T1204 e /T3605 to enable a trace in the log.
> > Now, I want to find the rigth point inside environment Navision where the
> > code must be modified.
> >
> > Below, I put the deadlock information registered:
> > Node:1
> > 2006-10-23 15:41:48.62 spid4 KEY: 7:1829581556:1 (1500fdbebb1d)
> > CleanCnt:1 Mode: Range-S-U Flags: 0x0
> > 2006-10-23 15:41:48.62 spid4 Grant List 1::
> > 2006-10-23 15:41:48.62 spid4 Owner:0x5d140580 Mode: Range-S-U
> > Flg:0x0
> > Ref:1 Life:02000000 SPID:56 ECID:0
> > 2006-10-23 15:41:48.62 spid4 SPID: 56 ECID: 0 Statement Type:
> > SELECT
> > Line #: 1
> > 2006-10-23 15:41:48.62 spid4 Input Buf: RPC Event: sp_prepexec;1
> > 2006-10-23 15:41:48.62 spid4 Requested By:
> > 2006-10-23 15:41:48.62 spid4 ResType:LockOwner Stype:'OR' Mode:
> > Range-S-U SPID:63 ECID:0 Ec:(0x5D98D550) Value:0x5cc8d000 Cost:(0/0)
> > 2006-10-23 15:41:48.62 spid4
> > 2006-10-23 15:41:48.62 spid4 Node:2
> > 2006-10-23 15:41:48.62 spid4 Port: 0x42bf0100 Xid Slot: 0, EC:
> > 0x72dd7550, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeOpenWorkers
> > 2006-10-23 15:41:48.62 spid4 Coordinator: EC = 0x72dd7550, SPID: 56,
> > ECID: 0, Not Blocking
> > 2006-10-23 15:41:48.62 spid4 Consumer List::
> > 2006-10-23 15:41:48.62 spid4 Consumer: Xid Slot: 0, EC = 0x72dd7550,
> > SPID: 56, ECID: 0, Blocking
> > 2006-10-23 15:41:48.62 spid4 Producer List::
> > 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 1, EC = 0x23790098,
> > SPID: 56, ECID: 2, Blocking
> > 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 2, EC = 0x23786098,
> > SPID: 56, ECID: 4, Blocking
> > 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 3, EC = 0x2379e098,
> > SPID: 56, ECID: 1, Blocking
> > 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 4, EC = 0x2378e098,
> > SPID: 56, ECID: 3, Blocking
> > 2006-10-23 15:41:48.62 spid4
> > 2006-10-23 15:41:48.62 spid4 Node:3
> > 2006-10-23 15:41:48.62 spid4 PAG: 7:3:526003 CleanCnt:4
> > Mode: IU Flags: 0x2
> > 2006-10-23 15:41:48.62 spid4 Wait List:
> > 2006-10-23 15:41:48.62 spid4 Owner:0x5cbd0de0 Mode: U
> > Flg:0x0
> > Ref:1 Life:02000000 SPID:56 ECID:1
> > 2006-10-23 15:41:48.62 spid4 Requested By:
> > 2006-10-23 15:41:48.62 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:56 ECID:2 Ec:(0x23790098) Value:0x5cbd0e20 Cost:(0/1A68)
> > 2006-10-23 15:41:48.62 spid4
> > 2006-10-23 15:41:48.62 spid4 Node:4
> > 2006-10-23 15:41:48.62 spid4 PAG: 7:3:526003 CleanCnt:4
> > Mode: IU Flags: 0x2
> > 2006-10-23 15:41:48.62 spid4 Grant List 0::
> > 2006-10-23 15:41:48.62 spid4 Owner:0x5d007180 Mode: IU
> > Flg:0x0
> > Ref:1 Life:02000000 SPID:63 ECID:0
> > 2006-10-23 15:41:48.62 spid4 SPID: 63 ECID: 0 Statement Type:
> > UPDATE
> > Line #: 1
> > 2006-10-23 15:41:48.62 spid4 Input Buf: RPC Event: sp_execute;1
> > 2006-10-23 15:41:48.62 spid4 Grant List 1::
> > 2006-10-23 15:41:48.62 spid4 Requested By:
> > 2006-10-23 15:41:48.62 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:56 ECID:1 Ec:(0x2379E098) Value:0x5cbd0de0 Cost:(0/1A68)
> > 2006-10-23 15:41:48.62 spid4
> > 2006-10-23 15:41:48.62 spid4 -- next branch --
> > 2006-10-23 15:41:48.62 spid4
> > 2006-10-23 15:41:48.62 spid4
> > 2006-10-23 15:41:48.62 spid4 Node:2
> > 2006-10-23 15:41:48.62 spid4 Port: 0x42bf0100 Xid Slot: 0, EC:
> > 0x72dd7550, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeOpenWorkers
> > 2006-10-23 15:41:48.62 spid4 Coordinator: EC = 0x72dd7550, SPID: 56,
> > ECID: 0, Not Blocking
> > 2006-10-23 15:41:48.62 spid4 Consumer List::
> > 2006-10-23 15:41:48.62 spid4 Consumer: Xid Slot: 0, EC = 0x72dd7550,
> > SPID: 56, ECID: 0, Blocking
> > 2006-10-23 15:41:48.62 spid4 Producer List::
> > 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 1, EC = 0x23790098,
> > SPID: 56, ECID: 2, Blocking
> > 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 2, EC = 0x23786098,
> > SPID: 56, ECID: 4, Blocking
> > 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 3, EC = 0x2379e098,
> > SPID: 56, ECID: 1, Blocking
> > 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 4, EC = 0x2378e098,
> > SPID: 56, ECID: 3, Blocking
> > 2006-10-23 15:41:48.62 spid4
> > 2006-10-23 15:41:48.62 spid4 Node:7
> > 2006-10-23 15:41:48.62 spid4 PAG: 7:3:526003 CleanCnt:4
> > Mode: IU Flags: 0x2
> > 2006-10-23 15:41:48.62 spid4 Wait List:
> > 2006-10-23 15:41:48.62 spid4 Owner:0x5cbd0de0 Mode: U
> > Flg:0x0
> > Ref:1 Life:02000000 SPID:56 ECID:1
> > 2006-10-23 15:41:48.62 spid4 Requested By:
> > 2006-10-23 15:41:48.62 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:56 ECID:4 Ec:(0x23786098) Value:0x5cbd0e40 Cost:(0/1A68)
> > 2006-10-23 15:41:48.62 spid4
> > 2006-10-23 15:41:48.62 spid4 -- next branch --
> > 2006-10-23 15:41:48.62 spid4
> > 2006-10-23 15:41:48.62 spid4
> > 2006-10-23 15:41:48.62 spid4 Node:2
> > 2006-10-23 15:41:48.62 spid4 Port: 0x42bf0100 Xid Slot: 0, EC:
> > 0x72dd7550, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeOpenWorkers
> > 2006-10-23 15:41:48.62 spid4 Coordinator: EC = 0x72dd7550, SPID: 56,
> > ECID: 0, Not Blocking
> > 2006-10-23 15:41:48.62 spid4 Consumer List::
> > 2006-10-23 15:41:48.62 spid4 Consumer: Xid Slot: 0, EC = 0x72dd7550,
> > SPID: 56, ECID: 0, Blocking
> > 2006-10-23 15:41:48.62 spid4 Producer List::
> > 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 1, EC = 0x23790098,
> > SPID: 56, ECID: 2, Blocking
> > 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 2, EC = 0x23786098,
> > SPID: 56, ECID: 4, Blocking
> > 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 3, EC = 0x2379e098,
> > SPID: 56, ECID: 1, Blocking
> > 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 4, EC = 0x2378e098,
> > SPID: 56, ECID: 3, Blocking
> > 2006-10-23 15:41:48.62 spid4
> > 2006-10-23 15:41:48.62 spid4 -- next branch --
> > 2006-10-23 15:41:48.62 spid4
> > 2006-10-23 15:41:48.62 spid4
> > 2006-10-23 15:41:48.62 spid4 Node:2
> > 2006-10-23 15:41:48.62 spid4 Port: 0x42bf0100 Xid Slot: 0, EC:
> > 0x72dd7550, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeOpenWorkers
> > 2006-10-23 15:41:48.62 spid4 Coordinator: EC = 0x72dd7550, SPID: 56,
> > ECID: 0, Not Blocking
> > 2006-10-23 15:41:48.62 spid4 Consumer List::
> > 2006-10-23 15:41:48.62 spid4 Consumer: Xid Slot: 0, EC = 0x72dd7550,
> > SPID: 56, ECID: 0, Blocking
> > 2006-10-23 15:41:48.62 spid4 Producer List::
> > 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 1, EC = 0x23790098,
> > SPID: 56, ECID: 2, Blocking
> > 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 2, EC = 0x23786098,
> > SPID: 56, ECID: 4, Blocking
> > 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 3, EC = 0x2379e098,
> > SPID: 56, ECID: 1, Blocking
> > 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 4, EC = 0x2378e098,
> > SPID: 56, ECID: 3, Blocking
> > 2006-10-23 15:41:48.62 spid4
> > 2006-10-23 15:41:48.62 spid4 Node:12
> > 2006-10-23 15:41:48.62 spid4 PAG: 7:3:526003 CleanCnt:4
> > Mode: IU Flags: 0x2
> > 2006-10-23 15:41:48.62 spid4 Wait List:
> > 2006-10-23 15:41:48.62 spid4 Owner:0x5cbd0de0 Mode: U
> > Flg:0x0
> > Ref:1 Life:02000000 SPID:56 ECID:1
> > 2006-10-23 15:41:48.62 spid4 Requested By:
> > 2006-10-23 15:41:48.62 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:56 ECID:3 Ec:(0x2378E098) Value:0x5cbd0e00 Cost:(0/1A68)
> > 2006-10-23 15:41:48.62 spid4
> > 2006-10-23 15:41:48.62 spid4 -- next branch --
> > 2006-10-23 15:41:48.62 spid4
> > 2006-10-23 15:41:48.62 spid4
> > 2006-10-23 15:41:48.62 spid4 Node:2
> > 2006-10-23 15:41:48.62 spid4 Port: 0x42bf0100 Xid Slot: 0, EC:
> > 0x72dd7550, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeOpenWorkers
> > 2006-10-23 15:41:48.62 spid4 Coordinator: EC = 0x72dd7550, SPID: 56,
> > ECID: 0, Not Blocking
> > 2006-10-23 15:41:48.62 spid4 Consumer List::
> > 2006-10-23 15:41:48.62 spid4 Consumer: Xid Slot: 0, EC = 0x72dd7550,
> > SPID: 56, ECID: 0, Blocking
> > 2006-10-23 15:41:48.62 spid4 Producer List::
> > 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 1, EC = 0x23790098,
> > SPID: 56, ECID: 2, Blocking
> > 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 2, EC = 0x23786098,
> > SPID: 56, ECID: 4, Blocking
> > 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 3, EC = 0x2379e098,
> > SPID: 56, ECID: 1, Blocking
> > 2006-10-23 15:41:48.62 spid4 Producer: Xid Slot: 4, EC = 0x2378e098,
> > SPID: 56, ECID: 3, Blocking
> > 2006-10-23 15:41:48.62 spid4 Victim Resource Owner:
> > 2006-10-23 15:41:48.62 spid4 ResType:LockOwner Stype:'OR' Mode:
> > Range-S-U SPID:63 ECID:0 Ec:(0x5D98D550) Value:0x5cc8d000 Cost:(0/0)
> >
> > I don't know the meanings or the goal of some terms: Life? Stype? EC? Xid
> > Slot? e_etypeOpenWorkers?
> >
> > I want to help my Navision programmers and so I want to indicate some
> > suggests to solve this issue.
> >
> > Many thanks
> >
> >
> >
> >
>
>

Wednesday, March 7, 2012

Hourly Log Backups... restore proceedure

Hello all,
I am very to sql and still learning so please bear with what may be simple
questions.
I have a SqlDataBase that is at around 2.5GB's my log file was growing to
3GB each day. My maintenance job#1 happens overnight and does everything -
optimize, backup data and log files to disk etc. This backup goes to tape at
4:00AM with the complete server backup . I have another complete database
and log backup that goes direct to a different tape at 4:00PM.
This morning purged the log and shrunk the database to start from a clean
slate Database at 2.5GB's log started at close to nothing. I have setup an
hourly backup of the LOG FILE ONLY to a separate disk keeping 4 hours worth
of logs. I am now looking at the backups and they are 6264KB, 3952KB,
7036KB, 3313KB over the last four hours. We are not busy today but this does
not seem possible does this make sense?
My Question is this: given the proceedure above if the server blew up how do
I get a complete restore of the database... should my hourly backups include
the database as well as the transaction log?
MMaureen,
If you have not already done so, you should read:
Designing a Backup and Restore Strategy
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad
_bkprst_63eh.asp
In brief,
1. 3 to 7 meg of log file in a couple of hours is not very much.
2. Backup of the transactions is how you keep the log from growing. (Except
for simple recovery model)
3. If you have a backup of the database and the hour transaction logs you
would restore the same way:
Restore Database, Restore Log 1, Restore Log 2, ..., Restore Log N.
You should definitely do a trial disaster recovery to learn whether your
whole process is as realiable as you need.
Russell Fields
"Maureen" <nosend2me@.hotmail.com> wrote in message
news:ehBJYtNwDHA.1600@.TK2MSFTNGP10.phx.gbl...
> Hello all,
> I am very to sql and still learning so please bear with what may be simple
> questions.
> I have a SqlDataBase that is at around 2.5GB's my log file was growing to
> 3GB each day. My maintenance job#1 happens overnight and does everything -
> optimize, backup data and log files to disk etc. This backup goes to tape
at
> 4:00AM with the complete server backup . I have another complete database
> and log backup that goes direct to a different tape at 4:00PM.
> This morning purged the log and shrunk the database to start from a clean
> slate Database at 2.5GB's log started at close to nothing. I have setup an
> hourly backup of the LOG FILE ONLY to a separate disk keeping 4 hours
worth
> of logs. I am now looking at the backups and they are 6264KB, 3952KB,
> 7036KB, 3313KB over the last four hours. We are not busy today but this
does
> not seem possible does this make sense?
> My Question is this: given the proceedure above if the server blew up how
do
> I get a complete restore of the database... should my hourly backups
include
> the database as well as the transaction log?
> M
>|||Russell,
Thank for your post. If I understand you correctly To restore I need the
last DB Backup PLUS all LOG backups. In my case if the server failed at 1:49
PM I would need:
4:00 AM Complete database backup
PLUS [8:00AM Logbackup]
PLUS [9:00AM Logbackup]
PLUS [10:00AM Logbackup]
PLUS [11:00AM Logbackup]
PLUS [12:00PM Logbackup]
PLUS [1:00PM Logbackup]
And I would be good as of 1:00PM and I loose 49 minutes of the day. If I
have only the last four hours of logs... I am in trouble, is this correct?
My options are to either keep all of the hourly logs or do additional
database backups. I was actually surprised that the log backups are soooo
small given that the may daily log growth is 3GB I now seem to understand
that log backups must be considered as "A SET" since the last database
backup.
Am I close to understanding this correctly?
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:eNqVj3NwDHA.1060@.TK2MSFTNGP12.phx.gbl...
> Maureen,
> If you have not already done so, you should read:
> Designing a Backup and Restore Strategy
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad
> _bkprst_63eh.asp
> In brief,
> 1. 3 to 7 meg of log file in a couple of hours is not very much.
> 2. Backup of the transactions is how you keep the log from growing.
(Except
> for simple recovery model)
> 3. If you have a backup of the database and the hour transaction logs you
> would restore the same way:
> Restore Database, Restore Log 1, Restore Log 2, ..., Restore Log N.
> You should definitely do a trial disaster recovery to learn whether your
> whole process is as realiable as you need.
> Russell Fields
> "Maureen" <nosend2me@.hotmail.com> wrote in message
> news:ehBJYtNwDHA.1600@.TK2MSFTNGP10.phx.gbl...
> > Hello all,
> >
> > I am very to sql and still learning so please bear with what may be
simple
> > questions.
> >
> > I have a SqlDataBase that is at around 2.5GB's my log file was growing
to
> > 3GB each day. My maintenance job#1 happens overnight and does
everything -
> > optimize, backup data and log files to disk etc. This backup goes to
tape
> at
> > 4:00AM with the complete server backup . I have another complete
database
> > and log backup that goes direct to a different tape at 4:00PM.
> >
> > This morning purged the log and shrunk the database to start from a
clean
> > slate Database at 2.5GB's log started at close to nothing. I have setup
an
> > hourly backup of the LOG FILE ONLY to a separate disk keeping 4 hours
> worth
> > of logs. I am now looking at the backups and they are 6264KB, 3952KB,
> > 7036KB, 3313KB over the last four hours. We are not busy today but this
> does
> > not seem possible does this make sense?
> >
> > My Question is this: given the proceedure above if the server blew up
how
> do
> > I get a complete restore of the database... should my hourly backups
> include
> > the database as well as the transaction log?
> >
> > M
> >
> >
>|||Maureen,
Some comments in-line below.
Russell
> Thank for your post. If I understand you correctly To restore I need the
> last DB Backup PLUS all LOG backups. In my case if the server failed at
1:49
> PM I would need:
> 4:00 AM Complete database backup
> PLUS [8:00AM Logbackup]
> PLUS [9:00AM Logbackup]
> PLUS [10:00AM Logbackup]
> PLUS [11:00AM Logbackup]
> PLUS [12:00PM Logbackup]
> PLUS [1:00PM Logbackup]
> And I would be good as of 1:00PM and I loose 49 minutes of the day.
Correct. You would be able to restore each of the logs serially and get
back to 1:00 PM. (So don't throw away your logs.) With regard to the last
49 minutes, BACKUP... NO_TRUNCATE is for backing up the log of a damaged
database, so it is sometimes possible to recover those last minutes as well.
There are more details in the Books Online.
> If I have only the last four hours of logs... I am in trouble, is this
correct?
> My options are to either keep all of the hourly logs or do additional
> database backups. I was actually surprised that the log backups are soooo
> small given that the may daily log growth is 3GB I now seem to understand
> that log backups must be considered as "A SET" since the last database
> backup.
Yes, every log since the last database backup is a "set" that is needed to
restore the database to current. (Read the whole section on Backing Up and
Restoring Databases in the BOL.)
Also, I suspect that the major growth in your log may be caused by your
nighttime maintenance tasks.
> Am I close to understanding this correctly?
Yes. Keep learning and it will all make good sense.
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:eNqVj3NwDHA.1060@.TK2MSFTNGP12.phx.gbl...
> > Maureen,
> >
> > If you have not already done so, you should read:
> > Designing a Backup and Restore Strategy
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad
> > _bkprst_63eh.asp
> >
> > In brief,
> > 1. 3 to 7 meg of log file in a couple of hours is not very much.
> > 2. Backup of the transactions is how you keep the log from growing.
> (Except
> > for simple recovery model)
> > 3. If you have a backup of the database and the hour transaction logs
you
> > would restore the same way:
> > Restore Database, Restore Log 1, Restore Log 2, ..., Restore Log N.
> >
> > You should definitely do a trial disaster recovery to learn whether your
> > whole process is as realiable as you need.
> >
> > Russell Fields
> >
> > "Maureen" <nosend2me@.hotmail.com> wrote in message
> > news:ehBJYtNwDHA.1600@.TK2MSFTNGP10.phx.gbl...
> > > Hello all,
> > >
> > > I am very to sql and still learning so please bear with what may be
> simple
> > > questions.
> > >
> > > I have a SqlDataBase that is at around 2.5GB's my log file was growing
> to
> > > 3GB each day. My maintenance job#1 happens overnight and does
> everything -
> > > optimize, backup data and log files to disk etc. This backup goes to
> tape
> > at
> > > 4:00AM with the complete server backup . I have another complete
> database
> > > and log backup that goes direct to a different tape at 4:00PM.
> > >
> > > This morning purged the log and shrunk the database to start from a
> clean
> > > slate Database at 2.5GB's log started at close to nothing. I have
setup
> an
> > > hourly backup of the LOG FILE ONLY to a separate disk keeping 4 hours
> > worth
> > > of logs. I am now looking at the backups and they are 6264KB, 3952KB,
> > > 7036KB, 3313KB over the last four hours. We are not busy today but
this
> > does
> > > not seem possible does this make sense?
> > >
> > > My Question is this: given the proceedure above if the server blew up
> how
> > do
> > > I get a complete restore of the database... should my hourly backups
> > include
> > > the database as well as the transaction log?
> > >
> > > M
> > >
> > >
> >
> >
>

Friday, March 23, 2012

How can I back up a log-shipped database?

Monday, March 19, 2012

how can attack database from sqlserver management studio express?

Monday, March 12, 2012

How big is my log?

How big is my log?

How big is my log?

Friday, March 9, 2012

How analysing SQL Server log to correct Navision code

How analysing SQL Server log to correct Navision code

How analysing SQL Server log to correct Navision code

Wednesday, March 7, 2012

Hourly Log Backups... restore proceedure

hot backup

Blog Archive

About Me