i havnt used sql in a long time and now my job requires it. il be using it in access, maby oricle and maby sql server. i need a book that teaches general sql. all about updates, deletes,dates, joins, corrrelates sub queries, aliases,and anything else i could encounter that will cause me to pull my hair out in sql.maby even it it went into TSQL that would be useful.any recommendations?Did you visit Amazon.com, for example? Search through "books" and use keyword "SQL" (as you don't know which one you'll use). Check readers' reviews - could be helpful.|||The SQL Bible is probably good. I heard it will blow your mind what all it has. :) I may pick up both the one for Access and SQL. The books titled "The complete reference book of ____" are also good i heard|||I prefer:
Transact-SQL:
The Guru's Guide to Transact SQL and XML (or something like that) by Ken Henderson
Teach Yourself Transact-SQL in 21 Days by Sam's (believe it or not)
Architecture:
Inside SQL Server by Kalen Delaney (it's the best out there)
Performance Tuning:
SQL Server 2000 Performance Tuning Manual by Microsoft Press|||Each SQL implementation is different. Of the three you've listed, there is a wide variety of syntax supported. MS-SQL implementes full SQL-92. Jet (the engine used by MS-Access) implements a large subset of SQL-87 with a lot of the SQL-92 features grafted on in peculiar ways. Oracle supports most of the features of SQL-92, but using Oracle's non-standard syntax.
I don't know of any one book that covers all of the possible SQL-like variations. I like Derrick's suggestions for MS-SQL. If you are looking for a very generic SQL overview, I'd suggest SQL for Dummies. If you want quality background that will help you understand how you should use SQL (but not get far into any particular implementation), I'd recommend Joe Celko's SQL For Smarties.
In short, I don't think exactly what you want can exist, but there are lots of choices that might be "good enough" for what you really want... You just have to pick the one that seems the closest to what you think you'll need and go from there.
-PatP
Showing posts with label maby. Show all posts
Showing posts with label maby. Show all posts
Tuesday, March 20, 2012
Sunday, March 11, 2012
Blocking Problem
Hi there,
maby someone know something about this kind of strange behaviour. One of my
colleagues has made an ASP appl. using SQL 2000 and I helped him move data
to another SQL Server 2000 . We just detached and attached and he changed
the connectionstring and it just worked fine... For a while. Suddenly the
users reported that the couldn't get a certain list of records and when I
checked the server there was a couple of X-locks on a table held by the same
SPID. The locks are on key-level. I've tested it and the problem seems to be
the Transaction handling in some way. I started the profiler and filtered
the trace to see
the SPID who made the locks and the T-SQL looked something like this :
Begin Transaction
Select...
Update...
Select..
Another one
Begin Transaction
Insert...
No commit or rollback even though my colleague tells me that he either
commits or rolls back his transaction in his code. But it looks like it
never hits the server ' Is this a known issue ?
Could it be a Service Pack issue. There's no SP's installed. It worked fine
when we were running on the old server.. One difference between the old
server and the new server is that the new one is installed as a named
instance. A lot of the select statements uses a linked server but not the
actual update, insert, and delete statement. they're on the loval server.
All the locks has Owner type XAct. I really cannot figure out why the
Transactions hang. Is there any known issues about named instances and
linked servers ?
Anybody got a clue ?
Regards .)
Bobby HenningsenHi Bobby
Try checking DBCC OPENTRAN to see if any transactions are open. You don't
say which events you are profiling, but have you included transactions/SQL
Transactions? IT is not unknown for profiler to not include some logging,
expecially on a busy server and you are profiling on the same server.
You don't say if you have performed any maintenance on this database, you
may want to defragment the indexes and update the statistics.
Without seeing the actual code it is hard to comment on if it can be
improved. You may want to see if you need the select statement before the
update, or if you are unneccesarily wrapping select statements in
transactions. If your select statement before the update uses a linked serve
r
this may force the transaction to be a distributed transaction.
John
"Bobby Henningsen" wrote:
> Hi there,
> maby someone know something about this kind of strange behaviour. One of m
y
> colleagues has made an ASP appl. using SQL 2000 and I helped him move data
> to another SQL Server 2000 . We just detached and attached and he changed
> the connectionstring and it just worked fine... For a while. Suddenly the
> users reported that the couldn't get a certain list of records and when I
> checked the server there was a couple of X-locks on a table held by the sa
me
> SPID. The locks are on key-level. I've tested it and the problem seems to
be
> the Transaction handling in some way. I started the profiler and filtered
> the trace to see
> the SPID who made the locks and the T-SQL looked something like this :
> Begin Transaction
> Select...
> Update...
> Select..
> Another one
> Begin Transaction
> Insert...
> No commit or rollback even though my colleague tells me that he either
> commits or rolls back his transaction in his code. But it looks like it
> never hits the server ' Is this a known issue ?
> Could it be a Service Pack issue. There's no SP's installed. It worked fin
e
> when we were running on the old server.. One difference between the old
> server and the new server is that the new one is installed as a named
> instance. A lot of the select statements uses a linked server but not the
> actual update, insert, and delete statement. they're on the loval server.
> All the locks has Owner type XAct. I really cannot figure out why the
> Transactions hang. Is there any known issues about named instances and
> linked servers ?
> Anybody got a clue ?
> Regards .)
> Bobby Henningsen
>
>|||Hi John,
i caqn see that theres is an open transaction under "Current Activity" so
I'm quite sure about that. I prodfiling the T-SQL stmt starting and batch
starting. The profiler is not running on the same server. I've updated all
the statistics.
I agree with you about the select statement and it eventually would be a
distributed transaction. But still. This was working on another SQL Server
2000 (And still are. They had to move the database back). I can'tfigure out
what's the difference other than this is running as a named instance. They
wil install SP3 this week and then we'll have to see. Another strange thing
is that the select staement mentioned is on a view which does a linked
server query and it then holds an Sch-S lock on the view. So when I look
under "Curent Activity" I see the X-locks but also 6-7 Sch-S locks on the
view!
Regards
Bobby Henningsen
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6EF40E94-3429-484E-8B06-785BBF86D4F1@.microsoft.com...[vbcol=seagreen]
> Hi Bobby
> Try checking DBCC OPENTRAN to see if any transactions are open. You don't
> say which events you are profiling, but have you included transactions/SQL
> Transactions? IT is not unknown for profiler to not include some logging,
> expecially on a busy server and you are profiling on the same server.
> You don't say if you have performed any maintenance on this database, you
> may want to defragment the indexes and update the statistics.
> Without seeing the actual code it is hard to comment on if it can be
> improved. You may want to see if you need the select statement before the
> update, or if you are unneccesarily wrapping select statements in
> transactions. If your select statement before the update uses a linked
> server
> this may force the transaction to be a distributed transaction.
> John
> "Bobby Henningsen" wrote:
>
---
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at f 234 spam-mails.
Betalende brugere fr ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dk|||Hi Bobby
SP3a would be a minumum requirement and if you are looking at staying with
SQL 2000 for a while you should consider SP4 + patching to 2187 (after prope
r
evaluation and testing!)
John
"Bobby Henningsen" wrote:
> Hi John,
> i caqn see that theres is an open transaction under "Current Activity" so
> I'm quite sure about that. I prodfiling the T-SQL stmt starting and batch
> starting. The profiler is not running on the same server. I've updated all
> the statistics.
> I agree with you about the select statement and it eventually would be a
> distributed transaction. But still. This was working on another SQL Serve
r
> 2000 (And still are. They had to move the database back). I can'tfigure ou
t
> what's the difference other than this is running as a named instance. They
> wil install SP3 this week and then we'll have to see. Another strange thin
g
> is that the select staement mentioned is on a view which does a linked
> server query and it then holds an Sch-S lock on the view. So when I look
> under "Curent Activity" I see the X-locks but also 6-7 Sch-S locks on the
> view!
> Regards
> Bobby Henningsen
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:6EF40E94-3429-484E-8B06-785BBF86D4F1@.microsoft.com...
>
> --
> ---
> Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
> Den har indtil videre sparet mig for at f? 234 spam-mails.
> Betalende brugere f?r ikke denne besked i deres e-mails.
> Hent gratis SPAMfighter her: www.spamfighter.dk
>
>
maby someone know something about this kind of strange behaviour. One of my
colleagues has made an ASP appl. using SQL 2000 and I helped him move data
to another SQL Server 2000 . We just detached and attached and he changed
the connectionstring and it just worked fine... For a while. Suddenly the
users reported that the couldn't get a certain list of records and when I
checked the server there was a couple of X-locks on a table held by the same
SPID. The locks are on key-level. I've tested it and the problem seems to be
the Transaction handling in some way. I started the profiler and filtered
the trace to see
the SPID who made the locks and the T-SQL looked something like this :
Begin Transaction
Select...
Update...
Select..
Another one
Begin Transaction
Insert...
No commit or rollback even though my colleague tells me that he either
commits or rolls back his transaction in his code. But it looks like it
never hits the server ' Is this a known issue ?
Could it be a Service Pack issue. There's no SP's installed. It worked fine
when we were running on the old server.. One difference between the old
server and the new server is that the new one is installed as a named
instance. A lot of the select statements uses a linked server but not the
actual update, insert, and delete statement. they're on the loval server.
All the locks has Owner type XAct. I really cannot figure out why the
Transactions hang. Is there any known issues about named instances and
linked servers ?
Anybody got a clue ?
Regards .)
Bobby HenningsenHi Bobby
Try checking DBCC OPENTRAN to see if any transactions are open. You don't
say which events you are profiling, but have you included transactions/SQL
Transactions? IT is not unknown for profiler to not include some logging,
expecially on a busy server and you are profiling on the same server.
You don't say if you have performed any maintenance on this database, you
may want to defragment the indexes and update the statistics.
Without seeing the actual code it is hard to comment on if it can be
improved. You may want to see if you need the select statement before the
update, or if you are unneccesarily wrapping select statements in
transactions. If your select statement before the update uses a linked serve
r
this may force the transaction to be a distributed transaction.
John
"Bobby Henningsen" wrote:
> Hi there,
> maby someone know something about this kind of strange behaviour. One of m
y
> colleagues has made an ASP appl. using SQL 2000 and I helped him move data
> to another SQL Server 2000 . We just detached and attached and he changed
> the connectionstring and it just worked fine... For a while. Suddenly the
> users reported that the couldn't get a certain list of records and when I
> checked the server there was a couple of X-locks on a table held by the sa
me
> SPID. The locks are on key-level. I've tested it and the problem seems to
be
> the Transaction handling in some way. I started the profiler and filtered
> the trace to see
> the SPID who made the locks and the T-SQL looked something like this :
> Begin Transaction
> Select...
> Update...
> Select..
> Another one
> Begin Transaction
> Insert...
> No commit or rollback even though my colleague tells me that he either
> commits or rolls back his transaction in his code. But it looks like it
> never hits the server ' Is this a known issue ?
> Could it be a Service Pack issue. There's no SP's installed. It worked fin
e
> when we were running on the old server.. One difference between the old
> server and the new server is that the new one is installed as a named
> instance. A lot of the select statements uses a linked server but not the
> actual update, insert, and delete statement. they're on the loval server.
> All the locks has Owner type XAct. I really cannot figure out why the
> Transactions hang. Is there any known issues about named instances and
> linked servers ?
> Anybody got a clue ?
> Regards .)
> Bobby Henningsen
>
>|||Hi John,
i caqn see that theres is an open transaction under "Current Activity" so
I'm quite sure about that. I prodfiling the T-SQL stmt starting and batch
starting. The profiler is not running on the same server. I've updated all
the statistics.
I agree with you about the select statement and it eventually would be a
distributed transaction. But still. This was working on another SQL Server
2000 (And still are. They had to move the database back). I can'tfigure out
what's the difference other than this is running as a named instance. They
wil install SP3 this week and then we'll have to see. Another strange thing
is that the select staement mentioned is on a view which does a linked
server query and it then holds an Sch-S lock on the view. So when I look
under "Curent Activity" I see the X-locks but also 6-7 Sch-S locks on the
view!
Regards
Bobby Henningsen
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6EF40E94-3429-484E-8B06-785BBF86D4F1@.microsoft.com...[vbcol=seagreen]
> Hi Bobby
> Try checking DBCC OPENTRAN to see if any transactions are open. You don't
> say which events you are profiling, but have you included transactions/SQL
> Transactions? IT is not unknown for profiler to not include some logging,
> expecially on a busy server and you are profiling on the same server.
> You don't say if you have performed any maintenance on this database, you
> may want to defragment the indexes and update the statistics.
> Without seeing the actual code it is hard to comment on if it can be
> improved. You may want to see if you need the select statement before the
> update, or if you are unneccesarily wrapping select statements in
> transactions. If your select statement before the update uses a linked
> server
> this may force the transaction to be a distributed transaction.
> John
> "Bobby Henningsen" wrote:
>
---
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at f 234 spam-mails.
Betalende brugere fr ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dk|||Hi Bobby
SP3a would be a minumum requirement and if you are looking at staying with
SQL 2000 for a while you should consider SP4 + patching to 2187 (after prope
r
evaluation and testing!)
John
"Bobby Henningsen" wrote:
> Hi John,
> i caqn see that theres is an open transaction under "Current Activity" so
> I'm quite sure about that. I prodfiling the T-SQL stmt starting and batch
> starting. The profiler is not running on the same server. I've updated all
> the statistics.
> I agree with you about the select statement and it eventually would be a
> distributed transaction. But still. This was working on another SQL Serve
r
> 2000 (And still are. They had to move the database back). I can'tfigure ou
t
> what's the difference other than this is running as a named instance. They
> wil install SP3 this week and then we'll have to see. Another strange thin
g
> is that the select staement mentioned is on a view which does a linked
> server query and it then holds an Sch-S lock on the view. So when I look
> under "Curent Activity" I see the X-locks but also 6-7 Sch-S locks on the
> view!
> Regards
> Bobby Henningsen
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:6EF40E94-3429-484E-8B06-785BBF86D4F1@.microsoft.com...
>
> --
> ---
> Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
> Den har indtil videre sparet mig for at f? 234 spam-mails.
> Betalende brugere f?r ikke denne besked i deres e-mails.
> Hent gratis SPAMfighter her: www.spamfighter.dk
>
>
Blocking Problem
Hi there,
maby someone know something about this kind of strange behaviour. One of my
colleagues has made an ASP appl. using SQL 2000 and I helped him move data
to another SQL Server 2000 . We just detached and attached and he changed
the connectionstring and it just worked fine... For a while. Suddenly the
users reported that the couldn't get a certain list of records and when I
checked the server there was a couple of X-locks on a table held by the same
SPID. The locks are on key-level. I've tested it and the problem seems to be
the Transaction handling in some way. I started the profiler and filtered
the trace to see
the SPID who made the locks and the T-SQL looked something like this :
Begin Transaction
Select...
Update...
Select..
Another one
Begin Transaction
Insert...
No commit or rollback even though my colleague tells me that he either
commits or rolls back his transaction in his code. But it looks like it
never hits the server ' Is this a known issue ?
Could it be a Service Pack issue. There's no SP's installed. It worked fine
when we were running on the old server.. One difference between the old
server and the new server is that the new one is installed as a named
instance. A lot of the select statements uses a linked server but not the
actual update, insert, and delete statement. they're on the loval server.
All the locks has Owner type XAct. I really cannot figure out why the
Transactions hang. Is there any known issues about named instances and
linked servers ?
Anybody got a clue ?
Regards .)
Bobby HenningsenHi Bobby
Try checking DBCC OPENTRAN to see if any transactions are open. You don't
say which events you are profiling, but have you included transactions/SQL
Transactions? IT is not unknown for profiler to not include some logging,
expecially on a busy server and you are profiling on the same server.
You don't say if you have performed any maintenance on this database, you
may want to defragment the indexes and update the statistics.
Without seeing the actual code it is hard to comment on if it can be
improved. You may want to see if you need the select statement before the
update, or if you are unneccesarily wrapping select statements in
transactions. If your select statement before the update uses a linked server
this may force the transaction to be a distributed transaction.
John
"Bobby Henningsen" wrote:
> Hi there,
> maby someone know something about this kind of strange behaviour. One of my
> colleagues has made an ASP appl. using SQL 2000 and I helped him move data
> to another SQL Server 2000 . We just detached and attached and he changed
> the connectionstring and it just worked fine... For a while. Suddenly the
> users reported that the couldn't get a certain list of records and when I
> checked the server there was a couple of X-locks on a table held by the same
> SPID. The locks are on key-level. I've tested it and the problem seems to be
> the Transaction handling in some way. I started the profiler and filtered
> the trace to see
> the SPID who made the locks and the T-SQL looked something like this :
> Begin Transaction
> Select...
> Update...
> Select..
> Another one
> Begin Transaction
> Insert...
> No commit or rollback even though my colleague tells me that he either
> commits or rolls back his transaction in his code. But it looks like it
> never hits the server ' Is this a known issue ?
> Could it be a Service Pack issue. There's no SP's installed. It worked fine
> when we were running on the old server.. One difference between the old
> server and the new server is that the new one is installed as a named
> instance. A lot of the select statements uses a linked server but not the
> actual update, insert, and delete statement. they're on the loval server.
> All the locks has Owner type XAct. I really cannot figure out why the
> Transactions hang. Is there any known issues about named instances and
> linked servers ?
> Anybody got a clue ?
> Regards .)
> Bobby Henningsen
>
>|||Hi John,
i caqn see that theres is an open transaction under "Current Activity" so
I'm quite sure about that. I prodfiling the T-SQL stmt starting and batch
starting. The profiler is not running on the same server. I've updated all
the statistics.
I agree with you about the select statement and it eventually would be a
distributed transaction. But still. This was working on another SQL Server
2000 (And still are. They had to move the database back). I can'tfigure out
what's the difference other than this is running as a named instance. They
wil install SP3 this week and then we'll have to see. Another strange thing
is that the select staement mentioned is on a view which does a linked
server query and it then holds an Sch-S lock on the view. So when I look
under "Curent Activity" I see the X-locks but also 6-7 Sch-S locks on the
view!
Regards :)
Bobby Henningsen
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6EF40E94-3429-484E-8B06-785BBF86D4F1@.microsoft.com...
> Hi Bobby
> Try checking DBCC OPENTRAN to see if any transactions are open. You don't
> say which events you are profiling, but have you included transactions/SQL
> Transactions? IT is not unknown for profiler to not include some logging,
> expecially on a busy server and you are profiling on the same server.
> You don't say if you have performed any maintenance on this database, you
> may want to defragment the indexes and update the statistics.
> Without seeing the actual code it is hard to comment on if it can be
> improved. You may want to see if you need the select statement before the
> update, or if you are unneccesarily wrapping select statements in
> transactions. If your select statement before the update uses a linked
> server
> this may force the transaction to be a distributed transaction.
> John
> "Bobby Henningsen" wrote:
>> Hi there,
>> maby someone know something about this kind of strange behaviour. One of
>> my
>> colleagues has made an ASP appl. using SQL 2000 and I helped him move
>> data
>> to another SQL Server 2000 . We just detached and attached and he changed
>> the connectionstring and it just worked fine... For a while. Suddenly the
>> users reported that the couldn't get a certain list of records and when I
>> checked the server there was a couple of X-locks on a table held by the
>> same
>> SPID. The locks are on key-level. I've tested it and the problem seems to
>> be
>> the Transaction handling in some way. I started the profiler and filtered
>> the trace to see
>> the SPID who made the locks and the T-SQL looked something like this :
>> Begin Transaction
>> Select...
>> Update...
>> Select..
>> Another one
>> Begin Transaction
>> Insert...
>> No commit or rollback even though my colleague tells me that he either
>> commits or rolls back his transaction in his code. But it looks like it
>> never hits the server ' Is this a known issue ?
>> Could it be a Service Pack issue. There's no SP's installed. It worked
>> fine
>> when we were running on the old server.. One difference between the old
>> server and the new server is that the new one is installed as a named
>> instance. A lot of the select statements uses a linked server but not the
>> actual update, insert, and delete statement. they're on the loval server.
>> All the locks has Owner type XAct. I really cannot figure out why the
>> Transactions hang. Is there any known issues about named instances and
>> linked servers ?
>> Anybody got a clue ?
>> Regards .)
>> Bobby Henningsen
>>
---
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at få 234 spam-mails.
Betalende brugere får ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dk|||Hi Bobby
SP3a would be a minumum requirement and if you are looking at staying with
SQL 2000 for a while you should consider SP4 + patching to 2187 (after proper
evaluation and testing!)
John
"Bobby Henningsen" wrote:
> Hi John,
> i caqn see that theres is an open transaction under "Current Activity" so
> I'm quite sure about that. I prodfiling the T-SQL stmt starting and batch
> starting. The profiler is not running on the same server. I've updated all
> the statistics.
> I agree with you about the select statement and it eventually would be a
> distributed transaction. But still. This was working on another SQL Server
> 2000 (And still are. They had to move the database back). I can'tfigure out
> what's the difference other than this is running as a named instance. They
> wil install SP3 this week and then we'll have to see. Another strange thing
> is that the select staement mentioned is on a view which does a linked
> server query and it then holds an Sch-S lock on the view. So when I look
> under "Curent Activity" I see the X-locks but also 6-7 Sch-S locks on the
> view!
> Regards :)
> Bobby Henningsen
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:6EF40E94-3429-484E-8B06-785BBF86D4F1@.microsoft.com...
> > Hi Bobby
> >
> > Try checking DBCC OPENTRAN to see if any transactions are open. You don't
> > say which events you are profiling, but have you included transactions/SQL
> > Transactions? IT is not unknown for profiler to not include some logging,
> > expecially on a busy server and you are profiling on the same server.
> >
> > You don't say if you have performed any maintenance on this database, you
> > may want to defragment the indexes and update the statistics.
> >
> > Without seeing the actual code it is hard to comment on if it can be
> > improved. You may want to see if you need the select statement before the
> > update, or if you are unneccesarily wrapping select statements in
> > transactions. If your select statement before the update uses a linked
> > server
> > this may force the transaction to be a distributed transaction.
> >
> > John
> >
> > "Bobby Henningsen" wrote:
> >
> >> Hi there,
> >> maby someone know something about this kind of strange behaviour. One of
> >> my
> >> colleagues has made an ASP appl. using SQL 2000 and I helped him move
> >> data
> >> to another SQL Server 2000 . We just detached and attached and he changed
> >> the connectionstring and it just worked fine... For a while. Suddenly the
> >> users reported that the couldn't get a certain list of records and when I
> >> checked the server there was a couple of X-locks on a table held by the
> >> same
> >> SPID. The locks are on key-level. I've tested it and the problem seems to
> >> be
> >> the Transaction handling in some way. I started the profiler and filtered
> >> the trace to see
> >> the SPID who made the locks and the T-SQL looked something like this :
> >>
> >> Begin Transaction
> >> Select...
> >> Update...
> >> Select..
> >>
> >> Another one
> >>
> >> Begin Transaction
> >> Insert...
> >>
> >> No commit or rollback even though my colleague tells me that he either
> >> commits or rolls back his transaction in his code. But it looks like it
> >> never hits the server ' Is this a known issue ?
> >> Could it be a Service Pack issue. There's no SP's installed. It worked
> >> fine
> >> when we were running on the old server.. One difference between the old
> >> server and the new server is that the new one is installed as a named
> >> instance. A lot of the select statements uses a linked server but not the
> >> actual update, insert, and delete statement. they're on the loval server.
> >> All the locks has Owner type XAct. I really cannot figure out why the
> >> Transactions hang. Is there any known issues about named instances and
> >> linked servers ?
> >>
> >> Anybody got a clue ?
> >>
> >> Regards .)
> >> Bobby Henningsen
> >>
> >>
> >>
>
> --
> ---
> Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
> Den har indtil videre sparet mig for at få 234 spam-mails.
> Betalende brugere får ikke denne besked i deres e-mails.
> Hent gratis SPAMfighter her: www.spamfighter.dk
>
>
maby someone know something about this kind of strange behaviour. One of my
colleagues has made an ASP appl. using SQL 2000 and I helped him move data
to another SQL Server 2000 . We just detached and attached and he changed
the connectionstring and it just worked fine... For a while. Suddenly the
users reported that the couldn't get a certain list of records and when I
checked the server there was a couple of X-locks on a table held by the same
SPID. The locks are on key-level. I've tested it and the problem seems to be
the Transaction handling in some way. I started the profiler and filtered
the trace to see
the SPID who made the locks and the T-SQL looked something like this :
Begin Transaction
Select...
Update...
Select..
Another one
Begin Transaction
Insert...
No commit or rollback even though my colleague tells me that he either
commits or rolls back his transaction in his code. But it looks like it
never hits the server ' Is this a known issue ?
Could it be a Service Pack issue. There's no SP's installed. It worked fine
when we were running on the old server.. One difference between the old
server and the new server is that the new one is installed as a named
instance. A lot of the select statements uses a linked server but not the
actual update, insert, and delete statement. they're on the loval server.
All the locks has Owner type XAct. I really cannot figure out why the
Transactions hang. Is there any known issues about named instances and
linked servers ?
Anybody got a clue ?
Regards .)
Bobby HenningsenHi Bobby
Try checking DBCC OPENTRAN to see if any transactions are open. You don't
say which events you are profiling, but have you included transactions/SQL
Transactions? IT is not unknown for profiler to not include some logging,
expecially on a busy server and you are profiling on the same server.
You don't say if you have performed any maintenance on this database, you
may want to defragment the indexes and update the statistics.
Without seeing the actual code it is hard to comment on if it can be
improved. You may want to see if you need the select statement before the
update, or if you are unneccesarily wrapping select statements in
transactions. If your select statement before the update uses a linked server
this may force the transaction to be a distributed transaction.
John
"Bobby Henningsen" wrote:
> Hi there,
> maby someone know something about this kind of strange behaviour. One of my
> colleagues has made an ASP appl. using SQL 2000 and I helped him move data
> to another SQL Server 2000 . We just detached and attached and he changed
> the connectionstring and it just worked fine... For a while. Suddenly the
> users reported that the couldn't get a certain list of records and when I
> checked the server there was a couple of X-locks on a table held by the same
> SPID. The locks are on key-level. I've tested it and the problem seems to be
> the Transaction handling in some way. I started the profiler and filtered
> the trace to see
> the SPID who made the locks and the T-SQL looked something like this :
> Begin Transaction
> Select...
> Update...
> Select..
> Another one
> Begin Transaction
> Insert...
> No commit or rollback even though my colleague tells me that he either
> commits or rolls back his transaction in his code. But it looks like it
> never hits the server ' Is this a known issue ?
> Could it be a Service Pack issue. There's no SP's installed. It worked fine
> when we were running on the old server.. One difference between the old
> server and the new server is that the new one is installed as a named
> instance. A lot of the select statements uses a linked server but not the
> actual update, insert, and delete statement. they're on the loval server.
> All the locks has Owner type XAct. I really cannot figure out why the
> Transactions hang. Is there any known issues about named instances and
> linked servers ?
> Anybody got a clue ?
> Regards .)
> Bobby Henningsen
>
>|||Hi John,
i caqn see that theres is an open transaction under "Current Activity" so
I'm quite sure about that. I prodfiling the T-SQL stmt starting and batch
starting. The profiler is not running on the same server. I've updated all
the statistics.
I agree with you about the select statement and it eventually would be a
distributed transaction. But still. This was working on another SQL Server
2000 (And still are. They had to move the database back). I can'tfigure out
what's the difference other than this is running as a named instance. They
wil install SP3 this week and then we'll have to see. Another strange thing
is that the select staement mentioned is on a view which does a linked
server query and it then holds an Sch-S lock on the view. So when I look
under "Curent Activity" I see the X-locks but also 6-7 Sch-S locks on the
view!
Regards :)
Bobby Henningsen
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6EF40E94-3429-484E-8B06-785BBF86D4F1@.microsoft.com...
> Hi Bobby
> Try checking DBCC OPENTRAN to see if any transactions are open. You don't
> say which events you are profiling, but have you included transactions/SQL
> Transactions? IT is not unknown for profiler to not include some logging,
> expecially on a busy server and you are profiling on the same server.
> You don't say if you have performed any maintenance on this database, you
> may want to defragment the indexes and update the statistics.
> Without seeing the actual code it is hard to comment on if it can be
> improved. You may want to see if you need the select statement before the
> update, or if you are unneccesarily wrapping select statements in
> transactions. If your select statement before the update uses a linked
> server
> this may force the transaction to be a distributed transaction.
> John
> "Bobby Henningsen" wrote:
>> Hi there,
>> maby someone know something about this kind of strange behaviour. One of
>> my
>> colleagues has made an ASP appl. using SQL 2000 and I helped him move
>> data
>> to another SQL Server 2000 . We just detached and attached and he changed
>> the connectionstring and it just worked fine... For a while. Suddenly the
>> users reported that the couldn't get a certain list of records and when I
>> checked the server there was a couple of X-locks on a table held by the
>> same
>> SPID. The locks are on key-level. I've tested it and the problem seems to
>> be
>> the Transaction handling in some way. I started the profiler and filtered
>> the trace to see
>> the SPID who made the locks and the T-SQL looked something like this :
>> Begin Transaction
>> Select...
>> Update...
>> Select..
>> Another one
>> Begin Transaction
>> Insert...
>> No commit or rollback even though my colleague tells me that he either
>> commits or rolls back his transaction in his code. But it looks like it
>> never hits the server ' Is this a known issue ?
>> Could it be a Service Pack issue. There's no SP's installed. It worked
>> fine
>> when we were running on the old server.. One difference between the old
>> server and the new server is that the new one is installed as a named
>> instance. A lot of the select statements uses a linked server but not the
>> actual update, insert, and delete statement. they're on the loval server.
>> All the locks has Owner type XAct. I really cannot figure out why the
>> Transactions hang. Is there any known issues about named instances and
>> linked servers ?
>> Anybody got a clue ?
>> Regards .)
>> Bobby Henningsen
>>
---
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at få 234 spam-mails.
Betalende brugere får ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dk|||Hi Bobby
SP3a would be a minumum requirement and if you are looking at staying with
SQL 2000 for a while you should consider SP4 + patching to 2187 (after proper
evaluation and testing!)
John
"Bobby Henningsen" wrote:
> Hi John,
> i caqn see that theres is an open transaction under "Current Activity" so
> I'm quite sure about that. I prodfiling the T-SQL stmt starting and batch
> starting. The profiler is not running on the same server. I've updated all
> the statistics.
> I agree with you about the select statement and it eventually would be a
> distributed transaction. But still. This was working on another SQL Server
> 2000 (And still are. They had to move the database back). I can'tfigure out
> what's the difference other than this is running as a named instance. They
> wil install SP3 this week and then we'll have to see. Another strange thing
> is that the select staement mentioned is on a view which does a linked
> server query and it then holds an Sch-S lock on the view. So when I look
> under "Curent Activity" I see the X-locks but also 6-7 Sch-S locks on the
> view!
> Regards :)
> Bobby Henningsen
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:6EF40E94-3429-484E-8B06-785BBF86D4F1@.microsoft.com...
> > Hi Bobby
> >
> > Try checking DBCC OPENTRAN to see if any transactions are open. You don't
> > say which events you are profiling, but have you included transactions/SQL
> > Transactions? IT is not unknown for profiler to not include some logging,
> > expecially on a busy server and you are profiling on the same server.
> >
> > You don't say if you have performed any maintenance on this database, you
> > may want to defragment the indexes and update the statistics.
> >
> > Without seeing the actual code it is hard to comment on if it can be
> > improved. You may want to see if you need the select statement before the
> > update, or if you are unneccesarily wrapping select statements in
> > transactions. If your select statement before the update uses a linked
> > server
> > this may force the transaction to be a distributed transaction.
> >
> > John
> >
> > "Bobby Henningsen" wrote:
> >
> >> Hi there,
> >> maby someone know something about this kind of strange behaviour. One of
> >> my
> >> colleagues has made an ASP appl. using SQL 2000 and I helped him move
> >> data
> >> to another SQL Server 2000 . We just detached and attached and he changed
> >> the connectionstring and it just worked fine... For a while. Suddenly the
> >> users reported that the couldn't get a certain list of records and when I
> >> checked the server there was a couple of X-locks on a table held by the
> >> same
> >> SPID. The locks are on key-level. I've tested it and the problem seems to
> >> be
> >> the Transaction handling in some way. I started the profiler and filtered
> >> the trace to see
> >> the SPID who made the locks and the T-SQL looked something like this :
> >>
> >> Begin Transaction
> >> Select...
> >> Update...
> >> Select..
> >>
> >> Another one
> >>
> >> Begin Transaction
> >> Insert...
> >>
> >> No commit or rollback even though my colleague tells me that he either
> >> commits or rolls back his transaction in his code. But it looks like it
> >> never hits the server ' Is this a known issue ?
> >> Could it be a Service Pack issue. There's no SP's installed. It worked
> >> fine
> >> when we were running on the old server.. One difference between the old
> >> server and the new server is that the new one is installed as a named
> >> instance. A lot of the select statements uses a linked server but not the
> >> actual update, insert, and delete statement. they're on the loval server.
> >> All the locks has Owner type XAct. I really cannot figure out why the
> >> Transactions hang. Is there any known issues about named instances and
> >> linked servers ?
> >>
> >> Anybody got a clue ?
> >>
> >> Regards .)
> >> Bobby Henningsen
> >>
> >>
> >>
>
> --
> ---
> Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
> Den har indtil videre sparet mig for at få 234 spam-mails.
> Betalende brugere får ikke denne besked i deres e-mails.
> Hent gratis SPAMfighter her: www.spamfighter.dk
>
>
Subscribe to:
Posts (Atom)