1

Topic: MS a SQL Server 2016 and Service Broker!

There was such problem:
There was an error in a cache of events of the timer. An error 2905, a state 2.
The code: 9646
The given error repeats endlessly yet does not hammer completely in a hard disk!
articles on the Internet came to a conclusion that all business in component Service Broker!
Found a script which stops transfer of messages:
USE master;
GO
ALTER DATABASE name_bd SET DISABLE_BROKER;
GO

The script helped! The error left! But after  systems, appeared again, and the script is not fulfilled any more (((
Help please! To solve the given problem!
At once I notify a teapot in this business so if it is possible is very detailed!

2

Re: MS a SQL Server 2016 and Service Broker!

Jonny32 wrote:

There was such problem:
There was an error in a cache of events of the timer. An error 2905, a state 2.
The code: 9646
The given error repeats endlessly yet does not hammer completely in a hard disk!
articles on the Internet came to a conclusion that all business in component Service Broker!
Found a script which stops transfer of messages:
USE master;
GO
ALTER DATABASE name_bd SET DISABLE_BROKER;
GO

The script helped! The error left! But after  systems, appeared again, and the script is not fulfilled any more (((
Help please! To solve the given problem!
At once I notify a teapot in this business so if it is possible is very detailed!

1. Such it is possible to do,  you are assured that  is completely not necessary
2. "And the script is not fulfilled any more" - writes "Ya bolshe ne vypolnyayus, idite nafig"?

3

Re: MS a SQL Server 2016 and Service Broker!

Rolg Hupin;
1. I assume that the broker is not necessary generally!
2. Writes that. After script performance passes in master and twists infinitely grists it is fulfilled...

4

Re: MS a SQL Server 2016 and Service Broker!

Jonny32;
Look log of Windows applications.

5

Re: MS a SQL Server 2016 and Service Broker!

Jonny32 wrote:

Rolg Hupin;
1. I assume that the broker is not necessary generally!
2. Writes that. After script performance passes in master and twists infinitely grists it is fulfilled...

There is such fast way if you accept its ghost effect
ALTER DATABASE [databaseName] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [databaseName] SET DISABLE_BROKER WITH ROLLBACK IMMEDIATE

6

Re: MS a SQL Server 2016 and Service Broker!

Jonny32;
And what he at you on master to a database forgot that?
If it there included, probably for what that of definite purposes.
For request:

use master
go
select * from sys.service_queues
go

What shows besides three  queues?

7

Re: MS a SQL Server 2016 and Service Broker!

And though at all it is necessary to begin with it, the version of a sequel at you what?

8

Re: MS a SQL Server 2016 and Service Broker!

felix_ff;
I apologize! There was no possibility to answer!
Release of a SQL Server 2016 Express!
Means, learned the following: the broker is necessary! Hence to disconnect it to me it is impossible!
Here completely copied an error from log windows:
Log name: Application
Source: MSSQLSERVER
Date: 10/11/2017 of 0:53:31 PM
Event code: 9646
Category :
Level: the Error
Key :
The user: /
The computer: SERVER_MED
The description:
There was an error in a cache of events of the timer. An error 2905, a state 2.
Xml events:
<Event xmlns = "http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name = "MSSQLSERVER"/>
<EventID Qualifiers = "49152"> 9646 </EventID>
<Level> 2 </Level>
<Task> 2 </Task>
<Keywords> 0x80000000000000 </Keywords>
<TimeCreated SystemTime = "2017-10-11T09:53:31.000000000Z"/>
<EventRecordID>3871722747</EventRecordID>
<Channel> Application </Channel>
<Computer> SERVER_MED </Computer>
<Security/>
</System>
<EventData>
<Data> 2905 </Data>
<Data> 2 </Data>
<Binary>AE250000100000000B0000005300450052005600450052005F004D004500440000000700000063006C0069006E00690063000000</Binary>
</EventData>
</Event>

9

Re: MS a SQL Server 2016 and Service Broker!

felix_ff wrote:

Jonny32;
And what he at you on master to a database forgot that?
If it there included, probably for what that of definite purposes.
For request:

use master
go
select * from sys.service_queues
go

What shows besides three  queues?

1. QueryNotificationErrorsQueue
2. EventNotificationErrorsQueue
3. ServiceBrokerQueue
And all! There is nothing more

10

Re: MS a SQL Server 2016 and Service Broker!

Jonny32 wrote:

felix_ff;
I apologize! There was no possibility to answer!
Release of a SQL Server 2016 Express!
Means, learned the following: the broker is necessary! Hence to disconnect it to me it is impossible!
Here completely copied an error from log windows:
Log name: Application
Source: MSSQLSERVER
Date: 10/11/2017 of 0:53:31 PM
Event code: 9646
Category :
Level: the Error
Key :
The user: /
The computer: SERVER_MED
The description:
There was an error in a cache of events of the timer. An error 2905, a state 2.
Xml events:
<Event xmlns = "http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name = "MSSQLSERVER"/>
<EventID Qualifiers = "49152"> 9646 </EventID>
<Level> 2 </Level>
<Task> 2 </Task>
<Keywords> 0x80000000000000 </Keywords>
<TimeCreated SystemTime = "2017-10-11T09:53:31.000000000Z"/>
<EventRecordID>3871722747</EventRecordID>
<Channel> Application </Channel>
<Computer> SERVER_MED </Computer>
<Security/>
</System>
<EventData>
<Data> 2905 </Data>
<Data> 2 </Data>
<Binary>AE250000100000000B0000005300450052005600450052005F004D004500440000000700000063006C0069006E00690063000000</Binary>
</EventData>
</Event>

Before this message in a broad gull there are any records? For example, about the master a key etc.

11

Re: MS a SQL Server 2016 and Service Broker!

Rolg Hupin;
There is Svedinija:
Log name: Application
Source: Windows Error Reporting
Date: 10/11/2017 of 6:06:22 PM
Event code: 1001
Category :
Level: Convergence
Key :
The user: /
The computer: SERVER_MED
The description:
The error container, type 0
Event name: SQLException64
The response: There is no data
Identifier CAB: 0
The problem signature:
P1: sqlservr.exe
P2: 0.0.0.0
P3: 0000000000000000
P4: sqllang.dll
P5: 2015.130.4446.0
P6: 00000000596C7ADB
P7:-1073741819
P8: 000000000063FD5D
P9: 000000015E4A91B8
P10:
Nested files:
C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\SQLDump0001.mdmp
C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\SQLDump0001.txt
C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\SQLDump0001.log
These files can be found here:
The analysis character:
Repeated search of the decision: 0
The report identifier: bdb47af8-ae95-11e7-9495-7427ea5c9eb5
Report state: 262144
The heshirovannyj container:
Xml events:
<Event xmlns = "http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name = "Windows Error Reporting"/>
<EventID Qualifiers = "0"> 1001 </EventID>
<Level> 4 </Level>
<Task> 0 </Task>
<Keywords> 0x80000000000000 </Keywords>
<TimeCreated SystemTime = "2017-10-11T15:06:22.000000000Z"/>
<EventRecordID>3917313709</EventRecordID>
<Channel> Application </Channel>
<Computer> SERVER_MED </Computer>
<Security/>
</System>
<EventData>
<Data>
</Data>
<Data> 0 </Data>
<Data> SQLException64 </Data>
<Data> There are no given </Data>
<Data> 0 </Data>
<Data> sqlservr.exe </Data>
<Data> 0.0.0.0 </Data>
<Data> 0000000000000000 </Data>
<Data> sqllang.dll </Data>
<Data> 2015.130.4446.0 </Data>
<Data> 00000000596C7ADB </Data>
<Data>-1073741819 </Data>
<Data> 000000000063FD5D </Data>
<Data> 000000015E4A91B8 </Data>
<Data>
</Data>
<Data>
C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\SQLDump0001.mdmp
C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\SQLDump0001.txt
C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\SQLDump0001.log </Data>
<Data>
</Data>
<Data>
</Data>
<Data> 0 </Data>
<Data>bdb47af8-ae95-11e7-9495-7427ea5c9eb5</Data>
<Data> 262144 </Data>
<Data>
</Data>
</EventData>
</Event>

12

Re: MS a SQL Server 2016 and Service Broker!

Rolg Hupin, is one more convergence, it after an error 2905:
Log name: Application
Source: MSSQLSERVER
Date: 10/11/2017 of 6:06:22 PM
Event code: 28084
Category :
Level: Convergence
Key :
The user: /
The computer: SERVER_MED
The description:
During internal activation of component Service Broker there was an error at attempt of review of a state of the user queue "clinic.dbo.SqlQueryNotificationService-6236ecf1-07a4-4a28-bd3e-f99c2250aef2". An error: 2905, a state: 1. This information message. Any actions of the user it is not required.
Xml events:
<Event xmlns = "http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name = "MSSQLSERVER"/>
<EventID Qualifiers = "16384"> 28084 </EventID>
<Level> 4 </Level>
<Task> 2 </Task>
<Keywords> 0x80000000000000 </Keywords>
<TimeCreated SystemTime = "2017-10-11T15:06:22.000000000Z"/>
<EventRecordID>3917314742</EventRecordID>
<Channel> Application </Channel>
<Computer> SERVER_MED </Computer>
<Security/>
</System>
<EventData>
<Data>clinic.dbo.SqlQueryNotificationService-6236ecf1-07a4-4a28-bd3e-f99c2250aef2</Data>
<Data> 2905 </Data>
<Data> 1 </Data>
<Data>
</Data>
<Binary>B46D00000A0000000B0000005300450052005600450052005F004D004500440000000700000063006C0069006E00690063000000</Binary>
</EventData>
</Event>

13

Re: MS a SQL Server 2016 and Service Broker!

Jonny32;
At you a problem with this queue clinic.... In basis. Look in section ServiceBroker of the basis. If queues are not present, someone means "superfluous" deleted also application it is necessary to reinstall.

14

Re: MS a SQL Server 2016 and Service Broker!

Kolosov wrote:

Jonny32;
At you a problem with this queue clinic.... In basis. Look in section ServiceBroker of the basis. If queues are not present, someone means "superfluous" deleted also application it is necessary to reinstall.

I a teapot in this business so experience for my French)))
1. To look if at queue or not:
I.e. I fulfill system representation sys.transmission_queue , there is empty!
2. Application should be reinstalled. What?

15

Re: MS a SQL Server 2016 and Service Broker!

Today this ill-fated queue dbo.SqlQueryNotificationService-6236ecf1-07a4-4a28-bd3e-f99c2250aef2 stared in basis!!!
Question as it to delete? The right button, a line it is impossible to delete ((writes that type it does not exist or there is a resolution!
Help  to delete it!

16

Re: MS a SQL Server 2016 and Service Broker!

The problem is solved!! All THANKS!
Fulfilled a script:

ALTER DATABASE db_name SET NEW_BROKER WITH ROLLBACK IMMEDIATE
GO

All earned as hours!
0 errors!