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 Create Subscription

Using RS Standard, SP2, I am prevented from creating a subscription on
reports that I am able to schedule for snapshots. The error message says
"Subscriptions cannot be created because the credentials used to run the
report are not stored, the report is using user-defined parameter values, or
if a linked report, the report is no longer valid." There are no linked
reports involved, and there are both report parameters and query parameters,
but all have supplied values or defaults. (Since a snapshot works fine, it
would seem that criteria for a subscription would be satisfied). This is a
major problem for my users who believe subscription delivery is an essential
feature. Please help!
--
Bob RogersThen the only part of the error message your response didn't cover is the
credentials... They must be stored securely in the database... Go to the
datasource, and you may set the connection stuff to be stored in the
database... Then subscriptions can be made.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bob Rogers" <BobRogers@.discussions.microsoft.com> wrote in message
news:1D97E909-7A7A-4617-ABF0-0610CF43B863@.microsoft.com...
> Using RS Standard, SP2, I am prevented from creating a subscription on
> reports that I am able to schedule for snapshots. The error message says
> "Subscriptions cannot be created because the credentials used to run the
> report are not stored, the report is using user-defined parameter values,
> or
> if a linked report, the report is no longer valid." There are no linked
> reports involved, and there are both report parameters and query
> parameters,
> but all have supplied values or defaults. (Since a snapshot works fine,
> it
> would seem that criteria for a subscription would be satisfied). This is
> a
> major problem for my users who believe subscription delivery is an
> essential
> feature. Please help!
> --
> Bob Rogers|||Thanks for your reply. The database credentials were good; the problem
turned out to be user id being included in the template for report footer --
user id is apparently considered a parameter.
--
Bob Rogers
"Wayne Snyder" wrote:
> Then the only part of the error message your response didn't cover is the
> credentials... They must be stored securely in the database... Go to the
> datasource, and you may set the connection stuff to be stored in the
> database... Then subscriptions can be made.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Bob Rogers" <BobRogers@.discussions.microsoft.com> wrote in message
> news:1D97E909-7A7A-4617-ABF0-0610CF43B863@.microsoft.com...
> > Using RS Standard, SP2, I am prevented from creating a subscription on
> > reports that I am able to schedule for snapshots. The error message says
> > "Subscriptions cannot be created because the credentials used to run the
> > report are not stored, the report is using user-defined parameter values,
> > or
> > if a linked report, the report is no longer valid." There are no linked
> > reports involved, and there are both report parameters and query
> > parameters,
> > but all have supplied values or defaults. (Since a snapshot works fine,
> > it
> > would seem that criteria for a subscription would be satisfied). This is
> > a
> > major problem for my users who believe subscription delivery is an
> > essential
> > feature. Please help!
> > --
> > Bob Rogers
>
>

Cannot create databases (and other issues) from Management Studio

I seem to be having a number of problems using managment studio (MS) against my locally installed SQL 2005 Standard Edition. E.g. when I create a default database using MS it says...

TITLE: Microsoft SQL Server Management Studio
----------

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click:http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

----------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

----------

The server could not load DCOM. (Microsoft SQL Server, Error: 7404)

For help, click:http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=7404&LinkId=20476

However, the hyperlinks are less than helpful. I also get problems doing various maintance work from MS. I don't think its a database engine problem 'cause I can do all the work via TSQL DDL but the interface is so much easier to use (or should be). BTW it's running on XP Pro (firewall problem?).

From the Event Log...What does it mean?

Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17102
Date: 06/02/2006
Time: 20:12:06
User: N/A
Computer: TITAN
Description:
Failed to initialize Distributed COM (CoInitializeEx returned 80010119). Heterogeneous queries and remote procedure calls are disabled. Check the DCOM configuration using Component Services in Control Panel.

|||Finally discovered it was the nVidia (motherboard) firewall client, you have to uninstall it - I had a similar problem with IE7

Cannot connect to to the other server when setting up replication in SQL 2005

Im trying to setup replication between two 2005 standard editions. They are on the same LAN and installed equally + SP1. They are installed as named instances (with the same instance name MSSQL01). Remote access is allowed and TCP/IP and shared memory and I can connect between them.

SQL1\MSSQL01 is configured as the publisher & distributor and the snapshot is done. When Im using the subscriber wizard from SQL1 to create the subscriptions and add SQL2\MSSQL01 as a subscriber the connection fails to the remote server.

The error message is:

TITLE: Connect to Server

Cannot connect to SQL2\MSSQL01.

ADDITIONAL INFORMATION:

Failed to connect to server SQL2\MSSQL01. (Microsoft.SqlServer.ConnectionInfo)

An error has occurred while establishing a connection to the server. 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. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

When I do a manual connection to SQL2 using the server managemnet studio on SQL1 I cannot connect using the instance name e.g. SQL2\MSSQL01, it works only when only using the server name e.g. SQL2. Then I have full access both when using a windows or SQL user.

When trying with using the server name only when setting up the subscription, I got a error saying that I need to use the full name.

TITLE: Connect to Server

Cannot connect to TFMHQARNDE07.

ADDITIONAL INFORMATION:

SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, ' SQL2\MSSQL01'. (Replication.Utilities)

I can also mention that theese servers are cloned from another server and I needed to drop the old servername and add the new servername (sp_addserver) on both servers to get the publisher to work.

When I try to setup the subscription from SQL2 and add SQL1 as a publisher I get the same error.

Any one have any idea what it can be?

I found the problem. I didnt have the SQL Browser service runnning which looks to be needed when using instances. After starting it, it works to connect.

cannot connect to sql 2005 from isql

I have a sql 2005 server runs on Windows 2003 Standard OS. On the same
server, I can use sqlcmd to connect to the database. However, I failed
to connect to the database from a client machine using sql7's isql. The
error was
DB-Library: Unable to connect: SQL Server is unavailable or does not
exist.
Net-Library error 1326: ConnectionOpen (CreateFile()).
Please advise, thanks.Is the server instance listening to Named Pipes?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AH" <hhhsu7a@.yahoo.com> wrote in message
news:1167102465.643393.154420@.73g2000cwn.googlegroups.com...
>I have a sql 2005 server runs on Windows 2003 Standard OS. On the same
> server, I can use sqlcmd to connect to the database. However, I failed
> to connect to the database from a client machine using sql7's isql. The
> error was
> DB-Library: Unable to connect: SQL Server is unavailable or does not
> exist.
> Net-Library error 1326: ConnectionOpen (CreateFile()).
> Please advise, thanks.
>|||Hi
If this is SQL Express then network protocols are disabled by default. Check
which network protocols are being used, if necessary you may wish to create
an alias on the client to use TCP/IP to connect to the instance. Also make
sure that the client machine has the latest service packs.
John
"AH" wrote:
> I have a sql 2005 server runs on Windows 2003 Standard OS. On the same
> server, I can use sqlcmd to connect to the database. However, I failed
> to connect to the database from a client machine using sql7's isql. The
> error was
> DB-Library: Unable to connect: SQL Server is unavailable or does not
> exist.
> Net-Library error 1326: ConnectionOpen (CreateFile()).
> Please advise, thanks.
>|||John,
I tried to open TCP on the server but still got the same error when
connecting from client machine. The client has SQL 7 SP2. Do I need to
go to SP 4?
Does Microsoft provide any tool to trace the connection?
Thanks,
Andy
John Bell wrote:
> Hi
> If this is SQL Express then network protocols are disabled by default. Check
> which network protocols are being used, if necessary you may wish to create
> an alias on the client to use TCP/IP to connect to the instance. Also make
> sure that the client machine has the latest service packs.
> John
>
> "AH" wrote:
> > I have a sql 2005 server runs on Windows 2003 Standard OS. On the same
> > server, I can use sqlcmd to connect to the database. However, I failed
> > to connect to the database from a client machine using sql7's isql. The
> > error was
> > DB-Library: Unable to connect: SQL Server is unavailable or does not
> > exist.
> > Net-Library error 1326: ConnectionOpen (CreateFile()).
> >
> > Please advise, thanks.
> >
> >

Cannot connect to MSSAS 2005 Standard on local machine

I have installed SQL2005 completely, including Service Pack 1. Using Mgt Studio, I can connect to the relational db. I am not able to connect to analysis services though! The error message I receive is :"A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient". The additional information says: "Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.(System)."

The service is certainly running (I see this in the Mgt Studio).

I am able to connect to the service on another machine. However, once I try to browse a cube on another machine, I get the error message "Error HRESULT E_FAIL has been returned from a call to a COM component". I do not know whether these 2 problems are related or not.

Thanks a lot for your answer!

Joos

These two problems are probably unrelated.

Try and see if your "SQL Server Analysis Services(MSSQLSERVER)" service is running?

Try using "localhost" as a name when connecting to your local Analysis Server.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Analysis Services is running. I can see this under my registered servers and in services. I tried both localhost as machine name, without any luck. It is the standard edition on my XP laptop.|||What antivirus do you have? I have the same problem and it is because the antivirus is making some kind of blockage|||

I have panda running on my laptop. I also have a server (2003) with panda and SQL Enterprise installed. There it works fine...What do you suggest?

Thanks!

|||

Try uninstalling your untivirus software.

It has been reported (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=384104&SiteID=1) Panda will cause connection problems in some cases.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||

No more thinking about it!!!!

You have panda

I have panda

You have the problem

I have the problem

MICROOOOOOOOOOSOOOOOOOOOOOOOOOFFFFFFFTTTTTT!!!!!!!!! AND PANDAAAAAAAAAAAAA!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

REAAAAAAAAAAADDDDDDDDDDDDDDDD!!!!!!!!!!!!!!!!!!!!!!!!!!

DOOOOOOO SOMENTHINGGGGGGGGGGGGGGGGGGGGG!!!!!

|||

Jose is absolutely right!!! SQL2005 Standard edition on Windows XP with Panda Antivirus is a bad idea if you want to use Analysis Services!!!!

I removed Panda, restarted the pc and then it worked!

Thanks a lot Jose!

|||

You are very lucky because you can remove panda!!

I can't because i work for a big company and i can't remove panda antivirus

Please!!!!!!!!! Microsoft !!!!!!!! are you reading?

Please do something!!!

Panda!!!! this is not a good idea to block a Microsoft aplicattion!!! do something!!

fix it!!!

is it posible? the best supposed antivirus in the world blocking an aplication of Microsoft? Didn't you speak each other before making your products or wath?

to all people with the same problem (there are at least 10 questions posted whit this problem): try to uninstall your antivirus (specially if it's panda) and try again

|||

I'm running latest Panda Antivirus build "Titanium 2006 Antivirus + Antispyware " with no issue on my XPSP2 with AS 2005 SP1.

Which build do you use?

thx

Aus99

|||

I'm not sure. What i know is that it's a corporate version with true prevent.

Anybody with the same problem?

Cannot connect SQL 2005 with OLE DB Provider for SQL Server

Cannot connect SQL 2005 with OLE DB Provider for SQL Server

MSSQL 2000 Utilities & MSDE was uninstalled.
After installation SQL 2005 Standard Full all connections was OK (tested).
Aftert restarting the computer I cannot connect to MSSQL with OLE DB Provider for SQL Server.
I try to connect from Delphi 5 SP2 (ADOConnection) and from MS Access 2003 SP2.

Error "SQL Server denied or does not exists or access denied"

Versions:
Windows Xp Pro SP 2
ADO/MDAC: 2.81.1117.0 (xpsp_sp2_rtm.040803-2158)
Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

Connection sting:
Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=pwd;Data Source=GMSAlexHome;Initial Catalog=GMSSample;Application Name="GMS Office Tools Service Pack"

Question: how to connect to SQL 2005 with SQL DB Provider.
All old applications does not work.

On local machine when ServerName = 'localhost' or '.' - all OK.
When using SQL server name - error.
Before rebooting all was OK.

Thanks in advance.

If you try to connect from the local PC and it works fine, but does not work from the network then most likely SQL Server is not enabled to accept external network requests. Also if your server name contains instance name, then it also suppose to be specified in your connection string, like servername\instancename|||

On local computer connection avaible only as <localhost> and <.>.

There is no instance name and other sql servers.

SQLSRV Works more than 3 times slower than MSDE 2000 on small test DB (about 100 mb).

VERY many bugs in tools and very slow.... $(((

What comps in MS? I have P4 3MHz 1 Gb SATA - VERY slow.

Sory for digression.

|||

Do you compare performance based on exact same data and structure? If your indexes are not the same as in MSDE 2000 then you cannot compare performance.

About bugs. If you have any, you could report them here. If you do this and they really exist, I believe, it will help to improve product

|||

As Val mentioned, please double check using "SQL Server Configuration Manager" tool that your SQL instance has remote TCP and/or Named Pipe support enabled.

Also, please make sure that the Windows Firewall is not blocking the ports used by SQL Server. You can explicitly add TCP port 1433 to the exception list or you can add Sqlservr.exe program to the exception list (to ensure both TCP and Named Pipes are not blocked).

Jimmy Wu