mssearch.exe has high CPU usage

Hello-
Normally our mssearch.exe CPU is less than 5% even at peak times, and
falls to 0 at night. But for the last few days mssearch.exe CPU usage
remains close to 25% even when all the catalogs are idle and search
queries are very infrequent.
This happened a few months ago, and was fixed only by restarting
mssearch.exe. This time I'd like to discover what is causing the
problem. How can I figure out what mssearch.exe is doing?
SQL Server 2000 sp4 on Windows 2003 Server sp1, 4-processor, in an
active-active cluster, 3.5GB memory. 6 tables all with "Update Index in
background" option, each in their own catalog. The largest is 417MB
from 350k rows, the rest are much smaller.
Thanks for your help!
-Steve
www.vin.com
You can trace any SQL related activities of the MS Search service by running
SQL Profiler.
Perhaps - provided you're using task manager - what you see is the MS Search
service indexing your file system.
ML
http://milambda.blogspot.com/
|||Those are good suggestions, so thanks, but nothing was showing up on
profiler or perfmon that I could see, and nothing in the regular
filesystem was being indexed.
Here's how I resolved this, and it turns out that another issue I'd
been having was related to this one. There was a sql process (a
normally quick FT query) that was hanging for a couple of days and was
not responding to the KILL request. In the wee hours I restarted
mssearch.exe, the CPU dropped down to it's typical nearly nothing, and
the unkillable process finally dies. So I suppose mssearch was running
rampant and had locked up a sql process. Glad that's over but wish I
knew what caused mssearch to bork.
Cheers,
Steve
ML wrote:
> You can trace any SQL related activities of the MS Search service by running
> SQL Profiler.
> Perhaps - provided you're using task manager - what you see is the MS Search
> service indexing your file system.

msmdsrv.exe is taking 99% cpu

I've tried rebooting the server, restarting services, etc, but that only seems to help for about 5-10mins.

When the cpu usage was that high, I profiled the cube for a 3 minute period and no queries were being performed. Is there a way to narrow down what (and why) might be causing that process to hog so much CPU time and making the whole server slow?

I have seen this problem before; and I remember the solution was to apply the latest service pack for Analysis services. SP4.

Which service pack are you running?
|||

So sorry I didn't include that info.

SSAS 2005 , SP1
1.8Ghz, 1.5gb ram with around 5 users connecting.
The memory usage wasn't at all bad when this happened. The msmdsrv.exe process took about 35mb of RAM, and there was 800-950mb free, but the cpu was at its peek.

|||

This happens during quering of the cube and not the processing?

I suggest that you download the Performance Guide which is pointed to at the top post in this message group.

Are you using any real time update of the cube or is it a full MOLAP in all dimensions and in the measure groups?

Regards

Thomas Ivarsson

|||

tee_user5 wrote:

When the cpu usage was that high, I profiled the cube for a 3 minute period and no queries were being performed.

The whole server is slow when this happens, not just AS.

Right now the server is operating much better and msmdsrv is using 300mb. I'm not sure why it wasn't using more ram yesterday.

Note: sqlserver is also on this server and it's not an option to move it at this time. It is now using 600mb of ram when the server is stable.

Update:
I just saw the msmdsrv process take 900mb+, leaving the available amount of memory less than 1mb. Then what happened is that msmdsrv released the memory and then cranked on the cpu. It's now at 99%.

If I look at the event logs, there is an out of memory error logged from today (it's in there for yesterday as well)
--

Event Type: Information
Event Source: MSSQLServerOLAPService
Event Category: (289)
Event ID: 2
Date: 2/16/2007
Time: 10:12:31 AM
User: N/A
Computer: name
Description:
The system is out of memory.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp

--
I understand that it would be advisable to add more memory - but I'm not sure how quick that can be done. Also, why did the process basically give up? It had 900mb of memory, but then it dropped it all and worked down the cpu and continues to crank on it at 99%

|||

More info:

When the server acted up, it was always from the same user. I looked at their setup, and their permissions are done through windows domain groups and the 2 groups in which the user is placed put them in 2 different roles - which each have access to 1 section of a customer region. I took the person out of one of the roles and tried access again - there was no slowness on the server. Any clue as to why something like this would happen with 2 roles hooked up?

|||

I can recreate the problem by putting a test user in 2 different Cube roles - this doesn't even involve windows groups nor any measures!

Each role is for 1 region - east and central.

When I plop on the region attribute for a display, the server starts to take a dive.
Note: only the region attribute was used. I would expect 2 different regions to show. Here is the code that the cube browser creates:

CREATE SESSION
SET [sales].[{AF0CD6C2-0256-4225-AE4C-CF8C3091654D}Pivot2Axis1Set0] AS
'
{
{ [Customer].[Region].[All] },
AddCalculatedMembers([Customer].[Region].[Region].MEMBERS)
}
'

SELECT
NON EMPTY [{AF0CD6C2-0256-4225-AE4C-CF8C3091654D}Pivot2Axis1Set0]
DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS
FROM [sales]
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR

I'm going to see if SP2 helps, but please throw out any ideas. This problem is detrimental to the server.

|||

It is very hard to tell anything about your problem without knowing more about the limit of RAM on your server and how complex the cube is, regarding number of dimensions and each fact table size(number of records) in the cube.

Regards

Thomas Ivarsson

|||

That info was in my 3rd post.
There is little complexity because we have only 3 regions and I could create the problem just by looking at that attribute.

SP2 seems to have fixed the issue, but I'm needing to do more involved testing to make sure it's no longer an issue.

|||Hi

I'm having a similar problem. I have a machinne with 2 fisical processors and two logical, 4GB of memory RAM and 800 GB of free disc space. I'm describing Power Eddge Dell Server. The problem apears when a user perform a query to the cube, no matter the client tool used to make the query the server uses all it's memory and processor capacity an then pased a few minutes It aborts the operation and disconect any user. At this moment they are using Officce Web Component but I'm using the SQL's browser and I have the same problem.

I've made some test using two diferent machins. One of them with SQL Server 2005 SP1 y the other one with SQL Server 2005 SP2 and the results are 100% similar.

I really apreciate if anyone can help me. Thank you very much.
|||

does it differ any if the simpliest query is executed?

select from [cube name]

|||

It does not differ depending the complexity of the query. I get the problem, sometimes, only changing then members of a dimention, already included in the query. At this moment the case is on Microsoft Support Engeneers hands. If I receive a useful response from them I will let you know about it.

Thank you very much for your help.

|||

Hi,

I'm afraid I'm facing the same issue.

We are running a 64bit W2003 Server Enterprise with SQL2005 Enterprise Edition (64bit, SP2) on it. The System has 4 CPUs and 8GB of RAM. On the system we are running a Datawarehouse and a SSAS DB.

Now for the error: When I query an OLAP Cube (no mather if with Excel, OWC or the internal Cube Browser in VS) the client application randomly stops responding and you can see that one of the 4 CPUs is peaked on 100% and the memory consumed by SSAS is constantly increasing.

The memory consumption goes up to about 7,5 GB RAM, then the memory is paged (I can see an entry in the event log) and the memory is increasing again, now starting vom 100K Memory. The whole system also is non-respondive in these cases, you can hardly work on the system. The only sol?ution i found so far is restarting the SSAS Service.

The occurace of this error is not bound to a special query or complexity ... after about 5 minutes of "normal" analysis (drilldown, filter, ...) the problem occurs.

Is there an updated information about this issue available?

Greetings Montechristo

|||

We also have this problem.

Using a Intel Xeon QuadCore 3.4 GHz CPU with 3.5 GB ram and W2003Server, SQL2005 SP2.
SSAS and SSRS on the same server.

After a while (at least once a day, with 30 something users average a day) the msmdsrv.exe climbes to ~33% usage on each core.
It also eats up almost all available memory.

Only a restart on SSAS releases it.

During the "hangup" a "SQL server profiler"-session shows no activity.

Any idas?

|||

Since there seem to be a lot of implementations that suffer from this issue, is there any official comment/workaround/solution to this problem available?

Greetings

msmdsrv.exe is taking 99% cpu

I've tried rebooting the server, restarting services, etc, but that only seems to help for about 5-10mins.

When the cpu usage was that high, I profiled the cube for a 3 minute period and no queries were being performed. Is there a way to narrow down what (and why) might be causing that process to hog so much CPU time and making the whole server slow?

I have seen this problem before; and I remember the solution was to apply the latest service pack for Analysis services. SP4.

Which service pack are you running?
|||

So sorry I didn't include that info.

SSAS 2005 , SP1
1.8Ghz, 1.5gb ram with around 5 users connecting.
The memory usage wasn't at all bad when this happened. The msmdsrv.exe process took about 35mb of RAM, and there was 800-950mb free, but the cpu was at its peek.

|||

This happens during quering of the cube and not the processing?

I suggest that you download the Performance Guide which is pointed to at the top post in this message group.

Are you using any real time update of the cube or is it a full MOLAP in all dimensions and in the measure groups?

Regards

Thomas Ivarsson

|||

tee_user5 wrote:

When the cpu usage was that high, I profiled the cube for a 3 minute period and no queries were being performed.

The whole server is slow when this happens, not just AS.

Right now the server is operating much better and msmdsrv is using 300mb. I'm not sure why it wasn't using more ram yesterday.

Note: sqlserver is also on this server and it's not an option to move it at this time. It is now using 600mb of ram when the server is stable.

Update:
I just saw the msmdsrv process take 900mb+, leaving the available amount of memory less than 1mb. Then what happened is that msmdsrv released the memory and then cranked on the cpu. It's now at 99%.

If I look at the event logs, there is an out of memory error logged from today (it's in there for yesterday as well)
--

Event Type: Information
Event Source: MSSQLServerOLAPService
Event Category: (289)
Event ID: 2
Date: 2/16/2007
Time: 10:12:31 AM
User: N/A
Computer: name
Description:
The system is out of memory.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp

--
I understand that it would be advisable to add more memory - but I'm not sure how quick that can be done. Also, why did the process basically give up? It had 900mb of memory, but then it dropped it all and worked down the cpu and continues to crank on it at 99%

|||

More info:

When the server acted up, it was always from the same user. I looked at their setup, and their permissions are done through windows domain groups and the 2 groups in which the user is placed put them in 2 different roles - which each have access to 1 section of a customer region. I took the person out of one of the roles and tried access again - there was no slowness on the server. Any clue as to why something like this would happen with 2 roles hooked up?

|||

I can recreate the problem by putting a test user in 2 different Cube roles - this doesn't even involve windows groups nor any measures!

Each role is for 1 region - east and central.

When I plop on the region attribute for a display, the server starts to take a dive.
Note: only the region attribute was used. I would expect 2 different regions to show. Here is the code that the cube browser creates:

CREATE SESSION
SET [sales].[{AF0CD6C2-0256-4225-AE4C-CF8C3091654D}Pivot2Axis1Set0] AS
'
{
{ [Customer].[Region].[All] },
AddCalculatedMembers([Customer].[Region].[Region].MEMBERS)
}
'

SELECT
NON EMPTY [{AF0CD6C2-0256-4225-AE4C-CF8C3091654D}Pivot2Axis1Set0]
DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS
FROM [sales]
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR

I'm going to see if SP2 helps, but please throw out any ideas. This problem is detrimental to the server.

|||

It is very hard to tell anything about your problem without knowing more about the limit of RAM on your server and how complex the cube is, regarding number of dimensions and each fact table size(number of records) in the cube.

Regards

Thomas Ivarsson

|||

That info was in my 3rd post.
There is little complexity because we have only 3 regions and I could create the problem just by looking at that attribute.

SP2 seems to have fixed the issue, but I'm needing to do more involved testing to make sure it's no longer an issue.

|||Hi

I'm having a similar problem. I have a machinne with 2 fisical processors and two logical, 4GB of memory RAM and 800 GB of free disc space. I'm describing Power Eddge Dell Server. The problem apears when a user perform a query to the cube, no matter the client tool used to make the query the server uses all it's memory and processor capacity an then pased a few minutes It aborts the operation and disconect any user. At this moment they are using Officce Web Component but I'm using the SQL's browser and I have the same problem.

I've made some test using two diferent machins. One of them with SQL Server 2005 SP1 y the other one with SQL Server 2005 SP2 and the results are 100% similar.

I really apreciate if anyone can help me. Thank you very much.
|||

does it differ any if the simpliest query is executed?

select from [cube name]

|||

It does not differ depending the complexity of the query. I get the problem, sometimes, only changing then members of a dimention, already included in the query. At this moment the case is on Microsoft Support Engeneers hands. If I receive a useful response from them I will let you know about it.

Thank you very much for your help.

|||

Hi,

I'm afraid I'm facing the same issue.

We are running a 64bit W2003 Server Enterprise with SQL2005 Enterprise Edition (64bit, SP2) on it. The System has 4 CPUs and 8GB of RAM. On the system we are running a Datawarehouse and a SSAS DB.

Now for the error: When I query an OLAP Cube (no mather if with Excel, OWC or the internal Cube Browser in VS) the client application randomly stops responding and you can see that one of the 4 CPUs is peaked on 100% and the memory consumed by SSAS is constantly increasing.

The memory consumption goes up to about 7,5 GB RAM, then the memory is paged (I can see an entry in the event log) and the memory is increasing again, now starting vom 100K Memory. The whole system also is non-respondive in these cases, you can hardly work on the system. The only sol?ution i found so far is restarting the SSAS Service.

The occurace of this error is not bound to a special query or complexity ... after about 5 minutes of "normal" analysis (drilldown, filter, ...) the problem occurs.

Is there an updated information about this issue available?

Greetings Montechristo

|||

We also have this problem.

Using a Intel Xeon QuadCore 3.4 GHz CPU with 3.5 GB ram and W2003Server, SQL2005 SP2.
SSAS and SSRS on the same server.

After a while (at least once a day, with 30 something users average a day) the msmdsrv.exe climbes to ~33% usage on each core.
It also eats up almost all available memory.

Only a restart on SSAS releases it.

During the "hangup" a "SQL server profiler"-session shows no activity.

Any idas?

|||

Since there seem to be a lot of implementations that suffer from this issue, is there any official comment/workaround/solution to this problem available?

Greetings

msmdsrv.exe is taking 99% cpu

I've tried rebooting the server, restarting services, etc, but that only seems to help for about 5-10mins.

When the cpu usage was that high, I profiled the cube for a 3 minute period and no queries were being performed. Is there a way to narrow down what (and why) might be causing that process to hog so much CPU time and making the whole server slow?

I have seen this problem before; and I remember the solution was to apply the latest service pack for Analysis services. SP4.

Which service pack are you running?
|||

So sorry I didn't include that info.

SSAS 2005 , SP1
1.8Ghz, 1.5gb ram with around 5 users connecting.
The memory usage wasn't at all bad when this happened. The msmdsrv.exe process took about 35mb of RAM, and there was 800-950mb free, but the cpu was at its peek.

|||

This happens during quering of the cube and not the processing?

I suggest that you download the Performance Guide which is pointed to at the top post in this message group.

Are you using any real time update of the cube or is it a full MOLAP in all dimensions and in the measure groups?

Regards

Thomas Ivarsson

|||

tee_user5 wrote:

When the cpu usage was that high, I profiled the cube for a 3 minute period and no queries were being performed.

The whole server is slow when this happens, not just AS.

Right now the server is operating much better and msmdsrv is using 300mb. I'm not sure why it wasn't using more ram yesterday.

Note: sqlserver is also on this server and it's not an option to move it at this time. It is now using 600mb of ram when the server is stable.

Update:
I just saw the msmdsrv process take 900mb+, leaving the available amount of memory less than 1mb. Then what happened is that msmdsrv released the memory and then cranked on the cpu. It's now at 99%.

If I look at the event logs, there is an out of memory error logged from today (it's in there for yesterday as well)
--

Event Type: Information
Event Source: MSSQLServerOLAPService
Event Category: (289)
Event ID: 2
Date: 2/16/2007
Time: 10:12:31 AM
User: N/A
Computer: name
Description:
The system is out of memory.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp

--
I understand that it would be advisable to add more memory - but I'm not sure how quick that can be done. Also, why did the process basically give up? It had 900mb of memory, but then it dropped it all and worked down the cpu and continues to crank on it at 99%

|||

More info:

When the server acted up, it was always from the same user. I looked at their setup, and their permissions are done through windows domain groups and the 2 groups in which the user is placed put them in 2 different roles - which each have access to 1 section of a customer region. I took the person out of one of the roles and tried access again - there was no slowness on the server. Any clue as to why something like this would happen with 2 roles hooked up?

|||

I can recreate the problem by putting a test user in 2 different Cube roles - this doesn't even involve windows groups nor any measures!

Each role is for 1 region - east and central.

When I plop on the region attribute for a display, the server starts to take a dive.
Note: only the region attribute was used. I would expect 2 different regions to show. Here is the code that the cube browser creates:

CREATE SESSION
SET [sales].[{AF0CD6C2-0256-4225-AE4C-CF8C3091654D}Pivot2Axis1Set0] AS
'
{
{ [Customer].[Region].[All] },
AddCalculatedMembers([Customer].[Region].[Region].MEMBERS)
}
'

SELECT
NON EMPTY [{AF0CD6C2-0256-4225-AE4C-CF8C3091654D}Pivot2Axis1Set0]
DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS
FROM [sales]
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR

I'm going to see if SP2 helps, but please throw out any ideas. This problem is detrimental to the server.

|||

It is very hard to tell anything about your problem without knowing more about the limit of RAM on your server and how complex the cube is, regarding number of dimensions and each fact table size(number of records) in the cube.

Regards

Thomas Ivarsson

|||

That info was in my 3rd post.
There is little complexity because we have only 3 regions and I could create the problem just by looking at that attribute.

SP2 seems to have fixed the issue, but I'm needing to do more involved testing to make sure it's no longer an issue.

|||Hi

I'm having a similar problem. I have a machinne with 2 fisical processors and two logical, 4GB of memory RAM and 800 GB of free disc space. I'm describing Power Eddge Dell Server. The problem apears when a user perform a query to the cube, no matter the client tool used to make the query the server uses all it's memory and processor capacity an then pased a few minutes It aborts the operation and disconect any user. At this moment they are using Officce Web Component but I'm using the SQL's browser and I have the same problem.

I've made some test using two diferent machins. One of them with SQL Server 2005 SP1 y the other one with SQL Server 2005 SP2 and the results are 100% similar.

I really apreciate if anyone can help me. Thank you very much.
|||

does it differ any if the simpliest query is executed?

select from [cube name]

|||

It does not differ depending the complexity of the query. I get the problem, sometimes, only changing then members of a dimention, already included in the query. At this moment the case is on Microsoft Support Engeneers hands. If I receive a useful response from them I will let you know about it.

Thank you very much for your help.

|||

Hi,

I'm afraid I'm facing the same issue.

We are running a 64bit W2003 Server Enterprise with SQL2005 Enterprise Edition (64bit, SP2) on it. The System has 4 CPUs and 8GB of RAM. On the system we are running a Datawarehouse and a SSAS DB.

Now for the error: When I query an OLAP Cube (no mather if with Excel, OWC or the internal Cube Browser in VS) the client application randomly stops responding and you can see that one of the 4 CPUs is peaked on 100% and the memory consumed by SSAS is constantly increasing.

The memory consumption goes up to about 7,5 GB RAM, then the memory is paged (I can see an entry in the event log) and the memory is increasing again, now starting vom 100K Memory. The whole system also is non-respondive in these cases, you can hardly work on the system. The only sol?ution i found so far is restarting the SSAS Service.

The occurace of this error is not bound to a special query or complexity ... after about 5 minutes of "normal" analysis (drilldown, filter, ...) the problem occurs.

Is there an updated information about this issue available?

Greetings Montechristo

|||

We also have this problem.

Using a Intel Xeon QuadCore 3.4 GHz CPU with 3.5 GB ram and W2003Server, SQL2005 SP2.
SSAS and SSRS on the same server.

After a while (at least once a day, with 30 something users average a day) the msmdsrv.exe climbes to ~33% usage on each core.
It also eats up almost all available memory.

Only a restart on SSAS releases it.

During the "hangup" a "SQL server profiler"-session shows no activity.

Any idas?

|||

Since there seem to be a lot of implementations that suffer from this issue, is there any official comment/workaround/solution to this problem available?

Greetings

msmdsrv.exe is taking 99% cpu

I've tried rebooting the server, restarting services, etc, but that only seems to help for about 5-10mins.

When the cpu usage was that high, I profiled the cube for a 3 minute period and no queries were being performed. Is there a way to narrow down what (and why) might be causing that process to hog so much CPU time and making the whole server slow?

I have seen this problem before; and I remember the solution was to apply the latest service pack for Analysis services. SP4.

Which service pack are you running?|||

So sorry I didn't include that info.

SSAS 2005 , SP1
1.8Ghz, 1.5gb ram with around 5 users connecting.
The memory usage wasn't at all bad when this happened. The msmdsrv.exe process took about 35mb of RAM, and there was 800-950mb free, but the cpu was at its peek.

|||

This happens during quering of the cube and not the processing?

I suggest that you download the Performance Guide which is pointed to at the top post in this message group.

Are you using any real time update of the cube or is it a full MOLAP in all dimensions and in the measure groups?

Regards

Thomas Ivarsson

|||

tee_user5 wrote:

When the cpu usage was that high, I profiled the cube for a 3 minute period and no queries were being performed.

The whole server is slow when this happens, not just AS.

Right now the server is operating much better and msmdsrv is using 300mb. I'm not sure why it wasn't using more ram yesterday.

Note: sqlserver is also on this server and it's not an option to move it at this time. It is now using 600mb of ram when the server is stable.

Update:
I just saw the msmdsrv process take 900mb+, leaving the available amount of memory less than 1mb. Then what happened is that msmdsrv released the memory and then cranked on the cpu. It's now at 99%.

If I look at the event logs, there is an out of memory error logged from today (it's in there for yesterday as well)
--

Event Type: Information
Event Source: MSSQLServerOLAPService
Event Category: (289)
Event ID: 2
Date: 2/16/2007
Time: 10:12:31 AM
User: N/A
Computer: name
Description:
The system is out of memory.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp

--
I understand that it would be advisable to add more memory - but I'm not sure how quick that can be done. Also, why did the process basically give up? It had 900mb of memory, but then it dropped it all and worked down the cpu and continues to crank on it at 99%

|||

More info:

When the server acted up, it was always from the same user. I looked at their setup, and their permissions are done through windows domain groups and the 2 groups in which the user is placed put them in 2 different roles - which each have access to 1 section of a customer region. I took the person out of one of the roles and tried access again - there was no slowness on the server. Any clue as to why something like this would happen with 2 roles hooked up?

|||

I can recreate the problem by putting a test user in 2 different Cube roles - this doesn't even involve windows groups nor any measures!

Each role is for 1 region - east and central.

When I plop on the region attribute for a display, the server starts to take a dive.
Note: only the region attribute was used. I would expect 2 different regions to show. Here is the code that the cube browser creates:

CREATE SESSION
SET [sales].[{AF0CD6C2-0256-4225-AE4C-CF8C3091654D}Pivot2Axis1Set0] AS
'
{
{ [Customer].[Region].[All] },
AddCalculatedMembers([Customer].[Region].[Region].MEMBERS)
}
'

SELECT
NON EMPTY [{AF0CD6C2-0256-4225-AE4C-CF8C3091654D}Pivot2Axis1Set0]
DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS
FROM [sales]
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR

I'm going to see if SP2 helps, but please throw out any ideas. This problem is detrimental to the server.

|||

It is very hard to tell anything about your problem without knowing more about the limit of RAM on your server and how complex the cube is, regarding number of dimensions and each fact table size(number of records) in the cube.

Regards

Thomas Ivarsson

|||

That info was in my 3rd post.
There is little complexity because we have only 3 regions and I could create the problem just by looking at that attribute.

SP2 seems to have fixed the issue, but I'm needing to do more involved testing to make sure it's no longer an issue.

|||Hi

I'm having a similar problem. I have a machinne with 2 fisical processors and two logical, 4GB of memory RAM and 800 GB of free disc space. I'm describing Power Eddge Dell Server. The problem apears when a user perform a query to the cube, no matter the client tool used to make the query the server uses all it's memory and processor capacity an then pased a few minutes It aborts the operation and disconect any user. At this moment they are using Officce Web Component but I'm using the SQL's browser and I have the same problem.

I've made some test using two diferent machins. One of them with SQL Server 2005 SP1 y the other one with SQL Server 2005 SP2 and the results are 100% similar.

I really apreciate if anyone can help me. Thank you very much.|||

does it differ any if the simpliest query is executed?

select from [cube name]

|||

It does not differ depending the complexity of the query. I get the problem, sometimes, only changing then members of a dimention, already included in the query. At this moment the case is on Microsoft Support Engeneers hands. If I receive a useful response from them I will let you know about it.

Thank you very much for your help.

|||

Hi,

I'm afraid I'm facing the same issue.

We are running a 64bit W2003 Server Enterprise with SQL2005 Enterprise Edition (64bit, SP2) on it. The System has 4 CPUs and 8GB of RAM. On the system we are running a Datawarehouse and a SSAS DB.

Now for the error: When I query an OLAP Cube (no mather if with Excel, OWC or the internal Cube Browser in VS) the client application randomly stops responding and you can see that one of the 4 CPUs is peaked on 100% and the memory consumed by SSAS is constantly increasing.

The memory consumption goes up to about 7,5 GB RAM, then the memory is paged (I can see an entry in the event log) and the memory is increasing again, now starting vom 100K Memory. The whole system also is non-respondive in these cases, you can hardly work on the system. The only sol?ution i found so far is restarting the SSAS Service.

The occurace of this error is not bound to a special query or complexity ... after about 5 minutes of "normal" analysis (drilldown, filter, ...) the problem occurs.

Is there an updated information about this issue available?

Greetings Montechristo

|||

We also have this problem.

Using a Intel Xeon QuadCore 3.4 GHz CPU with 3.5 GB ram and W2003Server, SQL2005 SP2.
SSAS and SSRS on the same server.

After a while (at least once a day, with 30 something users average a day) the msmdsrv.exe climbes to ~33% usage on each core.
It also eats up almost all available memory.

Only a restart on SSAS releases it.

During the "hangup" a "SQL server profiler"-session shows no activity.

Any idas?

|||

Since there seem to be a lot of implementations that suffer from this issue, is there any official comment/workaround/solution to this problem available?

Greetings

sql

msmdsrv.exe is taking 99% cpu

I've tried rebooting the server, restarting services, etc, but that only seems to help for about 5-10mins.

When the cpu usage was that high, I profiled the cube for a 3 minute period and no queries were being performed. Is there a way to narrow down what (and why) might be causing that process to hog so much CPU time and making the whole server slow?

I have seen this problem before; and I remember the solution was to apply the latest service pack for Analysis services. SP4.

Which service pack are you running?|||

So sorry I didn't include that info.

SSAS 2005 , SP1
1.8Ghz, 1.5gb ram with around 5 users connecting.
The memory usage wasn't at all bad when this happened. The msmdsrv.exe process took about 35mb of RAM, and there was 800-950mb free, but the cpu was at its peek.

|||

This happens during quering of the cube and not the processing?

I suggest that you download the Performance Guide which is pointed to at the top post in this message group.

Are you using any real time update of the cube or is it a full MOLAP in all dimensions and in the measure groups?

Regards

Thomas Ivarsson

|||

tee_user5 wrote:

When the cpu usage was that high, I profiled the cube for a 3 minute period and no queries were being performed.

The whole server is slow when this happens, not just AS.

Right now the server is operating much better and msmdsrv is using 300mb. I'm not sure why it wasn't using more ram yesterday.

Note: sqlserver is also on this server and it's not an option to move it at this time. It is now using 600mb of ram when the server is stable.

Update:
I just saw the msmdsrv process take 900mb+, leaving the available amount of memory less than 1mb. Then what happened is that msmdsrv released the memory and then cranked on the cpu. It's now at 99%.

If I look at the event logs, there is an out of memory error logged from today (it's in there for yesterday as well)
--

Event Type: Information
Event Source: MSSQLServerOLAPService
Event Category: (289)
Event ID: 2
Date: 2/16/2007
Time: 10:12:31 AM
User: N/A
Computer: name
Description:
The system is out of memory.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp

--
I understand that it would be advisable to add more memory - but I'm not sure how quick that can be done. Also, why did the process basically give up? It had 900mb of memory, but then it dropped it all and worked down the cpu and continues to crank on it at 99%

|||

More info:

When the server acted up, it was always from the same user. I looked at their setup, and their permissions are done through windows domain groups and the 2 groups in which the user is placed put them in 2 different roles - which each have access to 1 section of a customer region. I took the person out of one of the roles and tried access again - there was no slowness on the server. Any clue as to why something like this would happen with 2 roles hooked up?

|||

I can recreate the problem by putting a test user in 2 different Cube roles - this doesn't even involve windows groups nor any measures!

Each role is for 1 region - east and central.

When I plop on the region attribute for a display, the server starts to take a dive.
Note: only the region attribute was used. I would expect 2 different regions to show. Here is the code that the cube browser creates:

CREATE SESSION
SET [sales].[{AF0CD6C2-0256-4225-AE4C-CF8C3091654D}Pivot2Axis1Set0] AS
'
{
{ [Customer].[Region].[All] },
AddCalculatedMembers([Customer].[Region].[Region].MEMBERS)
}
'

SELECT
NON EMPTY [{AF0CD6C2-0256-4225-AE4C-CF8C3091654D}Pivot2Axis1Set0]
DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS
FROM [sales]
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR

I'm going to see if SP2 helps, but please throw out any ideas. This problem is detrimental to the server.

|||

It is very hard to tell anything about your problem without knowing more about the limit of RAM on your server and how complex the cube is, regarding number of dimensions and each fact table size(number of records) in the cube.

Regards

Thomas Ivarsson

|||

That info was in my 3rd post.
There is little complexity because we have only 3 regions and I could create the problem just by looking at that attribute.

SP2 seems to have fixed the issue, but I'm needing to do more involved testing to make sure it's no longer an issue.

|||Hi

I'm having a similar problem. I have a machinne with 2 fisical processors and two logical, 4GB of memory RAM and 800 GB of free disc space. I'm describing Power Eddge Dell Server. The problem apears when a user perform a query to the cube, no matter the client tool used to make the query the server uses all it's memory and processor capacity an then pased a few minutes It aborts the operation and disconect any user. At this moment they are using Officce Web Component but I'm using the SQL's browser and I have the same problem.

I've made some test using two diferent machins. One of them with SQL Server 2005 SP1 y the other one with SQL Server 2005 SP2 and the results are 100% similar.

I really apreciate if anyone can help me. Thank you very much.|||

does it differ any if the simpliest query is executed?

select from [cube name]

|||

It does not differ depending the complexity of the query. I get the problem, sometimes, only changing then members of a dimention, already included in the query. At this moment the case is on Microsoft Support Engeneers hands. If I receive a useful response from them I will let you know about it.

Thank you very much for your help.

|||

Hi,

I'm afraid I'm facing the same issue.

We are running a 64bit W2003 Server Enterprise with SQL2005 Enterprise Edition (64bit, SP2) on it. The System has 4 CPUs and 8GB of RAM. On the system we are running a Datawarehouse and a SSAS DB.

Now for the error: When I query an OLAP Cube (no mather if with Excel, OWC or the internal Cube Browser in VS) the client application randomly stops responding and you can see that one of the 4 CPUs is peaked on 100% and the memory consumed by SSAS is constantly increasing.

The memory consumption goes up to about 7,5 GB RAM, then the memory is paged (I can see an entry in the event log) and the memory is increasing again, now starting vom 100K Memory. The whole system also is non-respondive in these cases, you can hardly work on the system. The only sol?ution i found so far is restarting the SSAS Service.

The occurace of this error is not bound to a special query or complexity ... after about 5 minutes of "normal" analysis (drilldown, filter, ...) the problem occurs.

Is there an updated information about this issue available?

Greetings Montechristo

|||

We also have this problem.

Using a Intel Xeon QuadCore 3.4 GHz CPU with 3.5 GB ram and W2003Server, SQL2005 SP2.
SSAS and SSRS on the same server.

After a while (at least once a day, with 30 something users average a day) the msmdsrv.exe climbes to ~33% usage on each core.
It also eats up almost all available memory.

Only a restart on SSAS releases it.

During the "hangup" a "SQL server profiler"-session shows no activity.

Any idas?

|||

Since there seem to be a lot of implementations that suffer from this issue, is there any official comment/workaround/solution to this problem available?

Greetings

msmdsrv.exe is taking 99% cpu

I've tried rebooting the server, restarting services, etc, but that only seems to help for about 5-10mins.

When the cpu usage was that high, I profiled the cube for a 3 minute period and no queries were being performed. Is there a way to narrow down what (and why) might be causing that process to hog so much CPU time and making the whole server slow?

I have seen this problem before; and I remember the solution was to apply the latest service pack for Analysis services. SP4.

Which service pack are you running?|||

So sorry I didn't include that info.

SSAS 2005 , SP1
1.8Ghz, 1.5gb ram with around 5 users connecting.
The memory usage wasn't at all bad when this happened. The msmdsrv.exe process took about 35mb of RAM, and there was 800-950mb free, but the cpu was at its peek.

|||

This happens during quering of the cube and not the processing?

I suggest that you download the Performance Guide which is pointed to at the top post in this message group.

Are you using any real time update of the cube or is it a full MOLAP in all dimensions and in the measure groups?

Regards

Thomas Ivarsson

|||

tee_user5 wrote:

When the cpu usage was that high, I profiled the cube for a 3 minute period and no queries were being performed.

The whole server is slow when this happens, not just AS.

Right now the server is operating much better and msmdsrv is using 300mb. I'm not sure why it wasn't using more ram yesterday.

Note: sqlserver is also on this server and it's not an option to move it at this time. It is now using 600mb of ram when the server is stable.

Update:
I just saw the msmdsrv process take 900mb+, leaving the available amount of memory less than 1mb. Then what happened is that msmdsrv released the memory and then cranked on the cpu. It's now at 99%.

If I look at the event logs, there is an out of memory error logged from today (it's in there for yesterday as well)
--

Event Type: Information
Event Source: MSSQLServerOLAPService
Event Category: (289)
Event ID: 2
Date: 2/16/2007
Time: 10:12:31 AM
User: N/A
Computer: name
Description:
The system is out of memory.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp

--
I understand that it would be advisable to add more memory - but I'm not sure how quick that can be done. Also, why did the process basically give up? It had 900mb of memory, but then it dropped it all and worked down the cpu and continues to crank on it at 99%

|||

More info:

When the server acted up, it was always from the same user. I looked at their setup, and their permissions are done through windows domain groups and the 2 groups in which the user is placed put them in 2 different roles - which each have access to 1 section of a customer region. I took the person out of one of the roles and tried access again - there was no slowness on the server. Any clue as to why something like this would happen with 2 roles hooked up?

|||

I can recreate the problem by putting a test user in 2 different Cube roles - this doesn't even involve windows groups nor any measures!

Each role is for 1 region - east and central.

When I plop on the region attribute for a display, the server starts to take a dive.
Note: only the region attribute was used. I would expect 2 different regions to show. Here is the code that the cube browser creates:

CREATE SESSION
SET [sales].[{AF0CD6C2-0256-4225-AE4C-CF8C3091654D}Pivot2Axis1Set0] AS
'
{
{ [Customer].[Region].[All] },
AddCalculatedMembers([Customer].[Region].[Region].MEMBERS)
}
'

SELECT
NON EMPTY [{AF0CD6C2-0256-4225-AE4C-CF8C3091654D}Pivot2Axis1Set0]
DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS
FROM [sales]
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR

I'm going to see if SP2 helps, but please throw out any ideas. This problem is detrimental to the server.

|||

It is very hard to tell anything about your problem without knowing more about the limit of RAM on your server and how complex the cube is, regarding number of dimensions and each fact table size(number of records) in the cube.

Regards

Thomas Ivarsson

|||

That info was in my 3rd post.
There is little complexity because we have only 3 regions and I could create the problem just by looking at that attribute.

SP2 seems to have fixed the issue, but I'm needing to do more involved testing to make sure it's no longer an issue.

|||Hi

I'm having a similar problem. I have a machinne with 2 fisical processors and two logical, 4GB of memory RAM and 800 GB of free disc space. I'm describing Power Eddge Dell Server. The problem apears when a user perform a query to the cube, no matter the client tool used to make the query the server uses all it's memory and processor capacity an then pased a few minutes It aborts the operation and disconect any user. At this moment they are using Officce Web Component but I'm using the SQL's browser and I have the same problem.

I've made some test using two diferent machins. One of them with SQL Server 2005 SP1 y the other one with SQL Server 2005 SP2 and the results are 100% similar.

I really apreciate if anyone can help me. Thank you very much.|||

does it differ any if the simpliest query is executed?

select from [cube name]

|||

It does not differ depending the complexity of the query. I get the problem, sometimes, only changing then members of a dimention, already included in the query. At this moment the case is on Microsoft Support Engeneers hands. If I receive a useful response from them I will let you know about it.

Thank you very much for your help.

|||

Hi,

I'm afraid I'm facing the same issue.

We are running a 64bit W2003 Server Enterprise with SQL2005 Enterprise Edition (64bit, SP2) on it. The System has 4 CPUs and 8GB of RAM. On the system we are running a Datawarehouse and a SSAS DB.

Now for the error: When I query an OLAP Cube (no mather if with Excel, OWC or the internal Cube Browser in VS) the client application randomly stops responding and you can see that one of the 4 CPUs is peaked on 100% and the memory consumed by SSAS is constantly increasing.

The memory consumption goes up to about 7,5 GB RAM, then the memory is paged (I can see an entry in the event log) and the memory is increasing again, now starting vom 100K Memory. The whole system also is non-respondive in these cases, you can hardly work on the system. The only sol?ution i found so far is restarting the SSAS Service.

The occurace of this error is not bound to a special query or complexity ... after about 5 minutes of "normal" analysis (drilldown, filter, ...) the problem occurs.

Is there an updated information about this issue available?

Greetings Montechristo

|||

We also have this problem.

Using a Intel Xeon QuadCore 3.4 GHz CPU with 3.5 GB ram and W2003Server, SQL2005 SP2.
SSAS and SSRS on the same server.

After a while (at least once a day, with 30 something users average a day) the msmdsrv.exe climbes to ~33% usage on each core.
It also eats up almost all available memory.

Only a restart on SSAS releases it.

During the "hangup" a "SQL server profiler"-session shows no activity.

Any idas?

|||

Since there seem to be a lot of implementations that suffer from this issue, is there any official comment/workaround/solution to this problem available?

Greetings

msmdsrv.exe is taking 99% cpu

I've tried rebooting the server, restarting services, etc, but that only seems to help for about 5-10mins.

When the cpu usage was that high, I profiled the cube for a 3 minute period and no queries were being performed. Is there a way to narrow down what (and why) might be causing that process to hog so much CPU time and making the whole server slow?

I have seen this problem before; and I remember the solution was to apply the latest service pack for Analysis services. SP4.

Which service pack are you running?
|||

So sorry I didn't include that info.

SSAS 2005 , SP1
1.8Ghz, 1.5gb ram with around 5 users connecting.
The memory usage wasn't at all bad when this happened. The msmdsrv.exe process took about 35mb of RAM, and there was 800-950mb free, but the cpu was at its peek.

|||

This happens during quering of the cube and not the processing?

I suggest that you download the Performance Guide which is pointed to at the top post in this message group.

Are you using any real time update of the cube or is it a full MOLAP in all dimensions and in the measure groups?

Regards

Thomas Ivarsson

|||

tee_user5 wrote:

When the cpu usage was that high, I profiled the cube for a 3 minute period and no queries were being performed.

The whole server is slow when this happens, not just AS.

Right now the server is operating much better and msmdsrv is using 300mb. I'm not sure why it wasn't using more ram yesterday.

Note: sqlserver is also on this server and it's not an option to move it at this time. It is now using 600mb of ram when the server is stable.

Update:
I just saw the msmdsrv process take 900mb+, leaving the available amount of memory less than 1mb. Then what happened is that msmdsrv released the memory and then cranked on the cpu. It's now at 99%.

If I look at the event logs, there is an out of memory error logged from today (it's in there for yesterday as well)
--

Event Type: Information
Event Source: MSSQLServerOLAPService
Event Category: (289)
Event ID: 2
Date: 2/16/2007
Time: 10:12:31 AM
User: N/A
Computer: name
Description:
The system is out of memory.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp

--
I understand that it would be advisable to add more memory - but I'm not sure how quick that can be done. Also, why did the process basically give up? It had 900mb of memory, but then it dropped it all and worked down the cpu and continues to crank on it at 99%

|||

More info:

When the server acted up, it was always from the same user. I looked at their setup, and their permissions are done through windows domain groups and the 2 groups in which the user is placed put them in 2 different roles - which each have access to 1 section of a customer region. I took the person out of one of the roles and tried access again - there was no slowness on the server. Any clue as to why something like this would happen with 2 roles hooked up?

|||

I can recreate the problem by putting a test user in 2 different Cube roles - this doesn't even involve windows groups nor any measures!

Each role is for 1 region - east and central.

When I plop on the region attribute for a display, the server starts to take a dive.
Note: only the region attribute was used. I would expect 2 different regions to show. Here is the code that the cube browser creates:

CREATE SESSION
SET [sales].[{AF0CD6C2-0256-4225-AE4C-CF8C3091654D}Pivot2Axis1Set0] AS
'
{
{ [Customer].[Region].[All] },
AddCalculatedMembers([Customer].[Region].[Region].MEMBERS)
}
'

SELECT
NON EMPTY [{AF0CD6C2-0256-4225-AE4C-CF8C3091654D}Pivot2Axis1Set0]
DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS
FROM [sales]
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR

I'm going to see if SP2 helps, but please throw out any ideas. This problem is detrimental to the server.

|||

It is very hard to tell anything about your problem without knowing more about the limit of RAM on your server and how complex the cube is, regarding number of dimensions and each fact table size(number of records) in the cube.

Regards

Thomas Ivarsson

|||

That info was in my 3rd post.
There is little complexity because we have only 3 regions and I could create the problem just by looking at that attribute.

SP2 seems to have fixed the issue, but I'm needing to do more involved testing to make sure it's no longer an issue.

|||Hi

I'm having a similar problem. I have a machinne with 2 fisical processors and two logical, 4GB of memory RAM and 800 GB of free disc space. I'm describing Power Eddge Dell Server. The problem apears when a user perform a query to the cube, no matter the client tool used to make the query the server uses all it's memory and processor capacity an then pased a few minutes It aborts the operation and disconect any user. At this moment they are using Officce Web Component but I'm using the SQL's browser and I have the same problem.

I've made some test using two diferent machins. One of them with SQL Server 2005 SP1 y the other one with SQL Server 2005 SP2 and the results are 100% similar.

I really apreciate if anyone can help me. Thank you very much.
|||

does it differ any if the simpliest query is executed?

select from [cube name]

|||

It does not differ depending the complexity of the query. I get the problem, sometimes, only changing then members of a dimention, already included in the query. At this moment the case is on Microsoft Support Engeneers hands. If I receive a useful response from them I will let you know about it.

Thank you very much for your help.

|||

Hi,

I'm afraid I'm facing the same issue.

We are running a 64bit W2003 Server Enterprise with SQL2005 Enterprise Edition (64bit, SP2) on it. The System has 4 CPUs and 8GB of RAM. On the system we are running a Datawarehouse and a SSAS DB.

Now for the error: When I query an OLAP Cube (no mather if with Excel, OWC or the internal Cube Browser in VS) the client application randomly stops responding and you can see that one of the 4 CPUs is peaked on 100% and the memory consumed by SSAS is constantly increasing.

The memory consumption goes up to about 7,5 GB RAM, then the memory is paged (I can see an entry in the event log) and the memory is increasing again, now starting vom 100K Memory. The whole system also is non-respondive in these cases, you can hardly work on the system. The only sol?ution i found so far is restarting the SSAS Service.

The occurace of this error is not bound to a special query or complexity ... after about 5 minutes of "normal" analysis (drilldown, filter, ...) the problem occurs.

Is there an updated information about this issue available?

Greetings Montechristo

|||

We also have this problem.

Using a Intel Xeon QuadCore 3.4 GHz CPU with 3.5 GB ram and W2003Server, SQL2005 SP2.
SSAS and SSRS on the same server.

After a while (at least once a day, with 30 something users average a day) the msmdsrv.exe climbes to ~33% usage on each core.
It also eats up almost all available memory.

Only a restart on SSAS releases it.

During the "hangup" a "SQL server profiler"-session shows no activity.

Any idas?

|||

Since there seem to be a lot of implementations that suffer from this issue, is there any official comment/workaround/solution to this problem available?

Greetings

msftesql memory usage

We have been testing SQL 2005 full-text search, and noticed that the
msftesql process isn't using much memory (only around 6 megs), when the
single table we're indexing has about 3.8 millions rows, with 4 varchar
columns indexed ranging between varchar(255) and varchar(750). Is this
normal, i.e., is the full text search service using sqlservr.exe's memory?
Our server is a 2-way 2.0 GHZ opteron with 4GB RAM using WIN2K server with
/3GB switch.
We've looked around for a way to increase the memory available to msfteql
without success.
Thanks,
John
John,
Yes, its normal, but you can control the "aggressiveness" of the SQL Server
2005 MSFTESQL service via several advance sp_configure settings. Run the
below SQL code and review the new fulltext search options:
use master
go
sp_configure 'show advanced options', 1
reconfigure with override
go
sp_configure
There should be several new option to allow you to control the memory
allocation of the MSFTESQL service. No, the MSFTESQL uses its own memory
separate from the SQL Server memory.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"John" <jglass67@.msn.com> wrote in message
news:uocvvK2lFHA.764@.TK2MSFTNGP14.phx.gbl...
> We have been testing SQL 2005 full-text search, and noticed that the
> msftesql process isn't using much memory (only around 6 megs), when the
> single table we're indexing has about 3.8 millions rows, with 4 varchar
> columns indexed ranging between varchar(255) and varchar(750). Is this
> normal, i.e., is the full text search service using sqlservr.exe's memory?
> Our server is a 2-way 2.0 GHZ opteron with 4GB RAM using WIN2K server with
> /3GB switch.
> We've looked around for a way to increase the memory available to msfteql
> without success.
> Thanks,
> John
>

MSDTC and Delete transactions

Hello
I had a question reagrding MSDTC usage. I know that MSDTC will enlist
each connection transactions as it's own (take ownership) and
commit/abort accordingly.

However I'm not sure what would happen where I have a stored proc with
input parameters and 2 Delete commands on 2 different tables. In this
case, would MSDTC also enforce ACID rules when 1 cmd succeeds and 2nd
aborts ? In the script for the Stored Proc, there is no checking for
@.@.ERROR value. There are just DELETE statement and a plain RETURN.

thanks
Sunitsjoshi (sjoshi@.ingr.com) writes:

Quote:

Originally Posted by

I had a question reagrding MSDTC usage. I know that MSDTC will enlist
each connection transactions as it's own (take ownership) and
commit/abort accordingly.
>
However I'm not sure what would happen where I have a stored proc with
input parameters and 2 Delete commands on 2 different tables. In this
case, would MSDTC also enforce ACID rules when 1 cmd succeeds and 2nd
aborts ? In the script for the Stored Proc, there is no checking for
@.@.ERROR value. There are just DELETE statement and a plain RETURN.

In a normal stored procedure if you have:

BEGIN TRANSACTION
DELETE tbl WHERE ...
DELETE tbl2 WHERE ...
COMMIT TRANSACTION

you could very well end up with only one of the DELETE statements
being carried out, becuase many errors in SQL Server aborts the
current statment only.

A distributed transaction may be different. At least in some
situations there is a requirement that SET XACT_ABORT is ON. With
this settings all errors but compile errors abort the batch and
rollback the transaction.

But exactly what happens, I will have to admit that I don't know. The
best advice I can give is to test a scenario where one of the DELETE
statement fails. No, wait, the best is to either add checks on @.@.error
or make sure XACT_ABORT is ON.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx