Showing posts with label occurs. Show all posts
Showing posts with label occurs. Show all posts

Sunday, March 11, 2012

Blocking when using MS DTC

Hi,
I have a blocking situation occurring that I am having trouble sorting out. It occurs when using MS DTC and using SSIS as the transaction co-ordinator. I suppose you might argue that this should be in the SSIS forum but as the blocking is occurring in SQL Server I thought I'd try here as well.

I have 2 data-flows in SSIS. One of them INSERTs to mytable, the other UPDATEs mytable. The UPDATE is getting blocked by the INSERT which holds a lock on the table (I can see this thru sp_lock). Now although this is from SSIS all you really need to know is:

-These 2 operations occur using seperate connections
-I am running the 2 operations under the same MS DTC transaction
-mytable has a PK on it. If I remove the PK then my observations so far suggest that the blocking problem disappears
-The default isolation level on the DTC transaction is Serializable. I have tried various others and got the same problem
-The locks are held by SPID=-2 (which, as far as I am aware, is DTC)

Also note that:
-I can put the 2 operations in the same data-flow which means they occur under the same connection

Now, I'm not too hot on transactions and DTC etc... Is there anything I can do here to stop my blocking problem? Or am I just scuppered?

Feel free to ask questions and I'll answer qwith as much detail as I can. I'd appreciate any advice anyone has in resolving this.

-Jamie

P.S. Here's the thread I posted on the SSIS forum about this: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=154834&SiteID=1

It seems as though page level locking is occurring. Does anyone have any advice as to how to go about eradicating this? I understand that row-level locking is possible now in SQL Server. In theory this would solve my problem because I am not updating records that I am inserting.

-Jamie

Blocking when using MS DTC

Hi,
I have a blocking situation occurring that I am having trouble sorting out. It occurs when using MS DTC and using SSIS as the transaction co-ordinator. I suppose you might argue that this should be in the SSIS forum but as the blocking is occurring in SQL Server I thought I'd try here as well.

I have 2 data-flows in SSIS. One of them INSERTs to mytable, the other UPDATEs mytable. The UPDATE is getting blocked by the INSERT which holds a lock on the table (I can see this thru sp_lock). Now although this is from SSIS all you really need to know is:

-These 2 operations occur using seperate connections
-I am running the 2 operations under the same MS DTC transaction
-mytable has a PK on it. If I remove the PK then my observations so far suggest that the blocking problem disappears
-The default isolation level on the DTC transaction is Serializable. I have tried various others and got the same problem
-The locks are held by SPID=-2 (which, as far as I am aware, is DTC)

Also note that:
-I can put the 2 operations in the same data-flow which means they occur under the same connection

Now, I'm not too hot on transactions and DTC etc... Is there anything I can do here to stop my blocking problem? Or am I just scuppered?

Feel free to ask questions and I'll answer qwith as much detail as I can. I'd appreciate any advice anyone has in resolving this.

-Jamie

P.S. Here's the thread I posted on the SSIS forum about this: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=154834&SiteID=1

It seems as though page level locking is occurring. Does anyone have any advice as to how to go about eradicating this? I understand that row-level locking is possible now in SQL Server. In theory this would solve my problem because I am not updating records that I am inserting.

-Jamie

Blocking Transactions

Hi Guys...

Another issue, this time regarding transactions.
It seems that blocking occurs very often when processes are being put into transactions.

For example, I have two tables, tableA and tableB.

I create a sequence container, and inside, i put in two process.
1. Execute SQL - this to insert data into tableA.
2. Data Flow Task - move data from tableA to tableB
Set the sequence container transactions to required.

When I run the package, it always get stuck at process 2.
I tried running the SP_WHO2 command, and it shows that its suspended, being blocked by id "-2".
Any idea to solve this?

Thanks.

Cheers,

Ryan TanSince the transaction is active when #2 is running, #1 probably gets X locks that prevent #2 from getting data. Looks like you either need to commit the transaction after #1 is done, or change the connection manager to allow a less severe isolation level.

Thursday, March 8, 2012

Blocking in profiler?

Is tehre a way to profile blocking that occurs on a SQL Server
Th
DedrickThere is a deadlock chain seet of events, but there is not a good and direct
way to watch the aggregate impact of blocking. You can see how long locks
take to acquire with the lock events, but these are typically too voluminous
to run on a productioin server. Alas, there is not data column for any of
the stmt completed events that will let you see the amount of time a
statement waited on locks.
However, dbcc sqlperf(waitstats) is a good source of this info. Combined
with sampling of sysprocesses and profiler data you can usually figure out
which statements are being blocked for significant periods of time
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Dedrick Winston" <anonymous@.discussions.microsoft.com> wrote in message
news:306B5F74-91F2-496B-81BC-E794F47C3B90@.microsoft.com...
> Is tehre a way to profile blocking that occurs on a SQL Server?
> Thx
> Dedrick|||To add to Brian's response, you can monitor the Profiler Locks: Lock
Acquired event for long durations. Be sure to filter for a value > 0 since
the data can be quite voluminous.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dedrick Winston" <anonymous@.discussions.microsoft.com> wrote in message
news:306B5F74-91F2-496B-81BC-E794F47C3B90@.microsoft.com...
> Is tehre a way to profile blocking that occurs on a SQL Server?
> Thx
> Dedrick

Blocking in profiler?

Is tehre a way to profile blocking that occurs on a SQL Server?
Thx
Dedrick
There is a deadlock chain seet of events, but there is not a good and direct
way to watch the aggregate impact of blocking. You can see how long locks
take to acquire with the lock events, but these are typically too voluminous
to run on a productioin server. Alas, there is not data column for any of
the stmt completed events that will let you see the amount of time a
statement waited on locks.
However, dbcc sqlperf(waitstats) is a good source of this info. Combined
with sampling of sysprocesses and profiler data you can usually figure out
which statements are being blocked for significant periods of time
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Dedrick Winston" <anonymous@.discussions.microsoft.com> wrote in message
news:306B5F74-91F2-496B-81BC-E794F47C3B90@.microsoft.com...
> Is tehre a way to profile blocking that occurs on a SQL Server?
> Thx
> Dedrick
|||To add to Brian's response, you can monitor the Profiler Locks: Lock
Acquired event for long durations. Be sure to filter for a value > 0 since
the data can be quite voluminous.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dedrick Winston" <anonymous@.discussions.microsoft.com> wrote in message
news:306B5F74-91F2-496B-81BC-E794F47C3B90@.microsoft.com...
> Is tehre a way to profile blocking that occurs on a SQL Server?
> Thx
> Dedrick

Blocking in profiler?

Is tehre a way to profile blocking that occurs on a SQL Server?
Thx
DedrickThere is a deadlock chain seet of events, but there is not a good and direct
way to watch the aggregate impact of blocking. You can see how long locks
take to acquire with the lock events, but these are typically too voluminous
to run on a productioin server. Alas, there is not data column for any of
the stmt completed events that will let you see the amount of time a
statement waited on locks.
However, dbcc sqlperf(waitstats) is a good source of this info. Combined
with sampling of sysprocesses and profiler data you can usually figure out
which statements are being blocked for significant periods of time
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Dedrick Winston" <anonymous@.discussions.microsoft.com> wrote in message
news:306B5F74-91F2-496B-81BC-E794F47C3B90@.microsoft.com...
> Is tehre a way to profile blocking that occurs on a SQL Server?
> Thx
> Dedrick|||To add to Brian's response, you can monitor the Profiler Locks: Lock
Acquired event for long durations. Be sure to filter for a value > 0 since
the data can be quite voluminous.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dedrick Winston" <anonymous@.discussions.microsoft.com> wrote in message
news:306B5F74-91F2-496B-81BC-E794F47C3B90@.microsoft.com...
> Is tehre a way to profile blocking that occurs on a SQL Server?
> Thx
> Dedrick

Blocking Alert

I'm trying to set up an alert for when a block occurs on a database. I
thought I could use error 1229, but that doesn't appear to be the correct
one, as it never fires, even tho we do get an occassional block.
Is there another error number I should be using? Is it possible to do what
I'm trying to do? Ideally, I would like an email to be sent when a block
occurs, so I can analyze the situation and make necessary changes.
Thanks for any advice,
TomT
Tom,
Are you refering to a database lock or a lock on an object in the database
that might be causing blocking? Might want to check out the SQL Server
Agent - Performance Condition Alerts - SQL Server:Locks.
HTH
Jerry
"TomT" <tomt@.newsgroup.nospam> wrote in message
news:581B2CA0-D62A-4DF0-BC06-8E597404D971@.microsoft.com...
> I'm trying to set up an alert for when a block occurs on a database. I
> thought I could use error 1229, but that doesn't appear to be the correct
> one, as it never fires, even tho we do get an occassional block.
> Is there another error number I should be using? Is it possible to do what
> I'm trying to do? Ideally, I would like an email to be sent when a block
> occurs, so I can analyze the situation and make necessary changes.
> Thanks for any advice,
> TomT
|||Jerry,
Thanks for your response. I'm talking about a block that shows up in EM,
under Current Activity\Process Info. There's a column for Blocking and
Blocked By.
"Jerry Spivey" wrote:

> Tom,
> Are you refering to a database lock or a lock on an object in the database
> that might be causing blocking? Might want to check out the SQL Server
> Agent - Performance Condition Alerts - SQL Server:Locks.
> HTH
> Jerry
> "TomT" <tomt@.newsgroup.nospam> wrote in message
> news:581B2CA0-D62A-4DF0-BC06-8E597404D971@.microsoft.com...
>
>
|||Check the following Knowledge Base article for a sample
script on setting this up:
How to monitor SQL Server 2000 blocking
http://support.microsoft.com/?id=271509
-Sue
On Wed, 14 Sep 2005 15:08:04 -0700, "TomT"
<tomt@.newsgroup.nospam> wrote:

>I'm trying to set up an alert for when a block occurs on a database. I
>thought I could use error 1229, but that doesn't appear to be the correct
>one, as it never fires, even tho we do get an occassional block.
>Is there another error number I should be using? Is it possible to do what
>I'm trying to do? Ideally, I would like an email to be sent when a block
>occurs, so I can analyze the situation and make necessary changes.
>Thanks for any advice,
>TomT
|||Sue,
thanks very much for the link. Out of curiosity, is it possible to also set
an alert for this? Is there to your knowledge a specific error number for
this event?
thanks,
Tom
"Sue Hoegemeier" wrote:

> Check the following Knowledge Base article for a sample
> script on setting this up:
> How to monitor SQL Server 2000 blocking
> http://support.microsoft.com/?id=271509
> -Sue
> On Wed, 14 Sep 2005 15:08:04 -0700, "TomT"
> <tomt@.newsgroup.nospam> wrote:
>
>
|||TomT wrote:
> Sue,
> thanks very much for the link. Out of curiosity, is it possible to
> also set an alert for this? Is there to your knowledge a specific
> error number for this event?
Blocking is not really an error condition. It's a normal part of RDBMS
operation. You are always going to have blocking going on in some
fashion. In a healthly system, the blocks are so short they are never
noticed. On a system with problems these blocks are more pronounced. The
blocking scipts Sue mentioned will help you determine when extended
blocks are occurring in the database.
You can set a lock timeout in you application if you want. With a lock
timeout, the application will only wait for a specific maximum time when
blocked before cancelling the query. A cancelled query throws an
attention event, which can be captured with Profiler or using a
server-side trace.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Hi Tom,
Since many communities and MVP provide great answers, I wanted to post a
quick note to see if you would like additional assistance or information
regarding this particular issue. We appreciate your patience and look
forward to hearing from you!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Blocking Alert

I'm trying to set up an alert for when a block occurs on a database. I
thought I could use error 1229, but that doesn't appear to be the correct
one, as it never fires, even tho we do get an occassional block.
Is there another error number I should be using? Is it possible to do what
I'm trying to do? Ideally, I would like an email to be sent when a block
occurs, so I can analyze the situation and make necessary changes.
Thanks for any advice,
TomTTom,
Are you refering to a database lock or a lock on an object in the database
that might be causing blocking? Might want to check out the SQL Server
Agent - Performance Condition Alerts - SQL Server:Locks.
HTH
Jerry
"TomT" <tomt@.newsgroup.nospam> wrote in message
news:581B2CA0-D62A-4DF0-BC06-8E597404D971@.microsoft.com...
> I'm trying to set up an alert for when a block occurs on a database. I
> thought I could use error 1229, but that doesn't appear to be the correct
> one, as it never fires, even tho we do get an occassional block.
> Is there another error number I should be using? Is it possible to do what
> I'm trying to do? Ideally, I would like an email to be sent when a block
> occurs, so I can analyze the situation and make necessary changes.
> Thanks for any advice,
> TomT|||Jerry,
Thanks for your response. I'm talking about a block that shows up in EM,
under Current Activity\Process Info. There's a column for Blocking and
Blocked By.
"Jerry Spivey" wrote:

> Tom,
> Are you refering to a database lock or a lock on an object in the database
> that might be causing blocking? Might want to check out the SQL Server
> Agent - Performance Condition Alerts - SQL Server:Locks.
> HTH
> Jerry
> "TomT" <tomt@.newsgroup.nospam> wrote in message
> news:581B2CA0-D62A-4DF0-BC06-8E597404D971@.microsoft.com...
>
>|||Check the following Knowledge Base article for a sample
script on setting this up:
How to monitor SQL Server 2000 blocking
http://support.microsoft.com/?id=271509
-Sue
On Wed, 14 Sep 2005 15:08:04 -0700, "TomT"
<tomt@.newsgroup.nospam> wrote:

>I'm trying to set up an alert for when a block occurs on a database. I
>thought I could use error 1229, but that doesn't appear to be the correct
>one, as it never fires, even tho we do get an occassional block.
>Is there another error number I should be using? Is it possible to do what
>I'm trying to do? Ideally, I would like an email to be sent when a block
>occurs, so I can analyze the situation and make necessary changes.
>Thanks for any advice,
>TomT|||Sue,
thanks very much for the link. Out of curiosity, is it possible to also set
an alert for this? Is there to your knowledge a specific error number for
this event?
thanks,
Tom
"Sue Hoegemeier" wrote:

> Check the following Knowledge Base article for a sample
> script on setting this up:
> How to monitor SQL Server 2000 blocking
> http://support.microsoft.com/?id=271509
> -Sue
> On Wed, 14 Sep 2005 15:08:04 -0700, "TomT"
> <tomt@.newsgroup.nospam> wrote:
>
>|||TomT wrote:
> Sue,
> thanks very much for the link. Out of curiosity, is it possible to
> also set an alert for this? Is there to your knowledge a specific
> error number for this event?
Blocking is not really an error condition. It's a normal part of RDBMS
operation. You are always going to have blocking going on in some
fashion. In a healthly system, the blocks are so short they are never
noticed. On a system with problems these blocks are more pronounced. The
blocking scipts Sue mentioned will help you determine when extended
blocks are occurring in the database.
You can set a lock timeout in you application if you want. With a lock
timeout, the application will only wait for a specific maximum time when
blocked before cancelling the query. A cancelled query throws an
attention event, which can be captured with Profiler or using a
server-side trace.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi Tom,
Since many communities and MVP provide great answers, I wanted to post a
quick note to see if you would like additional assistance or information
regarding this particular issue. We appreciate your patience and look
forward to hearing from you!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Blocking Alert

I'm trying to set up an alert for when a block occurs on a database. I
thought I could use error 1229, but that doesn't appear to be the correct
one, as it never fires, even tho we do get an occassional block.
Is there another error number I should be using? Is it possible to do what
I'm trying to do? Ideally, I would like an email to be sent when a block
occurs, so I can analyze the situation and make necessary changes.
Thanks for any advice,
TomTTom,
Are you refering to a database lock or a lock on an object in the database
that might be causing blocking? Might want to check out the SQL Server
Agent - Performance Condition Alerts - SQL Server:Locks.
HTH
Jerry
"TomT" <tomt@.newsgroup.nospam> wrote in message
news:581B2CA0-D62A-4DF0-BC06-8E597404D971@.microsoft.com...
> I'm trying to set up an alert for when a block occurs on a database. I
> thought I could use error 1229, but that doesn't appear to be the correct
> one, as it never fires, even tho we do get an occassional block.
> Is there another error number I should be using? Is it possible to do what
> I'm trying to do? Ideally, I would like an email to be sent when a block
> occurs, so I can analyze the situation and make necessary changes.
> Thanks for any advice,
> TomT|||Jerry,
Thanks for your response. I'm talking about a block that shows up in EM,
under Current Activity\Process Info. There's a column for Blocking and
Blocked By.
"Jerry Spivey" wrote:
> Tom,
> Are you refering to a database lock or a lock on an object in the database
> that might be causing blocking? Might want to check out the SQL Server
> Agent - Performance Condition Alerts - SQL Server:Locks.
> HTH
> Jerry
> "TomT" <tomt@.newsgroup.nospam> wrote in message
> news:581B2CA0-D62A-4DF0-BC06-8E597404D971@.microsoft.com...
> > I'm trying to set up an alert for when a block occurs on a database. I
> > thought I could use error 1229, but that doesn't appear to be the correct
> > one, as it never fires, even tho we do get an occassional block.
> >
> > Is there another error number I should be using? Is it possible to do what
> > I'm trying to do? Ideally, I would like an email to be sent when a block
> > occurs, so I can analyze the situation and make necessary changes.
> >
> > Thanks for any advice,
> >
> > TomT
>
>|||Check the following Knowledge Base article for a sample
script on setting this up:
How to monitor SQL Server 2000 blocking
http://support.microsoft.com/?id=271509
-Sue
On Wed, 14 Sep 2005 15:08:04 -0700, "TomT"
<tomt@.newsgroup.nospam> wrote:
>I'm trying to set up an alert for when a block occurs on a database. I
>thought I could use error 1229, but that doesn't appear to be the correct
>one, as it never fires, even tho we do get an occassional block.
>Is there another error number I should be using? Is it possible to do what
>I'm trying to do? Ideally, I would like an email to be sent when a block
>occurs, so I can analyze the situation and make necessary changes.
>Thanks for any advice,
>TomT|||Sue,
thanks very much for the link. Out of curiosity, is it possible to also set
an alert for this? Is there to your knowledge a specific error number for
this event?
thanks,
Tom
"Sue Hoegemeier" wrote:
> Check the following Knowledge Base article for a sample
> script on setting this up:
> How to monitor SQL Server 2000 blocking
> http://support.microsoft.com/?id=271509
> -Sue
> On Wed, 14 Sep 2005 15:08:04 -0700, "TomT"
> <tomt@.newsgroup.nospam> wrote:
> >I'm trying to set up an alert for when a block occurs on a database. I
> >thought I could use error 1229, but that doesn't appear to be the correct
> >one, as it never fires, even tho we do get an occassional block.
> >
> >Is there another error number I should be using? Is it possible to do what
> >I'm trying to do? Ideally, I would like an email to be sent when a block
> >occurs, so I can analyze the situation and make necessary changes.
> >
> >Thanks for any advice,
> >
> >TomT
>|||TomT wrote:
> Sue,
> thanks very much for the link. Out of curiosity, is it possible to
> also set an alert for this? Is there to your knowledge a specific
> error number for this event?
Blocking is not really an error condition. It's a normal part of RDBMS
operation. You are always going to have blocking going on in some
fashion. In a healthly system, the blocks are so short they are never
noticed. On a system with problems these blocks are more pronounced. The
blocking scipts Sue mentioned will help you determine when extended
blocks are occurring in the database.
You can set a lock timeout in you application if you want. With a lock
timeout, the application will only wait for a specific maximum time when
blocked before cancelling the query. A cancelled query throws an
attention event, which can be captured with Profiler or using a
server-side trace.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi Tom,
Since many communities and MVP provide great answers, I wanted to post a
quick note to see if you would like additional assistance or information
regarding this particular issue. We appreciate your patience and look
forward to hearing from you!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Thursday, February 16, 2012

blank page between the main report and a subreport when exporting to PDF.

When I create a report that has imbedded subreports there are occasions
when I get a blank page between the main report and a subreport, this
occurs when exporting to PDF. From what I have observed; if a
subreport contains more rows of data than can fit on the same page as
the main report then a page break occurs a blank page is inserted and
then the subreport comes out on the subsequent page. This behavior will
not be repeated until the same condition occurs again.When the result
sets from the main report and subreport can fit on one page there is no
problem in exporting to PDF. In HTML format I get the report as
expected with no blank pages..PDF rendering is picky. You have to make sure that the body width does not
exceed, not even by 1 pixel, the (page width - (left margin + right
margin)).
--
Adrian M.
MCP
"Tom" <tom_barnard@.aotx.uscourts.gov> wrote in message
news:1112137446.997763.245800@.l41g2000cwc.googlegroups.com...
> When I create a report that has imbedded subreports there are occasions
> when I get a blank page between the main report and a subreport, this
> occurs when exporting to PDF. From what I have observed; if a
> subreport contains more rows of data than can fit on the same page as
> the main report then a page break occurs a blank page is inserted and
> then the subreport comes out on the subsequent page. This behavior will
> not be repeated until the same condition occurs again.When the result
> sets from the main report and subreport can fit on one page there is no
> problem in exporting to PDF. In HTML format I get the report as
> expected with no blank pages..
>|||Adrian,
We have checked the body width and found no problem there. Could
something else be causing this?
Thanks,
Tom|||This same situation happened to me. I found that two of the subreports had
matrices in them with extra white space to the right of the matrix. The extra
pages were eliminated when I removed this white space.
I hope this helps!
-Jason
"Tom" wrote:
> When I create a report that has imbedded subreports there are occasions
> when I get a blank page between the main report and a subreport, this
> occurs when exporting to PDF. From what I have observed; if a
> subreport contains more rows of data than can fit on the same page as
> the main report then a page break occurs a blank page is inserted and
> then the subreport comes out on the subsequent page. This behavior will
> not be repeated until the same condition occurs again.When the result
> sets from the main report and subreport can fit on one page there is no
> problem in exporting to PDF. In HTML format I get the report as
> expected with no blank pages..
>