Destination runs out of space => job never finishes

Hi,

I have developed a SSIS package that performs data cleansing before data is loaded into a DW. I'm using a Multicast transformation to load the cleansed data to both a production and a test environment.

A couple of time now, I have exprienced that the test environment runs out of disk space and can not grow the database file (I know - this should never happen, but tell that to the DBA :o).

For some reason this causes the package to hang - no error is returned and the job executing the package remains in "Executing job" status, meaning that the cube processing is never started. Shouldn't I get an error back when the disk runs out of space?

Regards,
SuneYes, you should. However, we rely on the provider returning the error from the SQL Server. If it doesn't then we have no way of knowing an error occurred.

Thanks,
Matt

Design Slow

I'm building a report with dozens of grids and around 8 columns per grid with
3-4 groups each. The report runs fine (slow because of the amount of data
but that's expected). When I am in the report designer editing the report,
simply trying to widen a column, change a label, add a text box or move a
grid is UNBELIEVABLY slow.
Can this be fixed? My guess is Visual Studio is "memorizing" the action
being performed as a type of macro to enable "undos" and based on the volume
of grids, text boxes, etc... it take a long time to do this.How much RAM do you have on your PC?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:8673FAFC-37CF-452B-B916-7F5917EDF506@.microsoft.com...
> I'm building a report with dozens of grids and around 8 columns per grid
> with
> 3-4 groups each. The report runs fine (slow because of the amount of data
> but that's expected). When I am in the report designer editing the
> report,
> simply trying to widen a column, change a label, add a text box or move a
> grid is UNBELIEVABLY slow.
> Can this be fixed? My guess is Visual Studio is "memorizing" the action
> being performed as a type of macro to enable "undos" and based on the
> volume
> of grids, text boxes, etc... it take a long time to do this.|||2 gigs. I'm maxed out and it shows a gig free. DEVENV shows using about
132mb when working on just this .rdl report.
"Bruce L-C [MVP]" wrote:
> How much RAM do you have on your PC?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:8673FAFC-37CF-452B-B916-7F5917EDF506@.microsoft.com...
> > I'm building a report with dozens of grids and around 8 columns per grid
> > with
> > 3-4 groups each. The report runs fine (slow because of the amount of data
> > but that's expected). When I am in the report designer editing the
> > report,
> > simply trying to widen a column, change a label, add a text box or move a
> > grid is UNBELIEVABLY slow.
> >
> > Can this be fixed? My guess is Visual Studio is "memorizing" the action
> > being performed as a type of macro to enable "undos" and based on the
> > volume
> > of grids, text boxes, etc... it take a long time to do this.
>
>|||My guess is that you are hitting some sort of resource problem (not a ram
problem however).
Dozens of grids is pretty unusual. One thing you might consider is that
instead of having all these grids on one report is to have a report with
multiple subreports. I hide the subreports in listview so the user does not
see it (that is a property of the report you can get to with report
manager). This enables you to test each subreport independently. Then you
drag and drop subreport onto the main report, hook up the parameters and
away you go.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:BEC46453-4262-442D-BC5A-0235C7DB5890@.microsoft.com...
>2 gigs. I'm maxed out and it shows a gig free. DEVENV shows using about
> 132mb when working on just this .rdl report.
> "Bruce L-C [MVP]" wrote:
>> How much RAM do you have on your PC?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> news:8673FAFC-37CF-452B-B916-7F5917EDF506@.microsoft.com...
>> > I'm building a report with dozens of grids and around 8 columns per
>> > grid
>> > with
>> > 3-4 groups each. The report runs fine (slow because of the amount of
>> > data
>> > but that's expected). When I am in the report designer editing the
>> > report,
>> > simply trying to widen a column, change a label, add a text box or move
>> > a
>> > grid is UNBELIEVABLY slow.
>> >
>> > Can this be fixed? My guess is Visual Studio is "memorizing" the
>> > action
>> > being performed as a type of macro to enable "undos" and based on the
>> > volume
>> > of grids, text boxes, etc... it take a long time to do this.
>>|||Ok, I'm expirementing with the sub report route and design seems faster if I
can work out my layout issues. Each subreport will be calling the same 4
store procs, each of which is a MONSTER. So, my question is if I use
subreports, am I hammering the SQL Server more by calling each ofthese 4
procs "repetitively" for each sub report, rather than a single call to each
proc from a single monster report with dozens of grids.
"Bruce L-C [MVP]" wrote:
> My guess is that you are hitting some sort of resource problem (not a ram
> problem however).
> Dozens of grids is pretty unusual. One thing you might consider is that
> instead of having all these grids on one report is to have a report with
> multiple subreports. I hide the subreports in listview so the user does not
> see it (that is a property of the report you can get to with report
> manager). This enables you to test each subreport independently. Then you
> drag and drop subreport onto the main report, hook up the parameters and
> away you go.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:BEC46453-4262-442D-BC5A-0235C7DB5890@.microsoft.com...
> >2 gigs. I'm maxed out and it shows a gig free. DEVENV shows using about
> > 132mb when working on just this .rdl report.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> How much RAM do you have on your PC?
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> >> news:8673FAFC-37CF-452B-B916-7F5917EDF506@.microsoft.com...
> >> > I'm building a report with dozens of grids and around 8 columns per
> >> > grid
> >> > with
> >> > 3-4 groups each. The report runs fine (slow because of the amount of
> >> > data
> >> > but that's expected). When I am in the report designer editing the
> >> > report,
> >> > simply trying to widen a column, change a label, add a text box or move
> >> > a
> >> > grid is UNBELIEVABLY slow.
> >> >
> >> > Can this be fixed? My guess is Visual Studio is "memorizing" the
> >> > action
> >> > being performed as a type of macro to enable "undos" and based on the
> >> > volume
> >> > of grids, text boxes, etc... it take a long time to do this.
> >>
> >>
> >>
>
>|||Hmmm, so your grids are tied to the same dataset(s). I.e. in the monster
report you have only 4 datasets for all the grids?
Well, then the answer is yes you will be calling it once for each subreport.
If it would work for you to have 4 subreports where each subreport has all
the grids for one dataset. Then you main report does nothing other than host
the four subreports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:1F36FDE2-0CA5-4017-BA68-CF385753A3C0@.microsoft.com...
> Ok, I'm expirementing with the sub report route and design seems faster if
> I
> can work out my layout issues. Each subreport will be calling the same 4
> store procs, each of which is a MONSTER. So, my question is if I use
> subreports, am I hammering the SQL Server more by calling each ofthese 4
> procs "repetitively" for each sub report, rather than a single call to
> each
> proc from a single monster report with dozens of grids.
> "Bruce L-C [MVP]" wrote:
>> My guess is that you are hitting some sort of resource problem (not a ram
>> problem however).
>> Dozens of grids is pretty unusual. One thing you might consider is that
>> instead of having all these grids on one report is to have a report with
>> multiple subreports. I hide the subreports in listview so the user does
>> not
>> see it (that is a property of the report you can get to with report
>> manager). This enables you to test each subreport independently. Then you
>> drag and drop subreport onto the main report, hook up the parameters and
>> away you go.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> news:BEC46453-4262-442D-BC5A-0235C7DB5890@.microsoft.com...
>> >2 gigs. I'm maxed out and it shows a gig free. DEVENV shows using
>> >about
>> > 132mb when working on just this .rdl report.
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> How much RAM do you have on your PC?
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> >> news:8673FAFC-37CF-452B-B916-7F5917EDF506@.microsoft.com...
>> >> > I'm building a report with dozens of grids and around 8 columns per
>> >> > grid
>> >> > with
>> >> > 3-4 groups each. The report runs fine (slow because of the amount
>> >> > of
>> >> > data
>> >> > but that's expected). When I am in the report designer editing the
>> >> > report,
>> >> > simply trying to widen a column, change a label, add a text box or
>> >> > move
>> >> > a
>> >> > grid is UNBELIEVABLY slow.
>> >> >
>> >> > Can this be fixed? My guess is Visual Studio is "memorizing" the
>> >> > action
>> >> > being performed as a type of macro to enable "undos" and based on
>> >> > the
>> >> > volume
>> >> > of grids, text boxes, etc... it take a long time to do this.
>> >>
>> >>
>> >>
>>|||Yes, I did some profiling and unfortunately, based on the complexity of the
underlying stored procs, I don't think I can justify calling them each
multiple times in production just speed design/development. I will try to
achieve something similar to what you are saying by trying to use a List
Boxes for a repeated groups...this may cut down on the number of grids and
help me.
Thanks for all your input.
"Bruce L-C [MVP]" wrote:
> Hmmm, so your grids are tied to the same dataset(s). I.e. in the monster
> report you have only 4 datasets for all the grids?
> Well, then the answer is yes you will be calling it once for each subreport.
> If it would work for you to have 4 subreports where each subreport has all
> the grids for one dataset. Then you main report does nothing other than host
> the four subreports.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:1F36FDE2-0CA5-4017-BA68-CF385753A3C0@.microsoft.com...
> > Ok, I'm expirementing with the sub report route and design seems faster if
> > I
> > can work out my layout issues. Each subreport will be calling the same 4
> > store procs, each of which is a MONSTER. So, my question is if I use
> > subreports, am I hammering the SQL Server more by calling each ofthese 4
> > procs "repetitively" for each sub report, rather than a single call to
> > each
> > proc from a single monster report with dozens of grids.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> My guess is that you are hitting some sort of resource problem (not a ram
> >> problem however).
> >>
> >> Dozens of grids is pretty unusual. One thing you might consider is that
> >> instead of having all these grids on one report is to have a report with
> >> multiple subreports. I hide the subreports in listview so the user does
> >> not
> >> see it (that is a property of the report you can get to with report
> >> manager). This enables you to test each subreport independently. Then you
> >> drag and drop subreport onto the main report, hook up the parameters and
> >> away you go.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> >> news:BEC46453-4262-442D-BC5A-0235C7DB5890@.microsoft.com...
> >> >2 gigs. I'm maxed out and it shows a gig free. DEVENV shows using
> >> >about
> >> > 132mb when working on just this .rdl report.
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> >> How much RAM do you have on your PC?
> >> >>
> >> >>
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> >> >> news:8673FAFC-37CF-452B-B916-7F5917EDF506@.microsoft.com...
> >> >> > I'm building a report with dozens of grids and around 8 columns per
> >> >> > grid
> >> >> > with
> >> >> > 3-4 groups each. The report runs fine (slow because of the amount
> >> >> > of
> >> >> > data
> >> >> > but that's expected). When I am in the report designer editing the
> >> >> > report,
> >> >> > simply trying to widen a column, change a label, add a text box or
> >> >> > move
> >> >> > a
> >> >> > grid is UNBELIEVABLY slow.
> >> >> >
> >> >> > Can this be fixed? My guess is Visual Studio is "memorizing" the
> >> >> > action
> >> >> > being performed as a type of macro to enable "undos" and based on
> >> >> > the
> >> >> > volume
> >> >> > of grids, text boxes, etc... it take a long time to do this.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

Design flaw - Too slow lock escalation

Hi!
I seem to have a problem opposite to everyone else.
Lock escalation does not occur.
My client runs a DTS-job with some 30 steps every night.
Randomly some of the the steps fail with:
Error 1204 , Severity Level 19 , Message Text ,The SQL Server cannot obtain
a LOCK resource at this time.
When the failing steps are rerun at daytime they "never" fails on 1204.
I have been trying to pin-point a locking culprit for some time but haven't
found any.
On Friday night I followed the DTS-job closely, running sp_who2 and sp_lock
at short intervals
and also had a perfmon running. When the 1204 at last occured after midnight
I found the following:
The transaction is a
Insert into BIG_TABLE
select * from WORK_Table
Most indexes, but not all are dropped from BIG_Table and later recreated.
Sp_who2 showed that the insert was parallelled in 5 threads, (6 cpu
server)
and within a few seconds these 5 threads had created +1GB of Key locks, and
shortly Error 1204 occured and the transaction was rollbacked.
The DTS-job was the only active transaction in the server so there was no
competition for resources.
My theory is that the lock monitor, (that I presume handles lock
escalation), sleeps too long sometimes,
and fails to do the lock escalation. In this case the server is quite
powerful and has good fibre access the SAN-disk,
and when CPU and I-O is freely available at nighttime lock consumimg is too
fast. At daytime when there is competition for both CPU and I-O,
the lock-creating process is not fast enough to eat all available memory.
Please enlighten me if I'm wrong.
We have circumvented the problem by adding a WITH (TABLOCK) to the insert
statement,
but this is undesirable, if we for other reasons have to rerun a jobstep at
daytime.
Is this a known problem or as designed?
Should I go into the tedious process of raising a support case with MS?
By the way:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
\dg
Dan van Ginhoven
Sweden
Is this an all or nothing process? By this I mean that can it be done in
smaller batches of say 10K rows at a time? Usually this can be done and
will limit the number of locks to ~10K. If not then a Table level lock may
be best. The most likely reason why the lock is not escalating to a tablock
is due to another lock on that table at some level. Even a single shared
row lock will stop the escalation process. So if users are selecting from
the table it may not escalate on it's own unless there is a lull in the
selects.
Andrew J. Kelly SQL MVP
"Dan van Ginhoven" <danfan46@.hotmail.com> wrote in message
news:shR6f.148931$dP1.506911@.newsc.telia.net...
> Hi!
> I seem to have a problem opposite to everyone else.
> Lock escalation does not occur.
> My client runs a DTS-job with some 30 steps every night.
> Randomly some of the the steps fail with:
> Error 1204 , Severity Level 19 , Message Text ,The SQL Server cannot
> obtain
> a LOCK resource at this time.
> When the failing steps are rerun at daytime they "never" fails on 1204.
> I have been trying to pin-point a locking culprit for some time but
> haven't
> found any.
> On Friday night I followed the DTS-job closely, running sp_who2 and
> sp_lock
> at short intervals
> and also had a perfmon running. When the 1204 at last occured after
> midnight
> I found the following:
> The transaction is a
> Insert into BIG_TABLE
> select * from WORK_Table
> Most indexes, but not all are dropped from BIG_Table and later recreated.
> Sp_who2 showed that the insert was parallelled in 5 threads, (6 cpu
> server)
> and within a few seconds these 5 threads had created +1GB of Key locks,
> and
> shortly Error 1204 occured and the transaction was rollbacked.
> The DTS-job was the only active transaction in the server so there was no
> competition for resources.
> My theory is that the lock monitor, (that I presume handles lock
> escalation), sleeps too long sometimes,
> and fails to do the lock escalation. In this case the server is quite
> powerful and has good fibre access the SAN-disk,
> and when CPU and I-O is freely available at nighttime lock consumimg is
> too
> fast. At daytime when there is competition for both CPU and I-O,
> the lock-creating process is not fast enough to eat all available memory.
> Please enlighten me if I'm wrong.
> We have circumvented the problem by adding a WITH (TABLOCK) to the
> insert
> statement,
> but this is undesirable, if we for other reasons have to rerun a jobstep
> at
> daytime.
> Is this a known problem or as designed?
> Should I go into the tedious process of raising a support case with MS?
> By the way:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
> \dg
> Dan van Ginhoven
> Sweden
>

Design flaw - Too slow lock escalation

Hi!
I seem to have a problem opposite to everyone else.
Lock escalation does not occur.
My client runs a DTS-job with some 30 steps every night.
Randomly some of the the steps fail with:
Error 1204 , Severity Level 19 , Message Text ,The SQL Server cannot obtain
a LOCK resource at this time.
When the failing steps are rerun at daytime they "never" fails on 1204.
I have been trying to pin-point a locking culprit for some time but haven't
found any.
On Friday night I followed the DTS-job closely, running sp_who2 and sp_lock
at short intervals
and also had a perfmon running. When the 1204 at last occured after midnight
I found the following:
The transaction is a
Insert into BIG_TABLE
select * from WORK_Table
Most indexes, but not all are dropped from BIG_Table and later recreated.
Sp_who2 showed that the insert was parallelled in 5 threads, (6 cpu
server)
and within a few seconds these 5 threads had created +1GB of Key locks, and
shortly Error 1204 occured and the transaction was rollbacked.
The DTS-job was the only active transaction in the server so there was no
competition for resources.
My theory is that the lock monitor, (that I presume handles lock
escalation), sleeps too long sometimes,
and fails to do the lock escalation. In this case the server is quite
powerful and has good fibre access the SAN-disk,
and when CPU and I-O is freely available at nighttime lock consumimg is too
fast. At daytime when there is competition for both CPU and I-O,
the lock-creating process is not fast enough to eat all available memory.
Please enlighten me if I'm wrong.
We have circumvented the problem by adding a WITH (TABLOCK) to the insert
statement,
but this is undesirable, if we for other reasons have to rerun a jobstep at
daytime.
Is this a known problem or as designed?
Should I go into the tedious process of raising a support case with MS?
By the way:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
\dg
---
Dan van Ginhoven
SwedenIs this an all or nothing process? By this I mean that can it be done in
smaller batches of say 10K rows at a time? Usually this can be done and
will limit the number of locks to ~10K. If not then a Table level lock may
be best. The most likely reason why the lock is not escalating to a tablock
is due to another lock on that table at some level. Even a single shared
row lock will stop the escalation process. So if users are selecting from
the table it may not escalate on it's own unless there is a lull in the
selects.
Andrew J. Kelly SQL MVP
"Dan van Ginhoven" <danfan46@.hotmail.com> wrote in message
news:shR6f.148931$dP1.506911@.newsc.telia.net...
> Hi!
> I seem to have a problem opposite to everyone else.
> Lock escalation does not occur.
> My client runs a DTS-job with some 30 steps every night.
> Randomly some of the the steps fail with:
> Error 1204 , Severity Level 19 , Message Text ,The SQL Server cannot
> obtain
> a LOCK resource at this time.
> When the failing steps are rerun at daytime they "never" fails on 1204.
> I have been trying to pin-point a locking culprit for some time but
> haven't
> found any.
> On Friday night I followed the DTS-job closely, running sp_who2 and
> sp_lock
> at short intervals
> and also had a perfmon running. When the 1204 at last occured after
> midnight
> I found the following:
> The transaction is a
> Insert into BIG_TABLE
> select * from WORK_Table
> Most indexes, but not all are dropped from BIG_Table and later recreated.
> Sp_who2 showed that the insert was parallelled in 5 threads, (6 cpu
> server)
> and within a few seconds these 5 threads had created +1GB of Key locks,
> and
> shortly Error 1204 occured and the transaction was rollbacked.
> The DTS-job was the only active transaction in the server so there was no
> competition for resources.
> My theory is that the lock monitor, (that I presume handles lock
> escalation), sleeps too long sometimes,
> and fails to do the lock escalation. In this case the server is quite
> powerful and has good fibre access the SAN-disk,
> and when CPU and I-O is freely available at nighttime lock consumimg is
> too
> fast. At daytime when there is competition for both CPU and I-O,
> the lock-creating process is not fast enough to eat all available memory.
> Please enlighten me if I'm wrong.
> We have circumvented the problem by adding a WITH (TABLOCK) to the
> insert
> statement,
> but this is undesirable, if we for other reasons have to rerun a jobstep
> at
> daytime.
> Is this a known problem or as designed?
> Should I go into the tedious process of raising a support case with MS?
> By the way:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
> \dg
> ---
> Dan van Ginhoven
> Sweden
>

Design flaw - Too slow lock escalation

Hi!
I seem to have a problem opposite to everyone else.
Lock escalation does not occur.
My client runs a DTS-job with some 30 steps every night.
Randomly some of the the steps fail with:
Error 1204 , Severity Level 19 , Message Text ,The SQL Server cannot obtain
a LOCK resource at this time.
When the failing steps are rerun at daytime they "never" fails on 1204.
I have been trying to pin-point a locking culprit for some time but haven't
found any.
On Friday night I followed the DTS-job closely, running sp_who2 and sp_lock
at short intervals
and also had a perfmon running. When the 1204 at last occured after midnight
I found the following:
The transaction is a
Insert into BIG_TABLE
select * from WORK_Table
Most indexes, but not all are dropped from BIG_Table and later recreated.
Sp_who2 showed that the insert was parallelled in 5 threads, (6 cpu
server)
and within a few seconds these 5 threads had created +1GB of Key locks, and
shortly Error 1204 occured and the transaction was rollbacked.
The DTS-job was the only active transaction in the server so there was no
competition for resources.
My theory is that the lock monitor, (that I presume handles lock
escalation), sleeps too long sometimes,
and fails to do the lock escalation. In this case the server is quite
powerful and has good fibre access the SAN-disk,
and when CPU and I-O is freely available at nighttime lock consumimg is too
fast. At daytime when there is competition for both CPU and I-O,
the lock-creating process is not fast enough to eat all available memory.
Please enlighten me if I'm wrong.
We have circumvented the problem by adding a WITH (TABLOCK) to the insert
statement,
but this is undesirable, if we for other reasons have to rerun a jobstep at
daytime.
Is this a known problem or as designed?
Should I go into the tedious process of raising a support case with MS?
By the way:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
\dg
---
Dan van Ginhoven
SwedenIs this an all or nothing process? By this I mean that can it be done in
smaller batches of say 10K rows at a time? Usually this can be done and
will limit the number of locks to ~10K. If not then a Table level lock may
be best. The most likely reason why the lock is not escalating to a tablock
is due to another lock on that table at some level. Even a single shared
row lock will stop the escalation process. So if users are selecting from
the table it may not escalate on it's own unless there is a lull in the
selects.
--
Andrew J. Kelly SQL MVP
"Dan van Ginhoven" <danfan46@.hotmail.com> wrote in message
news:shR6f.148931$dP1.506911@.newsc.telia.net...
> Hi!
> I seem to have a problem opposite to everyone else.
> Lock escalation does not occur.
> My client runs a DTS-job with some 30 steps every night.
> Randomly some of the the steps fail with:
> Error 1204 , Severity Level 19 , Message Text ,The SQL Server cannot
> obtain
> a LOCK resource at this time.
> When the failing steps are rerun at daytime they "never" fails on 1204.
> I have been trying to pin-point a locking culprit for some time but
> haven't
> found any.
> On Friday night I followed the DTS-job closely, running sp_who2 and
> sp_lock
> at short intervals
> and also had a perfmon running. When the 1204 at last occured after
> midnight
> I found the following:
> The transaction is a
> Insert into BIG_TABLE
> select * from WORK_Table
> Most indexes, but not all are dropped from BIG_Table and later recreated.
> Sp_who2 showed that the insert was parallelled in 5 threads, (6 cpu
> server)
> and within a few seconds these 5 threads had created +1GB of Key locks,
> and
> shortly Error 1204 occured and the transaction was rollbacked.
> The DTS-job was the only active transaction in the server so there was no
> competition for resources.
> My theory is that the lock monitor, (that I presume handles lock
> escalation), sleeps too long sometimes,
> and fails to do the lock escalation. In this case the server is quite
> powerful and has good fibre access the SAN-disk,
> and when CPU and I-O is freely available at nighttime lock consumimg is
> too
> fast. At daytime when there is competition for both CPU and I-O,
> the lock-creating process is not fast enough to eat all available memory.
> Please enlighten me if I'm wrong.
> We have circumvented the problem by adding a WITH (TABLOCK) to the
> insert
> statement,
> but this is undesirable, if we for other reasons have to rerun a jobstep
> at
> daytime.
> Is this a known problem or as designed?
> Should I go into the tedious process of raising a support case with MS?
> By the way:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
> \dg
> ---
> Dan van Ginhoven
> Sweden
>