sql reports: transaction

Showing posts with label transaction. Show all posts

Tuesday, March 27, 2012

Can we do Transaction log backup and log shipping on same server.

Hi,
I know that Log Shipping and Transaction log backup cannot be
done on same primary server side by side because i tried it, but i want
possible reason for that.
Next i want to know is that can we Detach and re Atach Log
Shipping Database on secondary server in a Standby mode (as you know
this database is itself in standby mode before detaching).
thanks and regards,
Sajid.What's version are you using?
<csajid@.gmail.com> wrote in message
news:1147952596.990826.130310@.i39g2000cwa.googlegroups.com...
> Hi,
> I know that Log Shipping and Transaction log backup cannot be
> done on same primary server side by side because i tried it, but i want
> possible reason for that.
> Next i want to know is that can we Detach and re Atach Log
> Shipping Database on secondary server in a Standby mode (as you know
> this database is itself in standby mode before detaching).
> thanks and regards,
> Sajid.
>|||> I know that Log Shipping and Transaction log backup cannot be
> done on same primary server side by side because i tried it, but i want
> possible reason for that.
You can do it, but you don't want to. Log shipping is based on transaction log backups. And as you
know, log backups has to be performed in sequence. So if the first log backup is shipped to the
other server, and the second is done by you and not shipped to the server, then the third log backup
done my log shipping won't restore as the second haven't been restored. It is possible that the
built-in log shipping tries somehow to restrict you from such a scenario, which would be a smart
thing to do.
> Next i want to know is that can we Detach and re Atach Log
> Shipping Database on secondary server in a Standby mode (as you know
> this database is itself in standby mode before detaching).
No.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<csajid@.gmail.com> wrote in message news:1147952596.990826.130310@.i39g2000cwa.googlegroups.com...
> Hi,
> I know that Log Shipping and Transaction log backup cannot be
> done on same primary server side by side because i tried it, but i want
> possible reason for that.
> Next i want to know is that can we Detach and re Atach Log
> Shipping Database on secondary server in a Standby mode (as you know
> this database is itself in standby mode before detaching).
> thanks and regards,
> Sajid.
>|||Hi All,
Thanks for this reply.
It clears my all doubt.
thanks again.
Regards,
Sajid.
Tibor Karaszi wrote:
> > I know that Log Shipping and Transaction log backup cannot be
> > done on same primary server side by side because i tried it, but i want
> > possible reason for that.
> You can do it, but you don't want to. Log shipping is based on transaction log backups. And as you
> know, log backups has to be performed in sequence. So if the first log backup is shipped to the
> other server, and the second is done by you and not shipped to the server, then the third log backup
> done my log shipping won't restore as the second haven't been restored. It is possible that the
> built-in log shipping tries somehow to restrict you from such a scenario, which would be a smart
> thing to do.
>
> > Next i want to know is that can we Detach and re Atach Log
> > Shipping Database on secondary server in a Standby mode (as you know
> > this database is itself in standby mode before detaching).
> No.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <csajid@.gmail.com> wrote in message news:1147952596.990826.130310@.i39g2000cwa.googlegroups.com...
> > Hi,
> >
> > I know that Log Shipping and Transaction log backup cannot be
> > done on same primary server side by side because i tried it, but i want
> > possible reason for that.
> >
> > Next i want to know is that can we Detach and re Atach Log
> > Shipping Database on secondary server in a Standby mode (as you know
> > this database is itself in standby mode before detaching).
> >
> > thanks and regards,
> > Sajid.
> >|||Hi All,
I want to schedule a job in ms sql server 2000 which will
copy the backup file from one server whose drive is mapped to
destination server where the backup file should get copied.
After copying the file i want to restore it on a same
destination server.
I can restore it with the help of Restore Database with move
option command, but the problem is the backup file is in format like
'sample_db_200605122100.bak' where sample is database name and numbers
indicate the date and time when backup was taken.
Now, i want to make a batch file which will delete the
previous same backup file, copy the backup file and rename it to
standard filename say 'sample_db_backup.bak'.
This batch file i can shedule to run through ms sql server
jobs by using 'xp_cmdshell'.
Please revert me on this ASAP.
Thanks and Regards,
Sajid.
csajid@.gmail.com wrote:
> Hi All,
> Thanks for this reply.
> It clears my all doubt.
> thanks again.
> Regards,
> Sajid.
> Tibor Karaszi wrote:
> > > I know that Log Shipping and Transaction log backup cannot be
> > > done on same primary server side by side because i tried it, but i want
> > > possible reason for that.
> >
> > You can do it, but you don't want to. Log shipping is based on transaction log backups. And as you
> > know, log backups has to be performed in sequence. So if the first log backup is shipped to the
> > other server, and the second is done by you and not shipped to the server, then the third log backup
> > done my log shipping won't restore as the second haven't been restored. It is possible that the
> > built-in log shipping tries somehow to restrict you from such a scenario, which would be a smart
> > thing to do.
> >
> >
> > > Next i want to know is that can we Detach and re Atach Log
> > > Shipping Database on secondary server in a Standby mode (as you know
> > > this database is itself in standby mode before detaching).
> >
> > No.
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > <csajid@.gmail.com> wrote in message news:1147952596.990826.130310@.i39g2000cwa.googlegroups.com...
> > > Hi,
> > >
> > > I know that Log Shipping and Transaction log backup cannot be
> > > done on same primary server side by side because i tried it, but i want
> > > possible reason for that.
> > >
> > > Next i want to know is that can we Detach and re Atach Log
> > > Shipping Database on secondary server in a Standby mode (as you know
> > > this database is itself in standby mode before detaching).
> > >
> > > thanks and regards,
> > > Sajid.
> > >sql

can we control how transaction send from principal to mirror ?

Hi,

can we control how transaction send from principal to mirror ?

If application inserting 10000000 rows in one transaction to principal database

how infomation will be transfered once it is commited and where it will be stored before it is replayed on mirror database?

1. Is it going to be 1 big data packet ?

2.is it going to be split on many packets (of what size ?)

Thanks

Alex

Log records are sent from principal to mirror by log block boundary (64k max). In this case, since the transaction log for the transaction will be larger than 64K, multiple log blocks will be sent to the mirror. The log records are being sent continuously -- it doesn't wait for the commit.

The log records are stored on the log files on the mirror and are are applied on the mirror database continuously.

|||

Hi Sanjay , thank for you help

can you post link where I can read about "log block boundary" in db mirroring

you wrote

> The log records are stored on the log files on the mirror and are are applied on the mirror database continuously

Do you you mean they written to .ldf file of mirrored database ?

Alex

|||

Yes, the log is written to the .ldf file of the mirror database.

Thursday, March 22, 2012

Can u pls help with the trasaction isolation levels

SET TRANSACTION ISOLATION LEVEL issue:
which one i can use in a vb code which uses openrow set to
acesss data in sqlsever and as such it is blocking other
process so which trnsaction isolation level i can specify
so that it doesnt block others and its a read only procees
thanksHi Saradhi,
You can try READ UNCOMMITED. This isolation level allows readers of data to
read uncommited (durty) records from the database.
HTH
Karl Gram
http://www.gramonline.com
"saradhi" <anonymous@.discussions.microsoft.com> wrote in message
news:dc7c01c40b3c$a1e8ac20$a501280a@.phx.gbl...
> SET TRANSACTION ISOLATION LEVEL issue:
> which one i can use in a vb code which uses openrow set to
> acesss data in sqlsever and as such it is blocking other
> process so which trnsaction isolation level i can specify
> so that it doesnt block others and its a read only procees
> thanks|||sradhi
If you can provide a little bit more info about what are you trying to
accomlish so it helps us to solve the problem
Let me say you have a SELECT statement and later you decided to update a
selected rows. You have to make sure that others users could not change the
data while you select it. Specify UPDLOCK hint which dont block others users
from reading the selected rows but you can be assured that data has not
changed since last you read it.
"saradhi" <anonymous@.discussions.microsoft.com> wrote in message
news:dc7c01c40b3c$a1e8ac20$a501280a@.phx.gbl...
> SET TRANSACTION ISOLATION LEVEL issue:
> which one i can use in a vb code which uses openrow set to
> acesss data in sqlsever and as such it is blocking other
> process so which trnsaction isolation level i can specify
> so that it doesnt block others and its a read only procees
> thanks|||thanks i will go for read uncommited
OK the problem is we have a Dell PowerEdge 2650 Server
with win2k and sql server 2003 installed and the server
has all the default configuraion settings in sql server
i am facing problem with openrow set queries used by some
of the applications which is blocking other process i
check for why it is happenning i got the bug was with
the fiber mode option which i had enabled in sql server
settings later i had removed it as my ram is 2 gb and its
bogging u p my server now its on thread mode but still
i have problems in openrowset quiroes some times blocking
other process they go into ana infinite loop even we kill
the procees the rollback goes on and on it s bug with
fiber mode option as said in one of the tech docs in tech
net ,later i have gone for reinstalling th mdac but still
now has any one faced this problem and can anyone suggest
me how to resolve it pls
thanks
saradhi

>--Original Message--
>sradhi
>If you can provide a little bit more info about what are
you trying to
>accomlish so it helps us to solve the problem
>Let me say you have a SELECT statement and later you
decided to update a
>selected rows. You have to make sure that others users
could not change the
>data while you select it. Specify UPDLOCK hint which dont
block others users
>from reading the selected rows but you can be assured
that data has not
>changed since last you read it.
>
>"saradhi" <anonymous@.discussions.microsoft.com> wrote in
message
>news:dc7c01c40b3c$a1e8ac20$a501280a@.phx.gbl...
to
specify
procees
>
>.
>|||Saradi
<http://support.microsoft.com/direct...B;EN-US;Q224453>--
-- INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking
Problems (Q224453)
Also I would not recommend you to change the default setting. Are you aware
that your users can get dirty data which may not written to disk at all?
I'd go with reviewing your application , how does it work, how does it
access to the objects. Pehaps you have long-run queries which cause the
bloking.

"saradhi" <anonymous@.discussions.microsoft.com> wrote in message
news:b05e01c40b42$d63f8290$a601280a@.phx.gbl...
> thanks i will go for read uncommited
> OK the problem is we have a Dell PowerEdge 2650 Server
> with win2k and sql server 2003 installed and the server
> has all the default configuraion settings in sql server
> i am facing problem with openrow set queries used by some
> of the applications which is blocking other process i
> check for why it is happenning i got the bug was with
> the fiber mode option which i had enabled in sql server
> settings later i had removed it as my ram is 2 gb and its
> bogging u p my server now its on thread mode but still
> i have problems in openrowset quiroes some times blocking
> other process they go into ana infinite loop even we kill
> the procees the rollback goes on and on it s bug with
> fiber mode option as said in one of the tech docs in tech
> net ,later i have gone for reinstalling th mdac but still
> now has any one faced this problem and can anyone suggest
> me how to resolve it pls
> thanks
> saradhi
>
> you trying to
> decided to update a
> could not change the
> block others users
> that data has not
> message
> to
> specify
> procees

Tuesday, March 20, 2012

Can t-log size handle large number of deletions?

Hi All,
Im just wondering if there is a way i could find out if the size of my
transaction log will be able to record the number of deletions i will be
performing? Basically i have to delete 325 million rows from a table and my
t-log is, say 20GB. is there a way i can calculate if the t-log is big
enough or will it need to expand?
thx in advance.Well I certainly would not advocate deleting them all in one batch. If you
delete them in smaller batches (say 10K or 100K at a time) it will not only
be faster but you would have the option to backup or truncate the log as you
go along. How many rows in the table do you want to keep. It may be
easier to BCP out the ones to keep, truncate the table and bcp them back in.
--
Andrew J. Kelly
SQL Server MVP
"M Sandico" <msandico@.muchomail.com> wrote in message
news:%23ZDvAlIpDHA.3688@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> Im just wondering if there is a way i could find out if the size of my
> transaction log will be able to record the number of deletions i will be
> performing? Basically i have to delete 325 million rows from a table and
my
> t-log is, say 20GB. is there a way i can calculate if the t-log is big
> enough or will it need to expand?
> thx in advance.
>|||You are correct in that I dont delete them all in one batch. I delete in
batches of 1000 with a BEGIN TRAN and COMMIT TRAN as well. What I am
planning to do is just set the model to SIMPLE during the deletion and have
the COMMIT TRAN force the log to be flushed during the automatic checkpoint
(when log is 70% full).
Just thought there would be a way to guesstimate how big a t-log youd need
for certain operations..
thx.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e9rVaHJpDHA.360@.TK2MSFTNGP12.phx.gbl...
> Well I certainly would not advocate deleting them all in one batch. If
you
> delete them in smaller batches (say 10K or 100K at a time) it will not
only
> be faster but you would have the option to backup or truncate the log as
you
> go along. How many rows in the table do you want to keep. It may be
> easier to BCP out the ones to keep, truncate the table and bcp them back
in.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "M Sandico" <msandico@.muchomail.com> wrote in message
> news:%23ZDvAlIpDHA.3688@.TK2MSFTNGP11.phx.gbl...
> > Hi All,
> >
> > Im just wondering if there is a way i could find out if the size of my
> > transaction log will be able to record the number of deletions i will be
> > performing? Basically i have to delete 325 million rows from a table and
> my
> > t-log is, say 20GB. is there a way i can calculate if the t-log is big
> > enough or will it need to expand?
> >
> > thx in advance.
> >
> >
>|||I don't know of a formula off hand. You would have to account for at least
the amount of data that you are deleting and then some percentage for
overhead etc. What that percentage is I don't really know.
--
Andrew J. Kelly
SQL Server MVP
"M Sandico" <msandico@.muchomail.com> wrote in message
news:ugg192JpDHA.2188@.TK2MSFTNGP11.phx.gbl...
> You are correct in that I dont delete them all in one batch. I delete in
> batches of 1000 with a BEGIN TRAN and COMMIT TRAN as well. What I am
> planning to do is just set the model to SIMPLE during the deletion and
have
> the COMMIT TRAN force the log to be flushed during the automatic
checkpoint
> (when log is 70% full).
> Just thought there would be a way to guesstimate how big a t-log youd need
> for certain operations..
> thx.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e9rVaHJpDHA.360@.TK2MSFTNGP12.phx.gbl...
> > Well I certainly would not advocate deleting them all in one batch. If
> you
> > delete them in smaller batches (say 10K or 100K at a time) it will not
> only
> > be faster but you would have the option to backup or truncate the log as
> you
> > go along. How many rows in the table do you want to keep. It may be
> > easier to BCP out the ones to keep, truncate the table and bcp them back
> in.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "M Sandico" <msandico@.muchomail.com> wrote in message
> > news:%23ZDvAlIpDHA.3688@.TK2MSFTNGP11.phx.gbl...
> > > Hi All,
> > >
> > > Im just wondering if there is a way i could find out if the size of my
> > > transaction log will be able to record the number of deletions i will
be
> > > performing? Basically i have to delete 325 million rows from a table
and
> > my
> > > t-log is, say 20GB. is there a way i can calculate if the t-log is big
> > > enough or will it need to expand?
> > >
> > > thx in advance.
> > >
> > >
> >
> >
>

can this be done?

I have a text file that is generated from a host transaction. I have a batch
program that loads the text file into SQL and does a boatload of validation.
Is it possible to create DTS job or do this some how in SQL to make it
pretty less painful?you can create a dts package using import/export wizard in mssql server
enterprise manager, store it somewhere ( either on the server, or as a
file ), and then call this package from your app, and supply all dynamic
variables, including the filename, etc.
Ilyan Mishiyev
IGM Consulting Corporation
Enterprise Web Solutions
www.igmcc.com
"microsoft.news.com" <CSharpCoder> wrote in message
news:exxPynAUFHA.1432@.TK2MSFTNGP09.phx.gbl...
>I have a text file that is generated from a host transaction. I have a
>batch program that loads the text file into SQL and does a boatload of
>validation. Is it possible to create DTS job or do this some how in SQL to
>make it pretty less painful?
>
>|||would i still have to do all the business rules, validation etc, in the
batch program or could I use SQL to do all of that?
"Ilyan Mishiyev" <msnewsaccountREMOVE@.THISigmcc.com> wrote in message
news:exhFJ5AUFHA.2556@.TK2MSFTNGP12.phx.gbl...
> you can create a dts package using import/export wizard in mssql server
> enterprise manager, store it somewhere ( either on the server, or as a
> file ), and then call this package from your app, and supply all dynamic
> variables, including the filename, etc.
> --
> Ilyan Mishiyev
> IGM Consulting Corporation
> Enterprise Web Solutions
> www.igmcc.com
>
> "microsoft.news.com" <CSharpCoder> wrote in message
> news:exxPynAUFHA.1432@.TK2MSFTNGP09.phx.gbl...
>|||after you load all the data, you can call a stored proc by the same app
that proc might be used for validation
Ilyan Mishiyev
IGM Consulting Corporation
Enterprise Web Solutions
www.igmcc.com
"microsoft.news.com" <CSharpCoder> wrote in message
news:Of5CG$AUFHA.2056@.tk2msftngp13.phx.gbl...
> would i still have to do all the business rules, validation etc, in the
> batch program or could I use SQL to do all of that?
> "Ilyan Mishiyev" <msnewsaccountREMOVE@.THISigmcc.com> wrote in message
> news:exhFJ5AUFHA.2556@.TK2MSFTNGP12.phx.gbl...
>|||is there somewere online i could read about or look at this being done or
something like it being done?
"Ilyan Mishiyev" <msnewsaccountREMOVE@.THISigmcc.com> wrote in message
news:uxYGZUBUFHA.3652@.TK2MSFTNGP10.phx.gbl...
> after you load all the data, you can call a stored proc by the same app
> that proc might be used for validation
> --
> Ilyan Mishiyev
> IGM Consulting Corporation
> Enterprise Web Solutions
> www.igmcc.com
>
> "microsoft.news.com" <CSharpCoder> wrote in message
> news:Of5CG$AUFHA.2056@.tk2msftngp13.phx.gbl...
>|||not sure about that
actually it's really not that complicated
step 1 - create dts package
step 2 - call tht package from the app to load data
step 3 - call stored proc that validates loaded data
Ilyan Mishiyev
IGM Consulting Corporation
Enterprise Web Solutions
www.igmcc.com
"microsoft.news.com" <CSharpCoder> wrote in message
news:ebXU5cBUFHA.736@.TK2MSFTNGP10.phx.gbl...
> is there somewere online i could read about or look at this being done or
> something like it being done?
> "Ilyan Mishiyev" <msnewsaccountREMOVE@.THISigmcc.com> wrote in message
> news:uxYGZUBUFHA.3652@.TK2MSFTNGP10.phx.gbl...
>|||that appears easy, but how will i define what data goes into what column in
the table since my data in the file looks like this:
BMW325i20051212 65222 John Smith
its:
Car: BMW
Model: 3251
Year: 20051212
Price: 65222
Buyer: John Smith
and so on.
"Ilyan Mishiyev" <msnewsaccountREMOVE@.THISigmcc.com> wrote in message
news:O0YBSwBUFHA.2556@.TK2MSFTNGP12.phx.gbl...
> not sure about that
> actually it's really not that complicated
> step 1 - create dts package
> step 2 - call tht package from the app to load data
> step 3 - call stored proc that validates loaded data
> --
> Ilyan Mishiyev
> IGM Consulting Corporation
> Enterprise Web Solutions
> www.igmcc.com
>
> "microsoft.news.com" <CSharpCoder> wrote in message
> news:ebXU5cBUFHA.736@.TK2MSFTNGP10.phx.gbl...
>|||Is it a fixed length file?
Do you know the layout of this file?
For example,
from 1st character to 5th, field 1
from 6th character to 12th, field 2, etc.
If this is a fixed length file and you do know what the layout is, then you
can specify that when creating a dts package.
If it's a delimited file ( i don't think it's a delimited file by looking at
data ), then specify what the delimiter ( comma, semicolon, pipe, etc. ) is
so dts knows how to split that data into fields.
Then, when that package is called by your application, all the data will
automatically be loaded into the specified fields.
Try creating a package first and load that data manually.
Ilyan Mishiyev
IGM Consulting Corporation
Enterprise Web Solutions
www.igmcc.com
"microsoft.news.com" <CSharpCoder> wrote in message
news:uIr9U8BUFHA.2820@.tk2msftngp13.phx.gbl...
> that appears easy, but how will i define what data goes into what column
> in the table since my data in the file looks like this:
> BMW325i20051212 65222 John Smith
>
> its:
> Car: BMW
> Model: 3251
> Year: 20051212
> Price: 65222
> Buyer: John Smith
> and so on.
>
> "Ilyan Mishiyev" <msnewsaccountREMOVE@.THISigmcc.com> wrote in message
> news:O0YBSwBUFHA.2556@.TK2MSFTNGP12.phx.gbl...
>

Monday, March 19, 2012

Can the use of Shrinkfile break the Transaction Log chain?

My Log Shipping process has been up and running fine for about 6 months now.
Then as part of the preparation for upgrading the front end application for
the log shipped database, my partner DBA thought he would help the backup
speed by running a SHRINKFILE over the database files.
It was on or about this time that log shipping stopped working and reported
errors just like the ones you get when the transaction log has been
truncated. Basically it thinks the T Log chain has been broken.
Can SHRINKFILE break the chain? I thought it only compacted the unused file
space?
In my tests log shipping was not sensitive to shrinkfile statements. Perhaps
something else has gone wrong.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"GRP" <GRP@.discussions.microsoft.com> wrote in message
news:BD8BC1B6-FD32-43C2-B34E-7CA0397CAC35@.microsoft.com...
> My Log Shipping process has been up and running fine for about 6 months
now.
> Then as part of the preparation for upgrading the front end application
for
> the log shipped database, my partner DBA thought he would help the backup
> speed by running a SHRINKFILE over the database files.
> It was on or about this time that log shipping stopped working and
reported
> errors just like the ones you get when the transaction log has been
> truncated. Basically it thinks the T Log chain has been broken.
> Can SHRINKFILE break the chain? I thought it only compacted the unused
file
> space?
|||Hilary, thanks for taking the time to test this out. The only other
explanation I can think of is that the other DBA truncated the log prior to
the shrinkfile.
"Hilary Cotter" wrote:

> In my tests log shipping was not sensitive to shrinkfile statements. Perhaps
> something else has gone wrong.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "GRP" <GRP@.discussions.microsoft.com> wrote in message
> news:BD8BC1B6-FD32-43C2-B34E-7CA0397CAC35@.microsoft.com...
> now.
> for
> reported
> file
>
>

Can the transaction log be turned off?

I have a couple of databases that see a lot of updates (WEB user session state stuff) and I don't care at all if I lose any or all of the data. They don't get backed up and fresh copies can be created in seconds. Is there a way I can turn off the transaction logging to cut down on overhead?

ThanksNo

Why what the problem?

Waht's your recovery model set to?|||Recovery model is 'Simple'. Just thought I'd be able to cut down overhead. I've been getting timeouts and am grasping at straws.|||Originally posted by grahamt
Recovery model is 'Simple'. Just thought I'd be able to cut down overhead. I've been getting timeouts and am grasping at straws.

When you say timeouts, what do you mean by that?

From QA, from the application layer?

Are you blocking yourself (sp_lock)

How many connections are open (sp_who2)

How big is the database?

How often do you take backups?|||Setting recovery model is nothing to do with blocks or timeouts on the database. Ensure there are no issues with network, client connections and query conditions involved which may help causing timeouts.

Whatever the recovery model ensure to maintain & schedule full backup of the database.|||The SQL Server timeouts are reported by the ASP.
A bit of config info.

Hardware

SQL Server 2000
Dual Xeon 2.4GHz CPU
Ultra 320 SCSI controller
Two Ultra 320 18GB, 15000 RPM Hard Disks (C: and D:)
Two GB RAM (1.5GB assigned to SQL Server)
Windows 2000 Server

WEB Server connected to SQL server by Gigabit Ethernet.

Two databases involved. One has a single table of perhaps 2000 records (Session state info for WEB users) and is heavily used (Insert, Delete, and Select). It resides on drive C:.

Second DB residing on drive D: has one Master table and 26 others (A-Z). Master table has about 16000 6K records, Primary key on a varchar(50) and a char(2) column. Other tables run from 1000 to 50000 records 70 bytes/record (PK on a varchar(50) field). No updates are ever done (well, every few months maybe) and the Selects are done using stored procedures, one for the Master table (2 parameters) and 26 for the other tables (1 parameter).

Every few minutes (time varies as does the DB with the DB on drive D: getting the most) the WEB app logs an SQL Server timeout. With this horsepower driving these small DBs (and nothing else running) I wouldn't expect any timeouts at all.

Since I don't care about the data in one table and the data in the other never changes, I don't bother with backups.

Any thoughts?|||What are the timeout settings on SQL & ASP script?
IT may be worth if you enable DBCC DBREINDEX and other maint.plan checks on database which will addup performance.

And also consider network settings and take help of netadmin.|||http://vyaskn.tripod.com/sql_odbc_timeout_expired.htm - a useful guide to troubleshoot timeouts when using SQL & ASP.|||I've been monitoring network traffic with 3COMs Network Monitor and everything seems fine. The WEB server showed high FTP traffic so I've shut that service down but there's no perceptable load on SQL server. Performance monitor shows Disk Idle Time averaging 98% or better on both drives and the CPU load never exceeds 3-4%. I've run the SPs through QA and because they are so basic the execution cost is almost nil. The two SPs are

CREATE PROCEDURE [DBO].[prGetOrigins_A]
@.SomeVar Varchar(50)
AS

SET NOCOUNT ON

SELECT Code, Master FROM A Where SomeVar = @.Somevar
GO

and

CREATE PROCEDURE [DBO].[prGetMasterRec]
@.master varChar(20),
@.code varCHar(3)
AS
SET NOCOUNT ON

SELECT id, code,SomeData,
FROM Masters WHERE Master = @.master AND Code = @.Code
GO

and still I get random timeouts.

Personally I am not concerned. A timeout every 5 minutes or so with 250 users on the WEB server probably shouldn't happen but I can live with it. It's the boss who panics and worries that he may have lost a $5.00 sale. 8-)

Sunday, March 11, 2012

Can table1 in example below get updated by another process while the transaction is in pro

I am afraid that just after @.statusOfEmployee is retrieved from table1, but before table2 is updated, someone else (a second user) calls this same stored procedure and changes the @.statusOfEmployee value. This would create aninconsistentupdate of table2 by first user, since the update of table2 'might' not have gone ahead if the latest value of @.statusOF Employee was used. CAN SOMEONE PLEASE HELP ME WITH THIS SITUATION AND HOW I CAN BE SURE THAT ABOVE DOES NOT HAPPEN SINCE MULTIPLE USERS WILL BE HITTING THIS STORED PROCEDURE?

declare @.status int
begin tran
set @.status = (select statusOfEmployee from table1)
if @.@.ERROR = 0
begin
update table2
set destination = @.destination /* @.destination is an input parameter passed to the sp*/
where @.currentStatus = @.status
if @.ERROR = 0
commit tran
else
rollback tran
end
else
rollback tran

return

update table2
set destination = @.destination /* @.destination is an input parameter passed to the sp*/
where @.currentStatus = (select @.statusOfEmployee from table1)

This will only work if you have only 1 row in table1, but I'm guessing this isn't your real SP. If there can be more than 1, then you have other issues. And to answer your question, yes, it could have been updated between those statements.

|||

Sorryy. I meant a field there. I will edit it.

One question for you: If I used the original sp I mentioned in my post, then is there a danger of incosistent update as I have explained OR because the select is in a transaction, SQL Server will prevent any changes to tables being used in the transaction?

I was going to mark the ADO.Net code that calls this stored procedure as 'critical' in my C# code using lock(this) { }. That way only one user can execute this stored procedure at a time from the application, which eliminates any chances of inconsistent updates. ANYONE HAS ANY COMMENTS ON USING THIS APPROACH TO PREVENT INCOSISTENT UPDATES?

|||

I would think even with your approach, since the select and update are on different tables, there is nothing preventing another user from updating the table used in select statement. SQL Server willonlyprevent any user from updating 'table2' while this update statement is in progress.

|||

Yes, your original SP isn't multi-user safe. No, the one I gave you is. The difference is the type of locks that are requested and the duration for which they are held.

|||

So even though in your query a simple select is being executed on 'table1', SQL Server will place an exclusive lock on 'table1' row.

I thought that under default SQL Server 2000 locking (read committed), select statements will not place an exclusive lock on the row involved in select query. And if this is true, then including the 'select' within the 'update' will still allow someone else to update 'table1' before the update to 'tabl2' happens. Right or wrong?

|||

Because the select is happening within the confines of the UPDATE statement, the subquery will place a read-lock (sharing) on table1's row during the entire time the update is occuring. The update can not happen without this read-lock, and no other updates can happen to table1 while this statement has the read-lock in place.

The real issue that your prior SP had was that the read-lock on table 1 was released as soon as the SET @.var= was completed, which would allow someone to update table1 before table2 was updated. You could accomplish nearly the same thing with some of the locking hints, or changing the transaction isolation mode, but the SP won't execute as quickly as incorporating it into one statement like I did, and that means locks are being held longer than they need to be.

SET @.var=(SELECT ... WITH (HOLDLOCK) ...)

would have accomplished the same thing. If you add more steps to your transaction, then the read lock will be held until the completion of the transaction. With the combined UPDATE, the read lock is dropped when the UPDATE completes (The update lock on table2 is held until the end of the UPDATE if it's not in a transaction, or until the transaction is commited/rolled back...).

|||

Great explanation. It helped clarify an important point to me.

Thanks for that.

Friday, February 24, 2012

Can someone help me with multiple "Left Outer Joins"?

I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three
"Left Outer Joins" in order to return every transaction for a specific
set of criteria.

Using three "Left Outer Joins" slows the system down considerably.

I've tried creating a temp db, but I can't figure out how to execute
two select commands. (It throws the exception "The column prefix
'tempdb' does not match with a table name or alias name used in the
query.")

Looking for suggestions (and a lesson or two!) This is my first attempt
at SQL.

Current (working, albeit slowly) Query Below

TIA

SELECT

LEDGER_ENTRY.entry_amount,
LEDGER_TRANSACTION.credit_card_exp_date,
LEDGER_ENTRY.entry_datetime,
LEDGER_ENTRY.employee_id,
LEDGER_ENTRY.voucher_explanation,
LEDGER_ENTRY.card_reader_used_ind,
STAY.room_id,
GUEST.guest_lastname,
GUEST.guest_firstname,
STAY.arrival_time,
STAY.departure_time,
STAY.arrival_date,
STAY.original_departure_date,
STAY.no_show_status,
STAY.cancellation_date,
FOLIO.house_acct_id,
FOLIO.group_code,
LEDGER_TRANSACTION.original_receipt_id

FROM

mydb.dbo.LEDGER_ENTRY LEDGER_ENTRY,
mydb.dbo.LEDGER_TRANSACTION LEDGER_TRANSACTION,

mydb.dbo.FOLIO FOLIO
LEFT OUTER JOIN
mydb.dbo.STAY_FOLIO STAY_FOLIO
ON
FOLIO.folio_id = STAY_FOLIO.folio_id
LEFT OUTER JOIN
mydb.dbo.STAY STAY
ON
STAY_FOLIO.stay_id = STAY.stay_id
LEFT OUTER JOIN
mydb.dbo.GUEST GUEST
ON
FOLIO.guest_id = GUEST.guest_id

WHERE

LEDGER_ENTRY.trans_id = LEDGER_TRANSACTION.trans_id
AND FOLIO.folio_id = LEDGER_TRANSACTION.folio_id
AND LEDGER_ENTRY.payment_method='3737******6100'
AND LEDGER_ENTRY.property_id='abc123'

ORDER BY

LEDGER_ENTRY.entry_datetime DESCWhat is actually your question :-) ?

Jens Suessmeyer.|||My question is, Can this query be further optimized for speed?

I have tried creating temporary databases, to break-up the outer joins
into different select commands, but I couldn't get it to work properly.
I'm using VB6 and ADO, invoking the execute method of the adodb.command
object to return the recordset.|||To take access to different databases and tables you may
use for example syntax like this:
DatabaseName.TableName.ColumnName

I do not see why you would need to create a
temporal db and why this would help you
with performance.

I wonder if you meant a temporal table instead.

In general I experienced, that views (depending on what the do) may slow
down the whole query.
also ORDER BY.

I suggest you break your select statement in three peaces so you may
see with the profiler wich join would take the most of time.
maybe by applying an index to specific columns you get a bit more
performance.

if you watch the query from your VB-application, you wil have to
differ between the time thats used by your application and ADO
and the time the Database itself needs.
the bottleneck could also be at the application-side!

Hope this gave some hints.

Sonja

"Steve" <budgethelp@.yahoo.com> schrieb im Newsbeitrag
news:1126754368.398119.129660@.o13g2000cwo.googlegr oups.com...
>I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three
> "Left Outer Joins" in order to return every transaction for a specific
> set of criteria.
> Using three "Left Outer Joins" slows the system down considerably.
> I've tried creating a temp db, but I can't figure out how to execute
> two select commands. (It throws the exception "The column prefix
> 'tempdb' does not match with a table name or alias name used in the
> query.")
> Looking for suggestions (and a lesson or two!) This is my first attempt
> at SQL.
> Current (working, albeit slowly) Query Below
> TIA
> SELECT
> LEDGER_ENTRY.entry_amount,
> LEDGER_TRANSACTION.credit_card_exp_date,
> LEDGER_ENTRY.entry_datetime,
> LEDGER_ENTRY.employee_id,
> LEDGER_ENTRY.voucher_explanation,
> LEDGER_ENTRY.card_reader_used_ind,
> STAY.room_id,
> GUEST.guest_lastname,
> GUEST.guest_firstname,
> STAY.arrival_time,
> STAY.departure_time,
> STAY.arrival_date,
> STAY.original_departure_date,
> STAY.no_show_status,
> STAY.cancellation_date,
> FOLIO.house_acct_id,
> FOLIO.group_code,
> LEDGER_TRANSACTION.original_receipt_id
> FROM
> mydb.dbo.LEDGER_ENTRY LEDGER_ENTRY,
> mydb.dbo.LEDGER_TRANSACTION LEDGER_TRANSACTION,
> mydb.dbo.FOLIO FOLIO
> LEFT OUTER JOIN
> mydb.dbo.STAY_FOLIO STAY_FOLIO
> ON
> FOLIO.folio_id = STAY_FOLIO.folio_id
> LEFT OUTER JOIN
> mydb.dbo.STAY STAY
> ON
> STAY_FOLIO.stay_id = STAY.stay_id
> LEFT OUTER JOIN
> mydb.dbo.GUEST GUEST
> ON
> FOLIO.guest_id = GUEST.guest_id
> WHERE
> LEDGER_ENTRY.trans_id = LEDGER_TRANSACTION.trans_id
> AND FOLIO.folio_id = LEDGER_TRANSACTION.folio_id
> AND LEDGER_ENTRY.payment_method='3737******6100'
> AND LEDGER_ENTRY.property_id='abc123'
> ORDER BY
> LEDGER_ENTRY.entry_datetime DESC|||Yes, I meant that I tried to create a temporary table, not db, sorry...

Regarding the 3 joins...would putting parenthesis around any of them
help?
How are they being processed exactly?
The first join has a single table reference immediately preceding the
join statement, but the others cannot (is that correct?)
What are the next two joins being joined to exacty (since there is no
table specified before the two join statements?

The tables that I'm joining look like this:

--FOLIO-----STAY FOLIO------STAY
|
|
|___________GUEST

All transactions have FOLIO records, but not all transactions have STAY
FOLIO, STAY, OR GUEST records.
I need to return all transactions that have a folio record.

This is the syntax I'm using to accomplish this:

I don't understand how the order of the joins affects their processing.
Is there a better way to phrase the joins, given the table
relationships as outlined above?

Thanks!|||> Yes, I meant that I tried to create a temporary table, not db, sorry...

this would be accomplished with views. Like I mentioned before
but this may not be a Solution for your problem.

As I know a lot of Select Squences with a lot more Joins
than you need here, I do not believe that your performance-problem
results from the sql statement.
Depending on the server-machine your Database is installed on,
there may be different reasons, why this query takes a long time.

1)Maybe your tables are big. Lets asume each of them has 1 000 000 tuples.
Even then the query should not last (DEPENDING ON YOUR MACHINE)
a "long" time.
If this Machine is for example the whole time working on a 70% level
it slows down everything to death.
If the machine has enough breath to acomplish your query and you are testing
just solely we leave this section ...

2)the dbms tries to optimize sql -queries by itself, to make them faster, if
you want to
optimize more, use only the lines and columns you seek. It makes the whole
thing
a little faster if you simply snip columns and rows that you do not need.

3) it may help with performance to apply indexes to columns that will be
joined

4) Your application is getting all data over network one by one and
everything
slows down. Then its not a database or query -problem

5) use the SQL Profiler to see where the bottleneck is.

If you like, create for each join a view and then simply join the view with
folio
like this for example
------------
CREATE VIEW stay_test AS
Select Stay_folio.folio_id from
STAY_FOLIO left outer join STAY
ON
stay_folio.stay_id = stay.stay_id
-----------
SELECT * FROM
folio LEFT OUTER JOIN stay_test
ON
folio.folio_id = stay_test.folio_id
LEFT OUTER JOIN guest
ON
folio.guest_id = guest.guest_id
-----------

At the SQL profiler you can view each selection that is made and how long it
takes to get result

> Regarding the 3 joins...would putting parenthesis around any of them
> help?
> How are they being processed exactly?
> The first join has a single table reference immediately preceding the
> join statement, but the others cannot (is that correct?)
> What are the next two joins being joined to exacty (since there is no
> table specified before the two join statements?
> The tables that I'm joining look like this:
> --FOLIO-----STAY FOLIO------STAY
> |
> |
> |___________GUEST
> All transactions have FOLIO records, but not all transactions have STAY
> FOLIO, STAY, OR GUEST records.
> I need to return all transactions that have a folio record.
> This is the syntax I'm using to accomplish this:
> mydb.dbo.FOLIO FOLIO
> LEFT OUTER JOIN
> mydb.dbo.STAY_FOLIO STAY_FOLIO
> ON
> FOLIO.folio_id = STAY_FOLIO.folio_id
> LEFT OUTER JOIN
> mydb.dbo.STAY STAY
> ON
> STAY_FOLIO.stay_id = STAY.stay_id
> LEFT OUTER JOIN
> mydb.dbo.GUEST GUEST
> ON
> FOLIO.guest_id = GUEST.guest_id
> I don't understand how the order of the joins affects their processing.
> Is there a better way to phrase the joins, given the table
> relationships as outlined above?
> Thanks!|||On 14 Sep 2005 20:19:28 -0700, Steve wrote:

>I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three
>"Left Outer Joins" in order to return every transaction for a specific
>set of criteria.
>Using three "Left Outer Joins" slows the system down considerably.

Hi Steve,

That need not be the case. I guess that adding the right indexes would
help a lot.

>I've tried creating a temp db, but I can't figure out how to execute
>two select commands. (It throws the exception "The column prefix
>'tempdb' does not match with a table name or alias name used in the
>query.")

I could help you with solving this problem, but I won't. Breaking a
query in smaller pieces with temp tables has a fair chance to hurt your
performance, and very limited chance to do any good.

The query optimizer can use all the tricks that you can use, and then
some. Better to trust that the optimizer will pick the right execution
plan from the flock of available options instead of forcing it to do the
way you think is best. There ARE cases where the optimizer does need
some guidance, but they are the exception rather than the rule.

>Current (working, albeit slowly) Query Below

Thanks for posting the query, but you'll have to provide a lot more
information to enable us to help you. We need to know the structure of
your tables (posted as CREATE TABLE statements, including all properties
and constraints, but excluding irrelevant columns), the indexes you have
defined for your tables, if any (posted as CREATE INDEX statements), a
few rows of sample data (posted as INSERT statements) and the expected
results from that sample data to give us an idea what you're trying to
achieve. Including a short description of your actual business problem
is a great idea too. See www.aspfaq.com/5006 for some useful pointers on
hjow to assemble the information we need, in the best format.

Oh, and we'd also like to know how many rows (approximately) you have in
each of your tables - and the execution plan that is currently used for
your query (you can get the execution plan if you run the query with SET
SHOWPLAN_ALL ON.

With that information, we can try to find out why your current query is
running slow, and how to remedy that.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Steve (budgethelp@.yahoo.com) writes:
> I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three
> "Left Outer Joins" in order to return every transaction for a specific
> set of criteria.
> Using three "Left Outer Joins" slows the system down considerably.
> I've tried creating a temp db, but I can't figure out how to execute
> two select commands. (It throws the exception "The column prefix
> 'tempdb' does not match with a table name or alias name used in the
> query.")
> Looking for suggestions (and a lesson or two!) This is my first attempt
> at SQL.

As Hugo pointed out, it is impossible to give very precise advice from
from the information you have posted. Assuming that there is an index
on (payment_method, property_id) on LEDGER_ENTRY, and that all other
tables have indexes on the columns you join on, I would expect the query
to perform well. Then again, there can be several reasons to why it does
not.

I analysed your query, and I think that I found one flaw. Here is a
rewritten version:

SELECT LE.entry_amount, LT.credit_card_exp_date, LE.entry_datetime,
LE.employee_id, LE.voucher_explanation, LE.card_reader_used_ind,
S.room_id, G.guest_lastname, G.guest_firstname, S.arrival_time,
S.departure_time, S.arrival_date, S.original_departure_date,
S.no_show_status, S.cancellation_date, F.house_acct_id,
F.group_code, LT.original_receipt_id
FROM mydb.dbo.LEDGER_ENTRY LE
JOIN mydb.dbo.LEDGER_TRANSACTON LT ON LE.trans_id = LT.trans_id
JOIN mydb.dbo.FOLIO F ON F.folio_id = LT.folio_id
LEFT JOIN (mydb.dbo.STAY_FOLIO SF
JOIN mydb.dbo.STAY S ON SF.stay_id = S.stay_id)
ON F.folio_id = SF.folio_id
LEFT JOIN mydb.dbo.GUEST G ON F.guest_id = G.guest_id
WHERE LE.payment_method='3737******6100'
AND LE.property_id='abc123'
ORDER BY LE.entry_datetime DESC

This alters the semantics of the query slightly, and I guess to the
good. Whether it affects performance, I don't know.

One potential problem is if the joins from FOLIO to STAY_FOLIO and GUEST
could hit multiple rows in the latter tables. In such case you get too
many rows back, which also could cause poor performance.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Sunday, February 19, 2012

Can several UPDATE statements deadlock within serializable transaction

This can cause conversion deadlock:
==== set transaction isolation level serializable
begin tran
select * from authors where au_id = 'bla'
update authors set au_lname = au_lname where au_id = 'bla'
commit
==
because shared locks in serializable transactions are held for the duration
of the transaction and exculsive locks are not compatible with shared locks
from another transaction.
Here is the question - can this deadlock as well?
==== set transaction isolation level serializable
update authors set au_lname = au_lname where au_id = 'bla'
commit
==
If this can deadlock, how can I prevent it?
I am trying to resolve COM+ deadlocking issues...
Thanks,
-StanWhy are you using a SERIALIZABLE level?
In the update, could you get away with specifying
an UPDLOCK hint instead?
"Stan" <nospam@.yahoo.com> wrote in message
news:OtW9NmOIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> This can cause conversion deadlock:
> ====> set transaction isolation level serializable
> begin tran
> select * from authors where au_id = 'bla'
> update authors set au_lname = au_lname where au_id = 'bla'
> commit
> ==> because shared locks in serializable transactions are held for the
duration
> of the transaction and exculsive locks are not compatible with shared
locks
> from another transaction.
> Here is the question - can this deadlock as well?
> ====> set transaction isolation level serializable
> update authors set au_lname = au_lname where au_id = 'bla'
> commit
> ==> If this can deadlock, how can I prevent it?
> I am trying to resolve COM+ deadlocking issues...
> Thanks,
> -Stan
>|||1. I am not using serializable, COM+ is
2. I can put UPDLOCK, but will it have an effect in UPDATE statement?
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:%23F$WQFPIFHA.2752@.TK2MSFTNGP12.phx.gbl...
> Why are you using a SERIALIZABLE level?
> In the update, could you get away with specifying
> an UPDLOCK hint instead?
> "Stan" <nospam@.yahoo.com> wrote in message
> news:OtW9NmOIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> > This can cause conversion deadlock:
> >
> > ====> > set transaction isolation level serializable
> >
> > begin tran
> >
> > select * from authors where au_id = 'bla'
> >
> > update authors set au_lname = au_lname where au_id = 'bla'
> >
> > commit
> >
> > ==> >
> > because shared locks in serializable transactions are held for the
> duration
> > of the transaction and exculsive locks are not compatible with shared
> locks
> > from another transaction.
> >
> > Here is the question - can this deadlock as well?
> >
> > ====> > set transaction isolation level serializable
> >
> > update authors set au_lname = au_lname where au_id = 'bla'
> >
> > commit
> >
> > ==> >
> > If this can deadlock, how can I prevent it?
> >
> > I am trying to resolve COM+ deadlocking issues...
> >
> > Thanks,
> >
> > -Stan
> >
> >
>|||2. UPDATE hint should be put on select statement
--
Thank you,
Alex
"Stan" <nospam@.yahoo.com> wrote in message
news:eNSiFqPIFHA.3760@.TK2MSFTNGP12.phx.gbl...
> 1. I am not using serializable, COM+ is
> 2. I can put UPDLOCK, but will it have an effect in UPDATE statement?
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:%23F$WQFPIFHA.2752@.TK2MSFTNGP12.phx.gbl...
> > Why are you using a SERIALIZABLE level?
> >
> > In the update, could you get away with specifying
> > an UPDLOCK hint instead?
> >
> > "Stan" <nospam@.yahoo.com> wrote in message
> > news:OtW9NmOIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> > > This can cause conversion deadlock:
> > >
> > > ====> > > set transaction isolation level serializable
> > >
> > > begin tran
> > >
> > > select * from authors where au_id = 'bla'
> > >
> > > update authors set au_lname = au_lname where au_id = 'bla'
> > >
> > > commit
> > >
> > > ==> > >
> > > because shared locks in serializable transactions are held for the
> > duration
> > > of the transaction and exculsive locks are not compatible with shared
> > locks
> > > from another transaction.
> > >
> > > Here is the question - can this deadlock as well?
> > >
> > > ====> > > set transaction isolation level serializable
> > >
> > > update authors set au_lname = au_lname where au_id = 'bla'
> > >
> > > commit
> > >
> > > ==> > >
> > > If this can deadlock, how can I prevent it?
> > >
> > > I am trying to resolve COM+ deadlocking issues...
> > >
> > > Thanks,
> > >
> > > -Stan
> > >
> > >
> >
> >
>|||Use the hint on the SELECT
ie
SELECT mycolumn
FROM mytable WITH (UPDLOCK)
WHERE ID = @.ID
I don't know anything about COM+ so I couldn't
say how to suppress the SERIALIZABLE... it
seems too drastic.
"Stan" <nospam@.yahoo.com> wrote in message
news:eNSiFqPIFHA.3760@.TK2MSFTNGP12.phx.gbl...
> 1. I am not using serializable, COM+ is
> 2. I can put UPDLOCK, but will it have an effect in UPDATE statement?
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:%23F$WQFPIFHA.2752@.TK2MSFTNGP12.phx.gbl...
> > Why are you using a SERIALIZABLE level?
> >
> > In the update, could you get away with specifying
> > an UPDLOCK hint instead?
> >
> > "Stan" <nospam@.yahoo.com> wrote in message
> > news:OtW9NmOIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> > > This can cause conversion deadlock:
> > >
> > > ====> > > set transaction isolation level serializable
> > >
> > > begin tran
> > >
> > > select * from authors where au_id = 'bla'
> > >
> > > update authors set au_lname = au_lname where au_id = 'bla'
> > >
> > > commit
> > >
> > > ==> > >
> > > because shared locks in serializable transactions are held for the
> > duration
> > > of the transaction and exculsive locks are not compatible with shared
> > locks
> > > from another transaction.
> > >
> > > Here is the question - can this deadlock as well?
> > >
> > > ====> > > set transaction isolation level serializable
> > >
> > > update authors set au_lname = au_lname where au_id = 'bla'
> > >
> > > commit
> > >
> > > ==> > >
> > > If this can deadlock, how can I prevent it?
> > >
> > > I am trying to resolve COM+ deadlocking issues...
> > >
> > > Thanks,
> > >
> > > -Stan
> > >
> > >
> >
> >
>|||Hi Stan,
I've been reading a lot about locking and transaction isolation level
lately, as I was troubleshooting deadlocks from dll's running under COM+ as
well. I don't think a single update statement can cause deadlocks. Is there
a select statement in the calling client code that runs within the same
transaction? In C# code - which I was reviewing - I had to look for methods
with the attribute "Autocomplete()" within classes with the attribute
"Transaction(TransactionOption.Required)". This meant that the execution of
this method will be encapsulated in 1 transaction. If no unhandled exception
occurs, the transaction is automatically committed and otherwise it is
rolled back. Which leads me to the question if the T-SQL really has a
"commit" statement, because it isn't needed and could maybe even cause an
error (I'm not sure how COM+ reacts to a "no current transaction available"
when it tries to commit after the transaction is already closed, it may or
may not check the @.@.trancount function.
This is al speculative, now some solid advice: execute the following
statement in query analyzer as sa user: "dbcc traceon(-1, 1204)". 1204
instructs Sql Server to log deadlock information to the error log file - you
can find it under Management in the enterprise manager -; -1 makes the
traceflag global instead of limited to the current session. If you search in
google with "deadlock 1204" you'll learn how to interpret the error log.
This was a great help for me when investigating the deadlock situations.
Cheers,
Henk Kok
"Stan" <nospam@.yahoo.com> schreef in bericht
news:OtW9NmOIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> This can cause conversion deadlock:
> ====> set transaction isolation level serializable
> begin tran
> select * from authors where au_id = 'bla'
> update authors set au_lname = au_lname where au_id = 'bla'
> commit
> ==> because shared locks in serializable transactions are held for the
> duration
> of the transaction and exculsive locks are not compatible with shared
> locks
> from another transaction.
> Here is the question - can this deadlock as well?
> ====> set transaction isolation level serializable
> update authors set au_lname = au_lname where au_id = 'bla'
> commit
> ==> If this can deadlock, how can I prevent it?
> I am trying to resolve COM+ deadlocking issues...
> Thanks,
> -Stan
>|||> transaction? In C# code - which I was reviewing - I had to look for
methods
> with the attribute "Autocomplete()" within classes with the attribute
> "Transaction(TransactionOption.Required)". This meant that the execution
of
> this method will be encapsulated in 1 transaction.
I understand that. However all my "get" stored procedures have
"set transaction isolation level read uncommitted" statement. This should
removed the shared locks immidiately after select statement, but I still
have deadlocks...
"Update" stored procedures do not have this statement and I was wondering if
this can be a cause of deadlocking..

Can Select ever be in Xact log?

Can select statements ever go into the transaction log?Selects do not get logged in the tran log. If you want to see what
statements are being executed you should use trace or profiler for that.
Andrew J. Kelly SQL MVP
"Jim Weiler" <lisajimbo@.rcn.com> wrote in message
news:dJSdnfm07fFFuq3eRVn-3A@.rcn.net...
> Can select statements ever go into the transaction log?
>|||Jim
A select into will write to the transaction log, but as Andrew says regular
selects will not.
I once got asked this question at an interview, after answering the
question, the interviewer told me he was sorry but I was wrong and all
selects were written to the transaction log. It’s a bit hard to argue too
strongly with someone interviewing you. I told him I thought he was incorrec
t
and left it at that. He got a couple more questions wrong later in the
interview and I did not get the job because my technical knowledge was not
good enough.
Regards
John
"Andrew J. Kelly" wrote:

> Selects do not get logged in the tran log. If you want to see what
> statements are being executed you should use trace or profiler for that.
> --
> Andrew J. Kelly SQL MVP
>
> "Jim Weiler" <lisajimbo@.rcn.com> wrote in message
> news:dJSdnfm07fFFuq3eRVn-3A@.rcn.net...
>
>|||Hi John !

> A select into will write to the transaction log
I admit that I haven't used a log reader to investigate the log records for
SELECT INTO (or INSERT
... SELECT for that matter), but I will venture a guess that what is record
ed would be each inserted
row and not the SELECT statement. One could argue that the inserted rows wil
l show you what the
SELECT returned, of course. Splitting hairs, I guess... :-)

> I once got asked this question at an interview, <snip>
I hate it when this happens. My experiences are from MCT tests. I score far
higher on products I
don't know that well, where I only score OK on SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in messag
e
news:11C530BB-73B2-416F-9FED-E0D904B63C2C@.microsoft.com...[vbcol=seagreen]
> Jim
> A select into will write to the transaction log, but as Andrew says regula
r
> selects will not.
> I once got asked this question at an interview, after answering the
> question, the interviewer told me he was sorry but I was wrong and all
> selects were written to the transaction log. It’s a bit hard to argue to
o
> strongly with someone interviewing you. I told him I thought he was incorr
ect
> and left it at that. He got a couple more questions wrong later in the
> interview and I did not get the job because my technical knowledge was not
> good enough.
> Regards
> John
>
> "Andrew J. Kelly" wrote:
>

Can Select ever be in Xact log?

Can select statements ever go into the transaction log?
Selects do not get logged in the tran log. If you want to see what
statements are being executed you should use trace or profiler for that.
Andrew J. Kelly SQL MVP
"Jim Weiler" <lisajimbo@.rcn.com> wrote in message
news:dJSdnfm07fFFuq3eRVn-3A@.rcn.net...
> Can select statements ever go into the transaction log?
>
|||Jim
A select into will write to the transaction log, but as Andrew says regular
selects will not.
I once got asked this question at an interview, after answering the
question, the interviewer told me he was sorry but I was wrong and all
selects were written to the transaction log. It’s a bit hard to argue too
strongly with someone interviewing you. I told him I thought he was incorrect
and left it at that. He got a couple more questions wrong later in the
interview and I did not get the job because my technical knowledge was not
good enough.
Regards
John
"Andrew J. Kelly" wrote:

> Selects do not get logged in the tran log. If you want to see what
> statements are being executed you should use trace or profiler for that.
> --
> Andrew J. Kelly SQL MVP
>
> "Jim Weiler" <lisajimbo@.rcn.com> wrote in message
> news:dJSdnfm07fFFuq3eRVn-3A@.rcn.net...
>
>
|||Hi John !

> A select into will write to the transaction log
I admit that I haven't used a log reader to investigate the log records for SELECT INTO (or INSERT
... SELECT for that matter), but I will venture a guess that what is recorded would be each inserted
row and not the SELECT statement. One could argue that the inserted rows will show you what the
SELECT returned, of course. Splitting hairs, I guess... :-)

> I once got asked this question at an interview, <snip>
I hate it when this happens. My experiences are from MCT tests. I score far higher on products I
don't know that well, where I only score OK on SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in message
news:11C530BB-73B2-416F-9FED-E0D904B63C2C@.microsoft.com...[vbcol=seagreen]
> Jim
> A select into will write to the transaction log, but as Andrew says regular
> selects will not.
> I once got asked this question at an interview, after answering the
> question, the interviewer told me he was sorry but I was wrong and all
> selects were written to the transaction log. It’s a bit hard to argue too
> strongly with someone interviewing you. I told him I thought he was incorrect
> and left it at that. He got a couple more questions wrong later in the
> interview and I did not get the job because my technical knowledge was not
> good enough.
> Regards
> John
>
> "Andrew J. Kelly" wrote:

Can Select ever be in Xact log?

Can select statements ever go into the transaction log?Selects do not get logged in the tran log. If you want to see what
statements are being executed you should use trace or profiler for that.
--
Andrew J. Kelly SQL MVP
"Jim Weiler" <lisajimbo@.rcn.com> wrote in message
news:dJSdnfm07fFFuq3eRVn-3A@.rcn.net...
> Can select statements ever go into the transaction log?
>|||Jim
A select into will write to the transaction log, but as Andrew says regular
selects will not.
I once got asked this question at an interview, after answering the
question, the interviewer told me he was sorry but I was wrong and all
selects were written to the transaction log. Itâ's a bit hard to argue too
strongly with someone interviewing you. I told him I thought he was incorrect
and left it at that. He got a couple more questions wrong later in the
interview and I did not get the job because my technical knowledge was not
good enough.
Regards
John
"Andrew J. Kelly" wrote:
> Selects do not get logged in the tran log. If you want to see what
> statements are being executed you should use trace or profiler for that.
> --
> Andrew J. Kelly SQL MVP
>
> "Jim Weiler" <lisajimbo@.rcn.com> wrote in message
> news:dJSdnfm07fFFuq3eRVn-3A@.rcn.net...
> > Can select statements ever go into the transaction log?
> >
>
>|||Hi John !
> A select into will write to the transaction log
I admit that I haven't used a log reader to investigate the log records for SELECT INTO (or INSERT
... SELECT for that matter), but I will venture a guess that what is recorded would be each inserted
row and not the SELECT statement. One could argue that the inserted rows will show you what the
SELECT returned, of course. Splitting hairs, I guess... :-)
> I once got asked this question at an interview, <snip>
I hate it when this happens. My experiences are from MCT tests. I score far higher on products I
don't know that well, where I only score OK on SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in message
news:11C530BB-73B2-416F-9FED-E0D904B63C2C@.microsoft.com...
> Jim
> A select into will write to the transaction log, but as Andrew says regular
> selects will not.
> I once got asked this question at an interview, after answering the
> question, the interviewer told me he was sorry but I was wrong and all
> selects were written to the transaction log. Itâ's a bit hard to argue too
> strongly with someone interviewing you. I told him I thought he was incorrect
> and left it at that. He got a couple more questions wrong later in the
> interview and I did not get the job because my technical knowledge was not
> good enough.
> Regards
> John
>
> "Andrew J. Kelly" wrote:
>> Selects do not get logged in the tran log. If you want to see what
>> statements are being executed you should use trace or profiler for that.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jim Weiler" <lisajimbo@.rcn.com> wrote in message
>> news:dJSdnfm07fFFuq3eRVn-3A@.rcn.net...
>> > Can select statements ever go into the transaction log?
>> >
>>

Tuesday, March 27, 2012

Can we do Transaction log backup and log shipping on same server.

Can we do Transaction log backup and log shipping on same server.

can we control how transaction send from principal to mirror ?

Thursday, March 22, 2012

Can u pls help with the trasaction isolation levels

Tuesday, March 20, 2012

Can t-log size handle large number of deletions?

can this be done?

Monday, March 19, 2012

Can the use of Shrinkfile break the Transaction Log chain?

Can the transaction log be turned off?

Sunday, March 11, 2012

Can table1 in example below get updated by another process while the transaction is in pro

Friday, February 24, 2012

Can someone help me with multiple "Left Outer Joins"?

Sunday, February 19, 2012

Can several UPDATE statements deadlock within serializable transaction

Can several UPDATE statements deadlock within serializable transaction

Can Select ever be in Xact log?

Can Select ever be in Xact log?

Can Select ever be in Xact log?

sql reports

Blog Archive

About Me