Sql Cannot connect: service

Showing posts with label service. Show all posts

Tuesday, March 27, 2012

Cannot enable the Service Broker

When I try to enable the Service Broker I receive the following error message:
Cannot enable the Service Broker in database "msdb" because the Service
Broker GUID in the database (01F9916A-21C0-4EA1-A555-A9C7A020C718) does not
match the one in sys.databases (A7B475E6-5781-4FC2-B1E0-B0EF17FFE942).
ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 9776)
So as far as I can understand in the MSDB database the Service Broker GUID
is different from the one in the table sys.databases.
Is there a possibility to correct this so I will be able to enable the
Service Broker?
I want to enable it so I can configure the email part on this SQL server.
If the GUID itself can not be changed is there a way to rebuild the MSDB
database?
Thanks for the info,
Limmer
Try:
ALTER DATABASE MSDB SET NEW_BROKER
If SQL Agent is running, you will have to shut it down or this will hang.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Limmer" <Limmer@.discussions.microsoft.com> wrote in message
news:71240018-A03C-4F0D-830A-537F95BBB662@.microsoft.com...
> When I try to enable the Service Broker I receive the following error
> message:
> Cannot enable the Service Broker in database "msdb" because the Service
> Broker GUID in the database (01F9916A-21C0-4EA1-A555-A9C7A020C718) does
> not
> match the one in sys.databases (A7B475E6-5781-4FC2-B1E0-B0EF17FFE942).
> ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 9776)
> So as far as I can understand in the MSDB database the Service Broker GUID
> is different from the one in the table sys.databases.
> Is there a possibility to correct this so I will be able to enable the
> Service Broker?
> I want to enable it so I can configure the email part on this SQL server.
> If the GUID itself can not be changed is there a way to rebuild the MSDB
> database?
> Thanks for the info,
> Limmer
>
|||Thanks Roger,
This did the trick.
Limmer
"Roger Wolter[MSFT]" wrote:

> Try:
> ALTER DATABASE MSDB SET NEW_BROKER
> If SQL Agent is running, you will have to shut it down or this will hang.
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Limmer" <Limmer@.discussions.microsoft.com> wrote in message
> news:71240018-A03C-4F0D-830A-537F95BBB662@.microsoft.com...
>
>

Cannot enable the Service Broker

When I try to enable the Service Broker I receive the following error messag
e:
Cannot enable the Service Broker in database "msdb" because the Service
Broker GUID in the database (01F9916A-21C0-4EA1-A555-A9C7A020C718) does not
match the one in sys.databases (A7B475E6-5781-4FC2-B1E0-B0EF17FFE942).
ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 9776)
So as far as I can understand in the MSDB database the Service Broker GUID
is different from the one in the table sys.databases.
Is there a possibility to correct this so I will be able to enable the
Service Broker?
I want to enable it so I can configure the email part on this SQL server.
If the GUID itself can not be changed is there a way to rebuild the MSDB
database?
Thanks for the info,
LimmerTry:
ALTER DATABASE MSDB SET NEW_BROKER
If SQL Agent is running, you will have to shut it down or this will hang.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Limmer" <Limmer@.discussions.microsoft.com> wrote in message
news:71240018-A03C-4F0D-830A-537F95BBB662@.microsoft.com...
> When I try to enable the Service Broker I receive the following error
> message:
> Cannot enable the Service Broker in database "msdb" because the Service
> Broker GUID in the database (01F9916A-21C0-4EA1-A555-A9C7A020C718) does
> not
> match the one in sys.databases (A7B475E6-5781-4FC2-B1E0-B0EF17FFE942).
> ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 9776)
> So as far as I can understand in the MSDB database the Service Broker GUID
> is different from the one in the table sys.databases.
> Is there a possibility to correct this so I will be able to enable the
> Service Broker?
> I want to enable it so I can configure the email part on this SQL server.
> If the GUID itself can not be changed is there a way to rebuild the MSDB
> database?
> Thanks for the info,
> Limmer
>|||Thanks Roger,
This did the trick.
Limmer
"Roger Wolter[MSFT]" wrote:

> Try:
> ALTER DATABASE MSDB SET NEW_BROKER
> If SQL Agent is running, you will have to shut it down or this will hang.
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Limmer" <Limmer@.discussions.microsoft.com> wrote in message
> news:71240018-A03C-4F0D-830A-537F95BBB662@.microsoft.com...
>
>

Cannot enable the Service Broker

When I try to enable the Service Broker I receive the following error message:
Cannot enable the Service Broker in database "msdb" because the Service
Broker GUID in the database (01F9916A-21C0-4EA1-A555-A9C7A020C718) does not
match the one in sys.databases (A7B475E6-5781-4FC2-B1E0-B0EF17FFE942).
ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 9776)
So as far as I can understand in the MSDB database the Service Broker GUID
is different from the one in the table sys.databases.
Is there a possibility to correct this so I will be able to enable the
Service Broker?
I want to enable it so I can configure the email part on this SQL server.
If the GUID itself can not be changed is there a way to rebuild the MSDB
database?
Thanks for the info,
LimmerTry:
ALTER DATABASE MSDB SET NEW_BROKER
If SQL Agent is running, you will have to shut it down or this will hang.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Limmer" <Limmer@.discussions.microsoft.com> wrote in message
news:71240018-A03C-4F0D-830A-537F95BBB662@.microsoft.com...
> When I try to enable the Service Broker I receive the following error
> message:
> Cannot enable the Service Broker in database "msdb" because the Service
> Broker GUID in the database (01F9916A-21C0-4EA1-A555-A9C7A020C718) does
> not
> match the one in sys.databases (A7B475E6-5781-4FC2-B1E0-B0EF17FFE942).
> ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 9776)
> So as far as I can understand in the MSDB database the Service Broker GUID
> is different from the one in the table sys.databases.
> Is there a possibility to correct this so I will be able to enable the
> Service Broker?
> I want to enable it so I can configure the email part on this SQL server.
> If the GUID itself can not be changed is there a way to rebuild the MSDB
> database?
> Thanks for the info,
> Limmer
>|||Thanks Roger,
This did the trick.
Limmer
"Roger Wolter[MSFT]" wrote:
> Try:
> ALTER DATABASE MSDB SET NEW_BROKER
> If SQL Agent is running, you will have to shut it down or this will hang.
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Limmer" <Limmer@.discussions.microsoft.com> wrote in message
> news:71240018-A03C-4F0D-830A-537F95BBB662@.microsoft.com...
> > When I try to enable the Service Broker I receive the following error
> > message:
> >
> > Cannot enable the Service Broker in database "msdb" because the Service
> > Broker GUID in the database (01F9916A-21C0-4EA1-A555-A9C7A020C718) does
> > not
> > match the one in sys.databases (A7B475E6-5781-4FC2-B1E0-B0EF17FFE942).
> > ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 9776)
> >
> > So as far as I can understand in the MSDB database the Service Broker GUID
> > is different from the one in the table sys.databases.
> > Is there a possibility to correct this so I will be able to enable the
> > Service Broker?
> > I want to enable it so I can configure the email part on this SQL server.
> >
> > If the GUID itself can not be changed is there a way to rebuild the MSDB
> > database?
> >
> > Thanks for the info,
> > Limmer
> >
>
>sql

Cannot enable full text indexing

I also have the 'greyed-out' menu problem for full-text indexing and have
researched every post and article available.
The MSSEARCH service is running, and the SQL Server 2000 is upgraded through
SP3a. The Security tab for the server is set for "SQL Server and Windows"
authentication and the Startup Service Account is "System account". The EM
will not allow a change to "This account ... LocalSystem"--it changes back to
"System account" when the change is made.
I have also tried executing sp_fulltext_database in Query Analyzer and
receive this error:
"Full-Text Search is not installed, or a full-text component cannot be
loaded."
There are no icons for full-text indexing under Support Services in EM and I
am wondering if SQL Server itself needs to be re-installed. I have
re-installed SP3, but have not done a complete SQL Server re-install.
I am running this server under XP Professional on a local machine.
What is missing here?
seeker
can you run this script on your problem machine? Post the results back here.
save this script as a vbs file and run it from the command prompt
You many need to do a cscript.exe //H:CScript if you get a lot of dialog
boxes.
Set objSearchAdmin=CreateObject("MSSearch.Admin")
Set infoNT=CreateObject("WinNTSystemInfo")
wscript.echo"Host Name: "& objSearchAdmin.HostName
objSearchAdmin.HostName =InfoNT.ComputerName
Set objApplications=objSearchAdmin.Applications
wscript.echo "Number of Search Applications serviced by this Build Server: "
& objApplications.Count
for each application in objApplications
wscript.echo "Application Name: " & application.name
next
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"seeker" <seeker@.discussions.microsoft.com> wrote in message
news:75D2062B-0704-4847-9388-0D16866D9AF0@.microsoft.com...
> I also have the 'greyed-out' menu problem for full-text indexing and have
> researched every post and article available.
> The MSSEARCH service is running, and the SQL Server 2000 is upgraded
through
> SP3a. The Security tab for the server is set for "SQL Server and Windows"
> authentication and the Startup Service Account is "System account". The
EM
> will not allow a change to "This account ... LocalSystem"--it changes back
to
> "System account" when the change is made.
> I have also tried executing sp_fulltext_database in Query Analyzer and
> receive this error:
> "Full-Text Search is not installed, or a full-text component cannot be
> loaded."
> There are no icons for full-text indexing under Support Services in EM and
I
> am wondering if SQL Server itself needs to be re-installed. I have
> re-installed SP3, but have not done a complete SQL Server re-install.
> I am running this server under XP Professional on a local machine.
> What is missing here?
> --
> seeker
|||Thank you for your note, Hilary.
I resolved this problem with a re-install of SQL Server (Developer). When I
put in the CD, I discovered that it had been installed with Full Text
Indexing unchecked, which is the default.
I took it all the way through the SP3 update, and at the point the Full Text
Indexing selection was enabled.
"Hilary Cotter" wrote:

> can you run this script on your problem machine? Post the results back here.
> save this script as a vbs file and run it from the command prompt
> You many need to do a cscript.exe //H:CScript if you get a lot of dialog
> boxes.
> Set objSearchAdmin=CreateObject("MSSearch.Admin")
> Set infoNT=CreateObject("WinNTSystemInfo")
> wscript.echo"Host Name: "& objSearchAdmin.HostName
> objSearchAdmin.HostName =InfoNT.ComputerName
> Set objApplications=objSearchAdmin.Applications
> wscript.echo "Number of Search Applications serviced by this Build Server: "
> & objApplications.Count
> for each application in objApplications
> wscript.echo "Application Name: " & application.name
> next
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "seeker" <seeker@.discussions.microsoft.com> wrote in message
> news:75D2062B-0704-4847-9388-0D16866D9AF0@.microsoft.com...
> through
> EM
> to
> I
>
>
sql

Sunday, March 25, 2012

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;

drop service 'myservicename'

Cannot display database properties windows in Sql server management studio.

I use sql server 2005 developer edition with service pack 1.

When i right click on a database and i select properties an error occured with the folowing stack trace

===================================

Cannot show requested dialog.

===================================

Cannot show requested dialog. (SqlMgmt)

Program Location:

at Microsoft.SqlServer.Management.SqlMgmt.DefaultLaunchFormHostedControlAllocator.AllocateDialog(XmlDocument initializationXml, IServiceProvider dialogServiceProvider, CDataContainer dc)
at Microsoft.SqlServer.Management.SqlMgmt.DefaultLaunchFormHostedControlAllocator.Microsoft.SqlServer.Management.SqlMgmt.ILaunchFormHostedControlAllocator.CreateDialog(XmlDocument initializationXml, IServiceProvider dialogServiceProvider)
at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.InitializeForm(XmlDocument doc, IServiceProvider provider, ISqlControlCollection control)
at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm..ctor(XmlDocument doc, IServiceProvider provider)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ToolsMenuItem.OnCreateAndShowForm(IServiceProvider sp, XmlDocument doc)
at Microsoft.SqlServer.Management.SqlMgmt.RunningFormsTable.RunningFormsTableImpl.ThreadStarter.StartThread()

===================================

Object reference not set to an instance of an object. (System.Data)

Program Location:

at System.Data.SqlClient.TdsParserStateObject.ReadStringWithEncoding(Int32 length, Encoding encoding, Boolean isPlp)
at System.Data.SqlClient.TdsParser.ReadSqlStringValue(SqlBuffer value, Byte type, Int32 length, Encoding encoding, Boolean isPlp, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.ReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ReadColumnData()
at System.Data.SqlClient.SqlDataReader.ReadColumn(Int32 i, Boolean setTimeout)
at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i)
at System.Data.SqlClient.SqlDataReader.GetValues(Object[] values)
at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
at Microsoft.SqlServer.Management.Smo.DatabaseLevel.GetData(EnumResult res)
at Microsoft.SqlServer.Management.Smo.Environment.GetData()
at Microsoft.SqlServer.Management.Smo.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Smo.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request req)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetInitDataReader(String[] fields, OrderBy[] orderby)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ImplInitialize(String[] fields, OrderBy[] orderby)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Initialize(Boolean allProperties)
at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.GetObjectByKey(ObjectKeyBase key)
at Microsoft.SqlServer.Management.Smo.DatabaseCollection.get_Item(String name)
at Microsoft.SqlServer.Management.SqlManagerUI.CreateDatabaseData.DatabasePrototype.DatabaseData..ctor(CDataContainer context, String databaseName)
at Microsoft.SqlServer.Management.SqlManagerUI.CreateDatabaseData.DatabasePrototype.LoadDefinition(String newName)
at Microsoft.SqlServer.Management.SqlManagerUI.CreateDatabaseData.DatabasePrototype..ctor(CDataContainer context)
at Microsoft.SqlServer.Management.SqlManagerUI.DBPropSheet..ctor(CDataContainer context)

Accroding to the reflected sources:

public DatabaseData(CDataContainer context, string databaseName)
{
this.mirrorSafetyLevel = MirroringSafetyLevel.Off;
this.witnessServer = string.Empty;
Database database1 = context.Server.Databases[databaseName];

There might be a problem in getting the information from the database collection. So do the following steps:

-Run the profiler to the when the execution of the command stops. (Guess it has to do something with the database name)
-Select the database name from the sysdatabases and post it here

SELECT Name, DATALENGTH(Name),LEN(Name) from sys.databases

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

When i try to create a new view, in any of the databases i saw an error

Object reference not set to an instance of an object. (SQLEditors)

Program Location:

at System.Data.SqlClient.TdsParserStateObject.ReadStringWithEncoding(Int32 length, Encoding encoding, Boolean isPlp)
at System.Data.SqlClient.TdsParser.ReadSqlStringValue(SqlBuffer value, Byte type, Int32 length, Encoding encoding, Boolean isPlp, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.ReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ReadColumnData()
at System.Data.SqlClient.SqlDataReader.ReadColumn(Int32 i, Boolean setTimeout)
at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i)
at System.Data.SqlClient.SqlDataReader.GetValues(Object[] values)
at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
at Microsoft.SqlServer.Management.Smo.DatabaseLevel.GetData(EnumResult res)
at Microsoft.SqlServer.Management.Smo.Environment.GetData()
at Microsoft.SqlServer.Management.Smo.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Smo.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request req)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetInitDataReader(String[] fields, OrderBy[] orderby)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ImplInitialize(String[] fields, OrderBy[] orderby)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.OnPropertyMissing(String propname)
at Microsoft.SqlServer.Management.Smo.Database.get_DefaultSchema()
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.GetDefaultSchema(Server server, String databaseName)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.GenerateNewObjectUrn()
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.SetObjectAndParentUrns(Urn originalUrn)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProjectNode..ctor(Urn urn, DocumentOptions options, IManagedConnection connection)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProjectNode.Allocate(Urn origUrn, DocumentType editorType, DocumentOptions options, IManagedConnection connection)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProject.Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.CreateDesignerWindow(IManagedConnection mc, DocumentOptions options)

I think that there is a problem with the server installation. I will try to reinstalle it.

|||If you want to solve the problem, follow the mentioned steps to reproduce the executed script on the server. That might also help others to solve their problems and help to improve the product itself.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi,

Please make sure the database exists and is not deleted by clicking refresh on the server node and see if you can still see the database whose properties you were not able to access. I believe the database was dropped by some other means and your SSMS window was not refreshed after that.

Hope this helps.

Thanks,

Sravanthi.

|||Stack trace dump means there might be a probelm with the Windows, a virus or mismatch of hotfix/service pack on operating system. Make sure to check what has been changed since this was working correctly in previous state, if not you might try testing the same on other machine.|||All databases are in place. i can open tables and see their data.|||

thats what i believe too.

The problem appears after an update from microsoft windows update which find that my windows sql server installation need the service pack 1 update. I selected and install it.

after that i download and install service pack 2 for sql server 2005 but of course this doesn't correct anything.

I have in the same computer a sqlserver express edition installed and from microsoft sql server managment studio i can work properly with this instance without a problem (in case i thought that was a problem from microsoft sql server managment studio).

About response from Jens K. Suessmeyer .

Whene i execute the line

SELECT 'AdventureWorks', DATALENGTH('AdventureWorks'),LEN('AdventureWorks') from sys.databases

i get

'An error occurred while executing batch. Error message is: Object reference not set to an instance of an object.'

with any database there are in this installation.

|||

Hi,

From where are you running the queries? Please run the query SELECT Name, DATALENGTH(Name),LEN(Name) from sys.databases (run it as is as Jens K. Suessmeyer has given, dont replace Name in the query, if you want it specific to AdventureWorks just add a where clause). Run this query from new query window in SSMS and let us know the output. From the error that you are getting "Object reference..." looks like you are trying to run the query programmatically. Just run it from a SSMS query window and let us know the output. Make sure the database that is causing all these issues comes up in the query result.

Sravanthi

|||If you believe its SSMS tools problem, try to reinstall them again.|||

All databases causing that issues

The result after executing the above query is

NAME (no column name) (no column name)

master 12 6
tempdb 12 6
model 10 5
msdb 8 4
ReportServer$MAIMOY2005 46 23
ReportServer$MAIMOY2005TempDB 58 29
BASE DE DATOS ORIGINAL 44 22
ALKI 8 4
aspnetdb_ALKI 26 13
AdventureWorks 28 14
AdventureWorksDW 32 16

(11 row(s) affected)

|||After a full uninstall and reinstall everything seems to work perfect. Aftes installation i install also service pack 2 downloaded and installed locally and everything works properly.|||

somehow, i missed this thread and I am sure if I could furnish this information bit earlier it would have been helpful. nevertheless, i think i should share my experience in this regards. the story is as follows :)

One of our development server had the same problem and I have documented this error. But at that time I was on the tows and somehow I was to get rid of this problem and I did the same trick - reinstalling the SQL Server. But I was not satisfied by this solution. when I did the postmortem of the process then I realized that our TL used to synchronies the Development database from Visio. There were many connection used to connect to different database (from visio) and one of them was to connect to master database. He used the master connection , and Visio automatically detects the objects in the connected database which are not there in the Model and it ask whether u want to delete those object or not. He selected Yes and Visio deleted all the objects from master database which are not there in the model. I verified the objects between two instances Master databases. There were five system tables missing , the missing tables were spt_fallback_db,spt_fallback_dev,spt_fallback_usg,spt_monitor,spt_value. Then I created the script of these tables from other instance and run on the problem server, but those tables were not having owner , though it shows owner as DBO. Actually these tables comes under System Tables tree but when I created these by the script those created as user table. I was pretty sure that these problem were because of these tables got deleted. But I was not having time to do R&D on this and I reinstalled the instance.

(a) How come Visio able to delete system tables (the irony is that , in SSMO these tables are shown as System Tables , but if u use sp_help it is shown as Usertable).

(b) IF somehow these tables got deleted, how can we restore these table and revert back to normal stage without reinstalling anything.

Also question to Antonisk, is something like this was happened in your side…

I think we need to dig out the root of this problem. If these tables are so critical , then these should not be deletable from anywhere. If it is a bug the we need to report this to MS..

Thanks for the time

Madhu

|||I don't use Visio at all. Also i can't check if this was the problem (system tables missing) because I reinstall the Sql Server.

Cannot display database properties windows in Sql server management studio.

I use sql server 2005 developer edition with service pack 1.

When i right click on a database and i select properties an error occured with the folowing stack trace

===================================

Cannot show requested dialog.

===================================

Cannot show requested dialog. (SqlMgmt)

Program Location:

===================================

Object reference not set to an instance of an object. (System.Data)

Program Location:

Accroding to the reflected sources:

There might be a problem in getting the information from the database collection. So do the following steps:

-Run the profiler to the when the execution of the command stops. (Guess it has to do something with the database name)
-Select the database name from the sysdatabases and post it here

SELECT Name, DATALENGTH(Name),LEN(Name) from sys.databases

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

When i try to create a new view, in any of the databases i saw an error

Object reference not set to an instance of an object. (SQLEditors)

Program Location:

at System.Data.SqlClient.TdsParserStateObject.ReadStringWithEncoding(Int32 length, Encoding encoding, Boolean isPlp)
at System.Data.SqlClient.TdsParser.ReadSqlStringValue(SqlBuffer value, Byte type, Int32 length, Encoding encoding, Boolean isPlp, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.ReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ReadColumnData()
at System.Data.SqlClient.SqlDataReader.ReadColumn(Int32 i, Boolean setTimeout)
at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i)
at System.Data.SqlClient.SqlDataReader.GetValues(Object[] values)
at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
at Microsoft.SqlServer.Management.Smo.DatabaseLevel.GetData(EnumResult res)
at Microsoft.SqlServer.Management.Smo.Environment.GetData()
at Microsoft.SqlServer.Management.Smo.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Smo.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request req)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetInitDataReader(String[] fields, OrderBy[] orderby)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ImplInitialize(String[] fields, OrderBy[] orderby)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.OnPropertyMissing(String propname)
at Microsoft.SqlServer.Management.Smo.Database.get_DefaultSchema()
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.GetDefaultSchema(Server server, String databaseName)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.GenerateNewObjectUrn()
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.SetObjectAndParentUrns(Urn originalUrn)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProjectNode..ctor(Urn urn, DocumentOptions options, IManagedConnection connection)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProjectNode.Allocate(Urn origUrn, DocumentType editorType, DocumentOptions options, IManagedConnection connection)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProject.Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.CreateDesignerWindow(IManagedConnection mc, DocumentOptions options)

I think that there is a problem with the server installation. I will try to reinstalle it.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi,

Hope this helps.

Thanks,

Sravanthi.

thats what i believe too.

The problem appears after an update from microsoft windows update which find that my windows sql server installation need the service pack 1 update. I selected and install it.

after that i download and install service pack 2 for sql server 2005 but of course this doesn't correct anything.

About response from Jens K. Suessmeyer .

Whene i execute the line

SELECT 'AdventureWorks', DATALENGTH('AdventureWorks'),LEN('AdventureWorks') from sys.databases

i get

'An error occurred while executing batch. Error message is: Object reference not set to an instance of an object.'

with any database there are in this installation.

|||

Hi,

Sravanthi

|||If you believe its SSMS tools problem, try to reinstall them again.|||

All databases causing that issues

The result after executing the above query is

NAME (no column name) (no column name)

(11 row(s) affected)

|||After a full uninstall and reinstall everything seems to work perfect. Aftes installation i install also service pack 2 downloaded and installed locally and everything works properly.|||

(a) How come Visio able to delete system tables (the irony is that , in SSMO these tables are shown as System Tables , but if u use sp_help it is shown as Usertable).

(b) IF somehow these tables got deleted, how can we restore these table and revert back to normal stage without reinstalling anything.

Also question to Antonisk, is something like this was happened in your side…

I think we need to dig out the root of this problem. If these tables are so critical , then these should not be deletable from anywhere. If it is a bug the we need to report this to MS..

Thanks for the time

Madhu

|||I don't use Visio at all. Also i can't check if this was the problem (system tables missing) because I reinstall the Sql Server.sql

Thursday, March 22, 2012

Cannot Deploy Report - Please Help

I am trying to set up Reporting Services. I am using SQL Server 2005 on Win XP Pro.

I installed service pack 2 for SQL Server 2005. The database looks to be set up in the configuration manager. When I try to deploy the report I get the following message:

A connection could not be made to the report server http://localhost/ReportServer

When trying to open the ReportServer folder in IIS I get another message:

An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
Exception has been thrown by the target of an invocation.
Could not load file or assembly 'Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' or one of its dependencies. The system cannot find the file specified.

I am not sure what SharePoint is and why I need it. I am just trying to set up simple reports on a local host SQL database and IIS on a standalone PC.

Any help appreciated.

Thanks

Hi Troy23

I don't know if this will help but change the http://localhost/reportserver to http://ipaddresss/reportserver or http://servername/reportserver.

Hope it helps,

Kind Regards,

Joos Nieuwoudt

|||

What is the value for "Server Mode" in the RS Config. tool's "Database Setup" tab?

thanx,
Prash

|||

Prash

Many thanks for your reply. I had the check box for 'SharePoint' ticked. I unticked it and set a few permissions and it worked.

I would like to ask the difference between writing a simple report with report builder and using the report model. I presume there is more to it than the fact it can be utilised on a web server.

Thanks

|||Hi, Your IIS is telling you, or certainly think you have sharepoint installed and you installation of Reportsserver is no longer valid, we sometimes get corruption sin installs, your best bet is remove all instances of Sharepoint, If you can find it? if not dont worry just go to SQL install and remove all instances of report server and then re-install from scratch, this will sort out your issues, Make sure you do a proper reboot in between remove and re-install.

Andy|||Report Builder reports must be written against a report model. You cannot use Report Builder directly against a SQL database (for example). You must create a model for the database. You can do so in Report Manager, in Business Intelligence Development Studio or in Management Studio. Hope this answers your question.

Cannot Deploy Report - Please Help

I am trying to set up Reporting Services. I am using SQL Server 2005 on Win XP Pro.

I installed service pack 2 for SQL Server 2005. The database looks to be set up in the configuration manager. When I try to deploy the report I get the following message:

A connection could not be made to the report server http://localhost/ReportServer

When trying to open the ReportServer folder in IIS I get another message:

I am not sure what SharePoint is and why I need it. I am just trying to set up simple reports on a local host SQL database and IIS on a standalone PC.

Any help appreciated.

Thanks

Hi Troy23

I don't know if this will help but change the http://localhost/reportserver to http://ipaddresss/reportserver or http://servername/reportserver.

Hope it helps,

Kind Regards,

Joos Nieuwoudt

|||

What is the value for "Server Mode" in the RS Config. tool's "Database Setup" tab?

thanx,
Prash

|||

Prash

Many thanks for your reply. I had the check box for 'SharePoint' ticked. I unticked it and set a few permissions and it worked.

I would like to ask the difference between writing a simple report with report builder and using the report model. I presume there is more to it than the fact it can be utilised on a web server.

Thanks

cannot delete publication

We are using SQL Server 2005 Service Pack 2 on WIn2003.
We lost our distributor and I reinstall SQL Server in Distributor machine.
Now I am not able to delete publication and subscription from publisher and
subscriber.
Any help highly appreciated.
tried
sp_removedbreplication
Msg 3724, Level 16, State 2, Procedure sp_MSdrop_peertopeer_tables, Line 27
Cannot drop the table 'dbo.MSpeer_lsns' because it is being used for
replication.
Thanks
Or I get following error
OLE DB provider "SQLNCLI" for linked server "repl_distributor" returned
message "Communication link failure".
Msg 233, Level 16, State 1, Line 0
Named Pipes Provider: No process is on the other end of the pipe.
"Sandeep Shankar" <sandeeps@.dntg.net> wrote in message
news:%23t%23jvCv0HHA.4236@.TK2MSFTNGP06.phx.gbl...
> We are using SQL Server 2005 Service Pack 2 on WIn2003.
> We lost our distributor and I reinstall SQL Server in Distributor machine.
> Now I am not able to delete publication and subscription from publisher
and
> subscriber.
> Any help highly appreciated.
>
> tried
> sp_removedbreplication
> Msg 3724, Level 16, State 2, Procedure sp_MSdrop_peertopeer_tables, Line
27
> Cannot drop the table 'dbo.MSpeer_lsns' because it is being used for
> replication.
>
> Thanks
>
|||set the ignore_distributor =1 parameter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Sandeep Shankar" <sandeeps@.dntg.net> wrote in message
news:%234MijUv0HHA.4004@.TK2MSFTNGP05.phx.gbl...
> Or I get following error
> OLE DB provider "SQLNCLI" for linked server "repl_distributor" returned
> message "Communication link failure".
> Msg 233, Level 16, State 1, Line 0
> Named Pipes Provider: No process is on the other end of the pipe.
>
>
>
>
> "Sandeep Shankar" <sandeeps@.dntg.net> wrote in message
> news:%23t%23jvCv0HHA.4236@.TK2MSFTNGP06.phx.gbl...
> and
> 27
>
|||I did put this parameter yesterday, but Now when I refresh 'Local
Publication' I get ' Failed to retrive data for this request.'
More info
A exception occurred while executing a transact- sql statement or batch
Lock request time out period exceeded
Thanks
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OPzWNA30HHA.4184@.TK2MSFTNGP06.phx.gbl...[vbcol=seagreen]
> set the ignore_distributor =1 parameter
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Sandeep Shankar" <sandeeps@.dntg.net> wrote in message
> news:%234MijUv0HHA.4004@.TK2MSFTNGP05.phx.gbl...
Line
>
|||You are getting some deadlocking occurring. Run dbcc opentran and see if
there are open transaction. evaluate whether you can kill them. Close down
all instance of EM, stop the agents and try again.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Sandeep Shankar" <sandeeps@.dntg.net> wrote in message
news:ez9U3y40HHA.4652@.TK2MSFTNGP05.phx.gbl...
>I did put this parameter yesterday, but Now when I refresh 'Local
> Publication' I get ' Failed to retrive data for this request.'
> More info
> A exception occurred while executing a transact- sql statement or batch
> Lock request time out period exceeded
> Thanks
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OPzWNA30HHA.4184@.TK2MSFTNGP06.phx.gbl...
> Line
>

Cannot delete maintenance plan.

Cannot delete a maintenance plan. SQL 2005 patched service pack 2. (That
means I was dumb enough to apply service pack 2 immediately and then
microsoft release a patch a couple days later because spk2 was broken)
The DELETE statement conflicted with the REFERENCE constraint
"FK_subplan_job_id". The conflict occurred in database "msdb", table
"dbo.sysmaintplan_subplans", column...
--Apparently the job you try to delete has one or more subplans that (for some
reason) should be deleted apart.
Try deleting the single subplans before deleting the Maintenance Plan itself.
Each Subplan has foreign keys referencing the sysjobs table, containing the
definition of the Plan.
I cannot define the exact cause of your issue because i don't know the
complete history of the plan, his subplans, the schedules etc.
Look in msdb at the system tables to undertand the relationships between the
various table but DON'T TRY to operate updates directly over them!
Gilberto Zampatti
"SQLdba" wrote:
> Cannot delete a maintenance plan. SQL 2005 patched service pack 2. (That
> means I was dumb enough to apply service pack 2 immediately and then
> microsoft release a patch a couple days later because spk2 was broken)
> The DELETE statement conflicted with the REFERENCE constraint
> "FK_subplan_job_id". The conflict occurred in database "msdb", table
> "dbo.sysmaintplan_subplans", column...
>
> --
>

Tuesday, March 20, 2012

Cannot delete maintenance plan.

Cannot delete a maintenance plan. SQL 2005 patched service pack 2. (That
means I was dumb enough to apply service pack 2 immediately and then
microsoft release a patch a couple days later because spk2 was broken)
The DELETE statement conflicted with the REFERENCE constraint
"FK_subplan_job_id". The conflict occurred in database "msdb", table
"dbo.sysmaintplan_subplans", column...

Apparently the job you try to delete has one or more subplans that (for some
reason) should be deleted apart.
Try deleting the single subplans before deleting the Maintenance Plan itself.
Each Subplan has foreign keys referencing the sysjobs table, containing the
definition of the Plan.
I cannot define the exact cause of your issue because i don't know the
complete history of the plan, his subplans, the schedules etc.
Look in msdb at the system tables to undertand the relationships between the
various table but DON'T TRY to operate updates directly over them!
Gilberto Zampatti
"SQLdba" wrote:

> Cannot delete a maintenance plan. SQL 2005 patched service pack 2. (That
> means I was dumb enough to apply service pack 2 immediately and then
> microsoft release a patch a couple days later because spk2 was broken)
> The DELETE statement conflicted with the REFERENCE constraint
> "FK_subplan_job_id". The conflict occurred in database "msdb", table
> "dbo.sysmaintplan_subplans", column...
>
> --
>

Tuesday, March 27, 2012

Cannot enable the Service Broker

Cannot enable the Service Broker

Cannot enable the Service Broker

Cannot enable full text indexing

Sunday, March 25, 2012

Cannot DROP SERVICE

Cannot display database properties windows in Sql server management studio.

Cannot display database properties windows in Sql server management studio.

Thursday, March 22, 2012

Cannot Deploy Report - Please Help

Cannot Deploy Report - Please Help

cannot delete publication

Cannot delete maintenance plan.

Tuesday, March 20, 2012

Cannot delete maintenance plan.

Sql Cannot connect

Blog Archive

About Me