Cannot DROP SERVICE

I'm having trouble dropping a Service Broker Seriver using the DROP SERVICE command.

After about 15 minutes, my SQL Server 2005 Standard edition replies with the following message:

Msg 701, Level 17, State 171, Line 1
There is insufficient system memory to run this query.

Is there another way to remove a service? I've tried restarting SQL, but this does not work.

Open a second connection to the server and please run this command:

dbcc memorystatus

Run it one before the DROP statement is issued, one or twice while is running (e.g after 5 minutes and after 10 minutes) and then once when the error occurs. Save the output each time into a file and please send me this output to remus.rusanu@.microsoft.com

This way I hope to identify the problem.

Thanks,
~ Remus

|||For others out there, here is the before report.

<before>
Memory Manager KB
--
VM Reserved 1064060
VM Committed 693280
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0

(5 row(s) affected)

Memory node Id = 0 KB
--
VM Reserved 1059964
VM Committed 689336
AWE Allocated 0
MultiPage Allocator 7704
SinglePage Allocator 159856

(5 row(s) affected)

MEMORYCLERK_SQLGENERAL (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 880
MultiPage Allocator 1592

(7 row(s) affected)

MEMORYCLERK_SQLBUFFERPOOL (Total) KB
- --
VM Reserved 1047772
VM Committed 677824
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 256

(7 row(s) affected)

MEMORYCLERK_SQLOPTIMIZER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 144
MultiPage Allocator 72

(7 row(s) affected)

MEMORYCLERK_SQLUTILITIES (Total) KB
- --
VM Reserved 480
VM Committed 480
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 96
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLSTORENG (Total) KB
- --
VM Reserved 1024
VM Committed 1024
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 4088
MultiPage Allocator 72

(7 row(s) affected)

MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 240
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLCLR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLSERVICEBROKER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 17512
MultiPage Allocator 192

(7 row(s) affected)

MEMORYCLERK_SQLHTTP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SNI (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 16

(7 row(s) affected)

MEMORYCLERK_FULLTEXT (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLXP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_BHF (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_HOST (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SOSNODE (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 984
MultiPage Allocator 5320

(7 row(s) affected)

MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_OBJCP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 816
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_SQLCP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 6120
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_PHDR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 864
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XPROC (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 40
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_TEMPTABLES (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_NOTIF (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_VIEWDEFINITIONS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBTYPE (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBELEMENT (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBATTRIBUTE (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_STACKFRAMES (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 8

(7 row(s) affected)

CACHESTORE_BROKERTBLACS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 152
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERKEK (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERDSH (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERUSERCERTLOOKUP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERRSB (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERREADONLY (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 32
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERTO (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 39496
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_EVENTS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_SYSTEMROWSET (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 368
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_SCHEMAMGR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 424
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_DBMETADATA (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 224
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_TOKENPERM (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 72
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_OBJPERM (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 128
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_SXC (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_LBSS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 40
MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_SNI_PACKET (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 240
MultiPage Allocator 48

(7 row(s) affected)

OBJECTSTORE_SERVICE_BROKER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 34984
MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_LOCK_MANAGER (Total) KB
- --
VM Reserved 2048
VM Committed 2048
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 51608
MultiPage Allocator 0

(7 row(s) affected)

Buffer Distribution Buffers
--
Stolen 3016
Free 49283
Cached 16966
Database (clean) 3079
Database (dirty) 11117
I/O 0
Latched 1

(7 row(s) affected)

Buffer Counts Buffers
--
Committed 83462
Target 89991
Hashed 14197
Stolen Potential 103236
External Reservation 0
Min Free 32
Visible 89991
Available Paging File 178838

(8 row(s) affected)

Procedure Cache Value
--
TotalProcs 84
TotalPages 980
InUsePages 11

(3 row(s) affected)

Global Memory Objects Buffers
--
Resource 145
Locks 6454
XDES 461
SETLS 1
SE Dataset Allocators 2
SubpDesc Allocators 1
SE SchemaManager 52
SQLCache 33
Replication 2
ServerGlobal 25
XP Global 2
SortTables 2

(12 row(s) affected)

Query Memory Objects Value
--
Grants 0
Waiting 0
Available (Buffers) 62344
Maximum (Buffers) 62344
Limit 62344
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Last Target 65625

(11 row(s) affected)

Small Query Memory Objects Value
--
Grants 0
Waiting 0
Available (Buffers) 3281
Maximum (Buffers) 3281
Limit 3281

(5 row(s) affected)

Optimization Queue Value
--
Overall Memory 575897600
Target Memory 470007808
Last Notification 1
Timeout 6
Early Termination Factor 5

(5 row(s) affected)

Small Gateway Value
--
Configured Units 4
Available Units 4
Acquires 0
Waiters 0
Threshold Factor 250000
Threshold 250000

(6 row(s) affected)

Medium Gateway Value
--
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 12

(5 row(s) affected)

Big Gateway Value
--
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8

(5 row(s) affected)

MEMORYBROKER_FOR_CACHE Value
-- --
Allocations 16967
Rate 0
Target Allocations 70300
Future Allocations 0
Last Notification 1

(5 row(s) affected)

MEMORYBROKER_FOR_STEAL Value
-- --
Allocations 3010
Rate 0
Target Allocations 57374
Future Allocations 0
Last Notification 1

(5 row(s) affected)

MEMORYBROKER_FOR_RESERVE Value
-- --
Allocations 0
Rate 0
Target Allocations 70014
Future Allocations 15650
Last Notification 1

(5 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
</before>

Here is the 7 minute report.

<7min>
Memory Manager KB
--
VM Reserved 1067708
VM Committed 1055632
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0

(5 row(s) affected)

Memory node Id = 0 KB
--
VM Reserved 1063612
VM Committed 1051688
AWE Allocated 0
MultiPage Allocator 11320
SinglePage Allocator 974488

(5 row(s) affected)

MEMORYCLERK_SQLGENERAL (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 872
MultiPage Allocator 1592

(7 row(s) affected)

MEMORYCLERK_SQLBUFFERPOOL (Total) KB
- --
VM Reserved 1047772
VM Committed 1036560
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 256

(7 row(s) affected)

MEMORYCLERK_SQLOPTIMIZER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 144
MultiPage Allocator 72

(7 row(s) affected)

MEMORYCLERK_SQLUTILITIES (Total) KB
- --
VM Reserved 480
VM Committed 480
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 96
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLSTORENG (Total) KB
- --
VM Reserved 1024
VM Committed 1024
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 4096
MultiPage Allocator 3688

(7 row(s) affected)

MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 240
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLCLR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLSERVICEBROKER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 17512
MultiPage Allocator 192

(7 row(s) affected)

MEMORYCLERK_SQLHTTP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SNI (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 16

(7 row(s) affected)

MEMORYCLERK_FULLTEXT (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLXP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_BHF (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_HOST (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SOSNODE (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 944
MultiPage Allocator 5320

(7 row(s) affected)

MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_OBJCP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 152
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_SQLCP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 10072
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_PHDR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XPROC (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_TEMPTABLES (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_NOTIF (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_VIEWDEFINITIONS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBTYPE (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBELEMENT (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBATTRIBUTE (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_STACKFRAMES (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 8

(7 row(s) affected)

CACHESTORE_BROKERTBLACS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 128
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERKEK (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERDSH (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERUSERCERTLOOKUP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERRSB (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERREADONLY (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 32
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERTO (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 145136
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_EVENTS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_SYSTEMROWSET (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 64
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_SCHEMAMGR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 424
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_DBMETADATA (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 160
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_TOKENPERM (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 72
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_OBJPERM (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 128
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_SXC (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_LBSS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_SNI_PACKET (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 208
MultiPage Allocator 48

(7 row(s) affected)

OBJECTSTORE_SERVICE_BROKER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 742048
MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_LOCK_MANAGER (Total) KB
- --
VM Reserved 2048
VM Committed 2048
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 51512
MultiPage Allocator 0

(7 row(s) affected)

Buffer Distribution Buffers
--
Stolen 3012
Free 274
Cached 118808
Database (clean) 1899
Database (dirty) 4308
I/O 1
Latched 2

(7 row(s) affected)

Buffer Counts Buffers
--
Committed 128304
Target 128304
Hashed 6212
Stolen Potential 1421
External Reservation 0
Min Free 32
Visible 128304
Available Paging File 133742

(8 row(s) affected)

Procedure Cache Value
--
TotalProcs 1
TotalPages 1281
InUsePages 0

(3 row(s) affected)

Global Memory Objects Buffers
--
Resource 146
Locks 6442
XDES 914
SETLS 1
SE Dataset Allocators 2
SubpDesc Allocators 1
SE SchemaManager 52
SQLCache 31
Replication 2
ServerGlobal 25
XP Global 2
SortTables 2

(12 row(s) affected)

Query Memory Objects Value
--
Grants 0
Waiting 0
Available (Buffers) 5285
Maximum (Buffers) 5285
Limit 5240
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Last Target 5880

(11 row(s) affected)

Small Query Memory Objects Value
--
Grants 0
Waiting 0
Available (Buffers) 640
Maximum (Buffers) 640
Limit 640

(5 row(s) affected)

Optimization Queue Value
--
Overall Memory 822476800
Target Memory 51404800
Last Notification 0
Timeout 6
Early Termination Factor 5

(5 row(s) affected)

Small Gateway Value
--
Configured Units 4
Available Units 4
Acquires 0
Waiters 0
Threshold Factor 250000
Threshold 250000

(6 row(s) affected)

Medium Gateway Value
--
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 12

(5 row(s) affected)

Big Gateway Value
--
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8

(5 row(s) affected)

MEMORYBROKER_FOR_CACHE Value
-- --
Allocations 118885
Rate -706
Target Allocations 82639
Future Allocations 0
Last Notification 2

(5 row(s) affected)

MEMORYBROKER_FOR_STEAL Value
-- --
Allocations 3004
Rate -3
Target Allocations 6275
Future Allocations 0
Last Notification 0

(5 row(s) affected)

MEMORYBROKER_FOR_RESERVE Value
-- --
Allocations 0
Rate -96
Target Allocations 6275
Future Allocations 22354
Last Notification 0

(5 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
</7min>

Here is the after report.

<after>
Memory Manager KB
--
VM Reserved 1064060
VM Committed 755880
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0

(5 row(s) affected)

Memory node Id = 0 KB
--
VM Reserved 1059964
VM Committed 751936
AWE Allocated 0
MultiPage Allocator 7704
SinglePage Allocator 154824

(5 row(s) affected)

MEMORYCLERK_SQLGENERAL (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 872
MultiPage Allocator 1592

(7 row(s) affected)

MEMORYCLERK_SQLBUFFERPOOL (Total) KB
- --
VM Reserved 1047772
VM Committed 740424
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 256

(7 row(s) affected)

MEMORYCLERK_SQLOPTIMIZER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 144
MultiPage Allocator 72

(7 row(s) affected)

MEMORYCLERK_SQLUTILITIES (Total) KB
- --
VM Reserved 480
VM Committed 480
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 96
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLSTORENG (Total) KB
- --
VM Reserved 1024
VM Committed 1024
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 4088
MultiPage Allocator 72

(7 row(s) affected)

MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 224
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLCLR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLSERVICEBROKER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 17512
MultiPage Allocator 192

(7 row(s) affected)

MEMORYCLERK_SQLHTTP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SNI (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 16

(7 row(s) affected)

MEMORYCLERK_FULLTEXT (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLXP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_BHF (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_HOST (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SOSNODE (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 976
MultiPage Allocator 5320

(7 row(s) affected)

MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_OBJCP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 152
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_SQLCP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 56
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_PHDR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XPROC (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_TEMPTABLES (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_NOTIF (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_VIEWDEFINITIONS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBTYPE (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBELEMENT (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBATTRIBUTE (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_STACKFRAMES (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 8

(7 row(s) affected)

CACHESTORE_BROKERTBLACS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 128
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERKEK (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERDSH (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERUSERCERTLOOKUP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERRSB (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERREADONLY (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 32
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERTO (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 50480
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_EVENTS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_SYSTEMROWSET (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 104
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_SCHEMAMGR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 424
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_DBMETADATA (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 160
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_TOKENPERM (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 72
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_OBJPERM (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 128
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_SXC (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_LBSS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_SNI_PACKET (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 208
MultiPage Allocator 48

(7 row(s) affected)

OBJECTSTORE_SERVICE_BROKER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 27064
MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_LOCK_MANAGER (Total) KB
- --
VM Reserved 2048
VM Committed 2048
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 51560
MultiPage Allocator 0

(7 row(s) affected)

Buffer Distribution Buffers
--
Stolen 3012
Free 58046
Cached 16341
Database (clean) 1591
Database (dirty) 12296
I/O 0
Latched 1

(7 row(s) affected)

Buffer Counts Buffers
--
Committed 91287
Target 97284
Hashed 13888
Stolen Potential 103865
External Reservation 0
Min Free 32
Visible 97284
Available Paging File 170966

(8 row(s) affected)

Procedure Cache Value
--
TotalProcs 2
TotalPages 29
InUsePages 0

(3 row(s) affected)

Global Memory Objects Buffers
--
Resource 146
Locks 6448
XDES 461
SETLS 1
SE Dataset Allocators 2
SubpDesc Allocators 1
SE SchemaManager 52
SQLCache 31
Replication 2
ServerGlobal 25
XP Global 2
SortTables 2

(12 row(s) affected)

Query Memory Objects Value
--
Grants 0
Waiting 0
Available (Buffers) 68529
Maximum (Buffers) 68529
Limit 67688
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Last Target 71250

(11 row(s) affected)

Small Query Memory Objects Value
--
Grants 0
Waiting 0
Available (Buffers) 3605
Maximum (Buffers) 3605
Limit 3605

(5 row(s) affected)

Optimization Queue Value
--
Overall Memory 622592000
Target Memory 524369920
Last Notification 1
Timeout 6
Early Termination Factor 5

(5 row(s) affected)

Small Gateway Value
--
Configured Units 4
Available Units 4
Acquires 0
Waiters 0
Threshold Factor 250000
Threshold 250000

(6 row(s) affected)

Medium Gateway Value
--
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 12

(5 row(s) affected)

Big Gateway Value
--
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8

(5 row(s) affected)

MEMORYBROKER_FOR_CACHE Value
-- --
Allocations 16342
Rate 0
Target Allocations 76000
Future Allocations 0
Last Notification 1

(5 row(s) affected)

MEMORYBROKER_FOR_STEAL Value
-- --
Allocations 3006
Rate 0
Target Allocations 64010
Future Allocations 0
Last Notification 1

(5 row(s) affected)

MEMORYBROKER_FOR_RESERVE Value
-- --
Allocations 0
Rate 0
Target Allocations 76000
Future Allocations 16922
Last Notification 1

(5 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
</after>
|||

DROP SERVICE has to send an END CONVERSATION to each conversation owned by that service. Apparently, the system in question cannot handle this load in one single transaction.

To work around, end all the conversations first, using a cursor over sys.conversation_endpoints:

declare crsConversations cursor for

select conversation_handle

from sys.conversation_endpoints e

join sys.services s on s.service_id = e.service_id

where s.name = 'myservicename';

open crsConversations;

declare @.dh uniqueidentifier;

declare @.batch int;

select @.batch = 0;

begin transaction

fetch next from crsConversations into @.dh;

while @.@.fetch_status =0

begin

-- replace this with end conversation @.dh with error ... if

-- and error has to be sent to the peer

--

end conversation @.dh with cleanup;

-- commit every 1000 conversations ended

select @.batch = @.batch + 1;

if @.batch > 999

begin

commit transaction;

begin transaction;

end

fetch next from crsConversations into @.dh;

end

commit transaction;

close crsConversations;

deallocate crsConversations;

go

drop service 'myservicename'

go

Cannot Drop Index

I could not find a way to remove primary key from an index, thus could not
drop it.
Command:
drop index Romm_Inv.PK_Room_Inv
Error
Server: Msg 3723, Level 16, State 4, Line 1
An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
It is being used for PRIMARY KEY constraint enforcement.
The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it did
not work.
Gracias. LaEsmeraldaALTER TABLE is what you are supposed to use. What statement did you use and
what was the error message?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:F00810B8-62EA-4E19-A1EF-71AA5C8F9AB8@.microsoft.com...
I could not find a way to remove primary key from an index, thus could not
drop it.
Command:
drop index Romm_Inv.PK_Room_Inv
Error
Server: Msg 3723, Level 16, State 4, Line 1
An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
It is being used for PRIMARY KEY constraint enforcement.
The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it did
not work.
Gracias. LaEsmeralda|||Hola LaEsmeralda
You do need to use ALTER TABLE to remove a primary key constraint, which
will drop the index along with it.
What does 'did not work' mean? Can you show us the statement you were using
and the error message you received.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:F00810B8-62EA-4E19-A1EF-71AA5C8F9AB8@.microsoft.com...
>I could not find a way to remove primary key from an index, thus could not
> drop it.
> Command:
> drop index Romm_Inv.PK_Room_Inv
> Error
> Server: Msg 3723, Level 16, State 4, Line 1
> An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
> It is being used for PRIMARY KEY constraint enforcement.
> The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it
> did
> not work.
> Gracias. LaEsmeralda|||I made it, Tom. Thanks a lot for quick help.
"Tom Moreau" wrote:
> ALTER TABLE is what you are supposed to use. What statement did you use and
> what was the error message?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
> news:F00810B8-62EA-4E19-A1EF-71AA5C8F9AB8@.microsoft.com...
> I could not find a way to remove primary key from an index, thus could not
> drop it.
> Command:
> drop index Romm_Inv.PK_Room_Inv
> Error
> Server: Msg 3723, Level 16, State 4, Line 1
> An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
> It is being used for PRIMARY KEY constraint enforcement.
> The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it did
> not work.
> Gracias. LaEsmeralda
>

cannot create multiple triggers

PLS HELP !!!!
I cannot create multiple triggers (AFTER) of command type INSERT. When
I create a new one the previous one gets deleted.
Prodcut: SQL Server Enterprise Edition.
Product Version:8.00.194 (TRM).
The funny thing is that this happens on clients production environment
while I am able to create multiple triggers on my development
environment. The properties for this sql servers is as follows
Product: SQL Server Enterprise Edition.
Production Version: 8.00.760 (SP3).
Is this because of different product version or am i missing something?Hi
Tell you client to apply at least SQL Server 20000 SP3a. They are totally
unpatched and susceptible to Slammer.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<agandhi@.usc.edu> wrote in message
news:1121983311.188033.51980@.g47g2000cwa.googlegroups.com...
> PLS HELP !!!!
> I cannot create multiple triggers (AFTER) of command type INSERT. When
> I create a new one the previous one gets deleted.
> Prodcut: SQL Server Enterprise Edition.
> Product Version:8.00.194 (TRM).
> The funny thing is that this happens on clients production environment
> while I am able to create multiple triggers on my development
> environment. The properties for this sql servers is as follows
> Product: SQL Server Enterprise Edition.
> Production Version: 8.00.760 (SP3).
> Is this because of different product version or am i missing something?
>|||Thanks for the recommendation and I will do that immediately but any
idea about not being able to create multiple triggers.
Regards,|||Why SP3a and not SP4? SQL Server 2000 is installed on Windows 2000
Machine.|||The service pack may fix the problem you're having with creating multiple
triggers. If it works on the development server and not on the production
server, then it is probably the service pack. Unless the triggers are
instead of triggers, you can have more than one trigger of the same kind on
a table provided the name of the trigger is different for each one.
<agandhi@.usc.edu> wrote in message
news:1121985554.564111.259640@.g47g2000cwa.googlegroups.com...
> Thanks for the recommendation and I will do that immediately but any
> idea about not being able to create multiple triggers.
> Regards,
>|||agandhi@.usc.edu wrote:
> Why SP3a and not SP4? SQL Server 2000 is installed on Windows 2000
> Machine.
He said SP3a as a *minimum*. Whether they're ready to move up to SP4 or
not is more of a business decision, and less of a "we have a massive
security hole here" kind of thing.
Damien

Cannot connect to SQL Express Remotely

I have SQL Express April CTP (from VS Beta 2) installed on machineA, but I cannot connect to it from machineB using the command "sqlcmd -S machineA\sqlexpress -E". This command works just fine from machineA.

I have enabled the TCP/IP protocol for the SQLExpress instance through the configuration manager, and turned off the Windows Firewall on machineA.

Any ideas on what I'm doing wrong?

Hi Michael,

By default network protocols (TCP/IP, named pipe) are disabled for SQL Server Express Editions.

You can use SQL Server configuration manager tool to Enable the server protocols on which the instance of SQL Server will listen.

hope this is helpful...

thanks..
Rajesh|||Does this help:

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx|||Thanks Euan -- That did the trick!|||Hi,

I have the same problem, I cannot connect remotely to sql server express

I have done all the steps in that article http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

but still not working remotely although locally is ok

I have tried also on domain lan and on workgroup lan

On domain, on different client computers with different domain users I received different error messages:

...login failed for user. the user is not asociated with a trusted sql server conenction...
or
...When connecting to SQL Server 2005, this failure may be caused by the

fact that under the default settings SQL Server does not allow remote

connections ... error locating server..
or
...When connecting to SQL Server 2005, this failure may be caused by the

fact that under the default settings SQL Server does not allow remote

connections ... login failed...

I am connectiong to my_machine\SQLEXPRESS with windows authentication, same as on the server machine

I cannot figure out what should be the problem, any help would be apreciated.

Thank you for your time.
Corina

Cannot connect to SQL Express Remotely

I have SQL Express April CTP (from VS Beta 2) installed on machineA, but I cannot connect to it from machineB using the command "sqlcmd -S machineA\sqlexpress -E". This command works just fine from machineA.

I have enabled the TCP/IP protocol for the SQLExpress instance through the configuration manager, and turned off the Windows Firewall on machineA.

Any ideas on what I'm doing wrong?

Hi Michael,

By default network protocols (TCP/IP, named pipe) are disabled for SQL Server Express Editions.

You can use SQL Server configuration manager tool to Enable the server protocols on which the instance of SQL Server will listen.

hope this is helpful...

thanks..
Rajesh|||Does this help:

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx|||Thanks Euan -- That did the trick!|||Hi,

I have the same problem, I cannot connect remotely to sql server express

I have done all the steps in that article http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

but still not working remotely although locally is ok

I have tried also on domain lan and on workgroup lan

On domain, on different client computers with different domain users I received different error messages:

...login failed for user. the user is not asociated with a trusted sql server conenction...
or
...When connecting to SQL Server 2005, this failure may be caused by the

fact that under the default settings SQL Server does not allow remote

connections ... error locating server..
or
...When connecting to SQL Server 2005, this failure may be caused by the

fact that under the default settings SQL Server does not allow remote

connections ... login failed...

I am connectiong to my_machine\SQLEXPRESS with windows authentication, same as on the server machine

I cannot figure out what should be the problem, any help would be apreciated.

Thank you for your time.
Corina