Processing OLAP Cube causes OLAP and SQL to shut down

Here's a situation I ran in recently, and I'm trying to discover root cause.
I should mention this was occuring on a active/active two-node cluster w/ 6
instances of SQL running on it. The OLAP service in question was tied to th
e
SQL Server service as part of the same cluster group.
I had a cube in OLAP I was attempting to process, but each time I tried the
OLAP service and the SQL Server Service that OLAP was using as a data source
would shut down and fail over to another cluster node. This would
continually happen each time we would try to process the cube - it would fai
l
over back and forth to the two nodes on the cluster.
Finally when we manually took the cluster group down and brought it back up,
everything stabilized. Not sure why this smoothed things out as the SQL
Server instance and the OLAP service had been on the correct node before, bu
t
now that we had moved the instance manually, everything was fine.
My only guess as to root cause was contention for resources. Does anybody
else have any ideas? Again, the problem I'm trying to understand is why
processing a cube will cause a SQL Server and OLAP instance to shut down.
Thanks in advance.Yes! Resource contention. If you are trying to run 6 instances of SQL
Server and at least 1 Analysis Services server on only a two node cluster,
you most assuradely have resource contention and it is called MEMORY, which,
too, is most assuradely used during the building of the cube.
How much memory are you running on those two nodes? What is the MAX SERVER
MEMORY configuration set to on each of the instances? What is the setting
for the source of the OLAP cube? Anything short of about 512 MB to 1 GB for
each instances, plus about 1 GB for the Analysis Services, is under capacity
for any real use production system.
Do you have 6 to 12 or so GB on EACH node?
Sincerely,
Anthony Thomas
"TX_KniveS" <TX_KniveS@.discussions.microsoft.com> wrote in message
news:9C541BA2-E1B4-46C8-8F1D-3D1D145A622C@.microsoft.com...
Here's a situation I ran in recently, and I'm trying to discover root cause.
I should mention this was occuring on a active/active two-node cluster w/ 6
instances of SQL running on it. The OLAP service in question was tied to
the
SQL Server service as part of the same cluster group.
I had a cube in OLAP I was attempting to process, but each time I tried the
OLAP service and the SQL Server Service that OLAP was using as a data source
would shut down and fail over to another cluster node. This would
continually happen each time we would try to process the cube - it would
fail
over back and forth to the two nodes on the cluster.
Finally when we manually took the cluster group down and brought it back up,
everything stabilized. Not sure why this smoothed things out as the SQL
Server instance and the OLAP service had been on the correct node before,
but
now that we had moved the instance manually, everything was fine.
My only guess as to root cause was contention for resources. Does anybody
else have any ideas? Again, the problem I'm trying to understand is why
processing a cube will cause a SQL Server and OLAP instance to shut down.
Thanks in advance.

Processing OLAP Cube causes OLAP and SQL to shut down

Here's a situation I ran in recently, and I'm trying to discover root cause.
I should mention this was occuring on a active/active two-node cluster w/ 6
instances of SQL running on it. The OLAP service in question was tied to the
SQL Server service as part of the same cluster group.
I had a cube in OLAP I was attempting to process, but each time I tried the
OLAP service and the SQL Server Service that OLAP was using as a data source
would shut down and fail over to another cluster node. This would
continually happen each time we would try to process the cube - it would fail
over back and forth to the two nodes on the cluster.
Finally when we manually took the cluster group down and brought it back up,
everything stabilized. Not sure why this smoothed things out as the SQL
Server instance and the OLAP service had been on the correct node before, but
now that we had moved the instance manually, everything was fine.
My only guess as to root cause was contention for resources. Does anybody
else have any ideas? Again, the problem I'm trying to understand is why
processing a cube will cause a SQL Server and OLAP instance to shut down.
Thanks in advance.Yes! Resource contention. If you are trying to run 6 instances of SQL
Server and at least 1 Analysis Services server on only a two node cluster,
you most assuradely have resource contention and it is called MEMORY, which,
too, is most assuradely used during the building of the cube.
How much memory are you running on those two nodes? What is the MAX SERVER
MEMORY configuration set to on each of the instances? What is the setting
for the source of the OLAP cube? Anything short of about 512 MB to 1 GB for
each instances, plus about 1 GB for the Analysis Services, is under capacity
for any real use production system.
Do you have 6 to 12 or so GB on EACH node?
Sincerely,
Anthony Thomas
"TX_KniveS" <TX_KniveS@.discussions.microsoft.com> wrote in message
news:9C541BA2-E1B4-46C8-8F1D-3D1D145A622C@.microsoft.com...
Here's a situation I ran in recently, and I'm trying to discover root cause.
I should mention this was occuring on a active/active two-node cluster w/ 6
instances of SQL running on it. The OLAP service in question was tied to
the
SQL Server service as part of the same cluster group.
I had a cube in OLAP I was attempting to process, but each time I tried the
OLAP service and the SQL Server Service that OLAP was using as a data source
would shut down and fail over to another cluster node. This would
continually happen each time we would try to process the cube - it would
fail
over back and forth to the two nodes on the cluster.
Finally when we manually took the cluster group down and brought it back up,
everything stabilized. Not sure why this smoothed things out as the SQL
Server instance and the OLAP service had been on the correct node before,
but
now that we had moved the instance manually, everything was fine.
My only guess as to root cause was contention for resources. Does anybody
else have any ideas? Again, the problem I'm trying to understand is why
processing a cube will cause a SQL Server and OLAP instance to shut down.
Thanks in advance.

Process won't die

While troubleshooting a problem with a web app having intermittent problems, I'm looking at the database as the cause. I made a backup of the database. Afterward I noticed a process in Activity Monitor using that database with 2 open transactions and a green check icon that won't kill (it says "Cannot use KILL to kill your own process. error 6104"); this process was not present prior to the backup. The process User shows my login (using Windows authentication and my domain account or while logged in as SA) and the Application is SQL Server Mgmt Studio. The host is the server name. Status is runnable.

I stopped and restarted SQL Server and SQL Server Agent using the Windows Services Console, not the SAC if it makes a difference. I also detached and reattached the database; the process still lives with 2 Open Transactions and is still using the same database. The text of the details doesn't change at all, which is:

create table #tmpDBCCinputbuffer ([Event Type] nvarchar(512), [Parameters] int, [Event Info] nvarchar(512))
insert into #tmpDBCCinputbuffer exec ('DBCC INPUTBUFFER(61)')
select [Event Info] from #tmpDBCCinputbuffer

I don't know if this has anything to do with the original problem, but I don't think it is helping either. Can someone tell me what the details mean and how to kill the unkillable process?

Thanks!

I think you'll find this to be a bit of a red herring.

In Activity Monitor does the name in the 'User' column for this particular row contain the name of the login that you use to connect to the SQL Server instance in Management Studio? If so then there's nothing to worry about as the row represents Management Studio's connection to the SQL Server instance. You should also find that the default database of the login that you are using is set to the name of the database that is displayed in the row.

The DBCC INPUTBUFFER(spid) command will return the text of the most recent batch executed against a specified SPID. When you double-click on a row in Activity Monitor (or right-click and select 'Details') then, under the covers, SQL Server runs the command you quoted in your post above (substituting the SPID of the selected row - 61 in your example) to return the most recent batch issued against the connection. In your example the batch actually happens to be the CREATE TABLE etc... batch that was issued by Activity Monitor to return the most recent batch!

In short, there's no need to worry about this connection - it is perfectly normal to see it when Management Studio is open and it is unlikely to be causing any performance problems.

Chris

|||

The name is always the login used for Mgmt Studio. My login's default db is master, but the activity monitor shows the db in question, even when I set to use another db and run a query against it. I'm curious why I hadn't ever seen it before. Are there certain conditions that will make it appear or not appear?

I think I figured out the original problem, but I'm still curious about this process. I stopped and restarted the service. The first Activity Monitor refresh immediately after doing so showed the db as master, refresh about 5 seconds later the db showed tempdb... then a couple more refreshes later showed the db in question, all without me actually running any commands. Ten minutes later it is still showing the db in question.