Another one about blocking

This is weird, but I hope someone can help me out:
MSSQLServer 2000, SP 2, W2K Server
Dedicated server with one DB, 60 concurrent users
Locktimeout is set
The DB is accesses via my app only.
About once a week the DB runs into a blocking situation, wich locks up all
other users. Although the lock timeout is set, no timeout occurs. I
monitored the DB for the blockees and got grip of the blocking statement:
select count(*) as c FROM TBLMWTRACE M WITH (NOLOCK) WHERE m.datum = Convert(datetime, '20031120', 112) and ((GELESEN IS NULL) or (GELESEN = 'N')) AND (M.HAUSCD = 'BRI')
I researched my app and I am quite sure, that there are no transactions left
open. If lengthy select statements were the cause, they would have been
captured by the monitor.
My assumption is, that if SQLServer gets under load, he starts issuing
x-locks without releasing them. But this can´t be true, of course.
What can I do to track down what the causes the blocks?
Thanks BerndHi Bernd !
Seems, that the option "with (NOLOCK)" does not work as expected. This
option is identical to transaction mode set to uncommmitted, that means,
that no locks are set, incoinsitencies may occurr. Have you considered
the "lock escalation" ? SQL Server 2000 automatically switches to table
lock, whenever too many users try to access a database.
My advice. Use databases with MVTO, MVRO, your problems will disappear.
look for it in google groups.
regards, Guido Stepken
Bernd Maierhofer (dato) wrote:
> This is weird, but I hope someone can help me out:
> MSSQLServer 2000, SP 2, W2K Server
> Dedicated server with one DB, 60 concurrent users
> Locktimeout is set
> The DB is accesses via my app only.
> About once a week the DB runs into a blocking situation, wich locks up all
> other users. Although the lock timeout is set, no timeout occurs. I
> monitored the DB for the blockees and got grip of the blocking statement:
> select count(*) as c FROM TBLMWTRACE M WITH (NOLOCK) WHERE m.datum => Convert(datetime, '20031120', 112) and ((GELESEN IS NULL) or (GELESEN => 'N')) AND (M.HAUSCD = 'BRI')
> I researched my app and I am quite sure, that there are no transactions left
> open. If lengthy select statements were the cause, they would have been
> captured by the monitor.
> My assumption is, that if SQLServer gets under load, he starts issuing
> x-locks without releasing them. But this can´t be true, of course.
> What can I do to track down what the causes the blocks?
> Thanks Bernd
>

Analysis Services not Started!

Good Day!

I have installed SQL Server 2005

In services.msc SQL Server Analysis Services (MSSQLSERVER) can't be started.

Message: Service Started and then Stopped and

The service cannot be started: The following system error occurred: Only one usage of each socket address (protocol/network address/port) is normally permitted.

What is this?

Thanks...

Some application occupied AS TCP port. In case of default instance it would be 2383. Please run netstat or (even better) tcpmon to find out what this application is.|||

Wow.

It seems like SQL Analysis Services can't live with started SAP on the Machine.

What ports use Analysis Services?

|||

Default instance uses port 2383.

The redirector for named instances uses port 2382.

Ports for named instances can be manually overriden.

|||

Ok

Where can I change instance port for Analysis?

|||Server -> Properties -> Show All (advanced) properties -> Port|||

Mosha Pasumansky wrote:

Server -> Properties -> Show All (advanced) properties -> Port

Sorry for borring, I'm a novice.

Where is it? I'm going in Computer Managment -> SQL Server Configuration Manager -> SQL Server 2005 Services -> SQL Server Analysis Services ->Properties

But there nothing...

|||

You need to go to SQL Server Mgmt Studio> Connect to Analysis Services>Right Click on the server-> Hit Properties> Scroll down and check the box "Show Advanced(All) properties" and then you will see the property "Port"