Service Broker: This connection lost the arbitration and it will be closed OR User does not have permission to send to the service OR Service Broker received an error message on this conversation. Service Broker will not transmit the message
June 28, 2019 | Posted in SQL Server
I haven’t blogged in a while, but this certainly warrants some sort of formal entry, because it may just help someone else with the same problem, or in the future I may be helping myself! (back to the future moment).
If you are trying to debug what on earth is going on in the land of service broker because your messages aren’t arriving and you get the following on the initiator side…
Service Broker received an error message on this conversation. Service Broker will not transmit the message; it will be held until the application ends the conversation.
…you may have come to the right place.
This one pretty well took up an entire day of my time trying to work out why the messages were stuck on the initiator with that error (Microsoft, if you are reading this, send the internal errors back to the initiator and set it on the sys.tranmission_queue.transmission_status!!!!). All configuration looked normal, ssbdiagnose didn’t give me any kind of leads, so I turned to my friend SQL Profiler. Every time I tried to reprocess the sending of the message to the target, I would see the following in the SQL logs on the target:
A new connection was established with the same peer. This connection lost the arbitration and it will be closed. All traffic will be redirected to the newly opened connection. This is an informational message only. No user action is required. State 79.
For me this was a red herring. It had nothing to do with my issue, and perhaps it doesn’t for you either.
Having switched on all broker events, filtered to the database in question (the target), and filtered out all of the unnecessary tracing I had no interest in, I eventually found this:
This message could not be delivered because the user with ID 0 in database ID 20 does not have permission to send to the service. Service name: ‘YOURSERVICE’
When I looked at that user it was [public] (at this point I felt like I was getting closer to the truth):
select * from master.sys.database_principals where principal_id = 0
I had some commands tucked away that I have used in the past, one of which is a command to GRANT SEND ON SERVICE to public. This seemed to fit what I was looking at quite well:
GRANT SEND ON SERVICE::[YOURSERVICE] TO [public] AS [dbo]
GO
I went back to the initiator, and reprocessed the message one last time.. Eureka! And so therein lies the FIX.
You may also like to try these which have also helped me in the past in other situations:
- DROP/CREATE the Service Broker endpoint
- If you do the above, make sure you grant connect on the end point to public:
GRANT CONNECT ON ENDPOINT::[ServiceBrokerEndPoint] TO PUBLIC
GO
- Make sure there are not two end points listening on port 4022 (if you have two versions of SQL Server installed one may come up listening on that port before the other, in which case was move them to using different ports)
- Check both the target and initiator have sys.databases.is_trustworthy_on = 1, sys.databases.is_broker_enabled = 1, sys.databases.suser_sname(owner_sid) = ‘sa’
- ssbdiagnose is a command line utility which can be useful in finding configuration issues between the initiator and the target… examples (switch initiator to target to test the other way):
Initiator to Target:
ssbdiagnose CONFIGURATION FROM SERVICE “YOURINITIATORSERVICE” -S “INITIATORSERVER” -d “INITIATORDATABASENAME” TO SERVICE “YOURTARGETSERVICE” -S “TARGETSERVERNAME” -d “TARGETDATABASENAME” ON CONTRACT “YOURCONTRACT”
- Grant receive on your initiator AND target queues:
GRANT RECEIVE ON [dbo].[your_initiator_queue] TO [public]
GO
GRANT RECEIVE ON [dbo].[your_target_queue] TO [public]
GO
- Search through SQL logs for some specific error or message:
set dateformat ymd;
declare @log table (LogDate datetime, ProcessInfo varchar(50), Description varchar(500))
insert into @log
EXEC master.dbo.sp_readerrorlog 0, 1, ‘handshake’
select * from @log
where LogDate > ’2019-06-28′
- Investigating broker connections and tasks:
select * from sys.dm_broker_connections
select * from sys.dm_broker_activated_tasks
select * from sys.dm_exec_connections where protocol_type = ‘Service Broker’ order by connect_time desc
- And finally, how I solved this, SQL Profiler. I have on some occasions fallen back to this tool and it has helped me find the error, and in some cases Service Broker is not very good at bubbling up the internal errors like the one above. When you find yourself in this situation, use SQL Profiler, find the problem, and fix it. It may save you a lot of time!
Consider this too… there were other services that needed the same fix so you may want to do them at the same time:
select ‘GRANT SEND ON SERVICE::[' + name + '] TO [public] AS [dbo]‘
from sys.services
where name like ‘%yoursearchstring%’
SQL Server Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.
October 28, 2016 | Posted in SQL Server
For me, I received this error having got up to 1110 case when statements i.e.
case
when Something = ’1′ then ’2′
else null end as Test
So I assume this is the upper limit allowed by SQL Server.
Incorrect Syntax Near ‘BEGIN’. Expecting EXTERNAL.
October 3, 2016 | Posted in SQL Server
Example of where this might happen:
IF NOT EXISTS (SELECT NULL FROM sys.objects [objects] JOIN sys.schemas [schemas] ON [objects].schema_id = [schemas].schema_id WHERE [schemas].[Name] = ‘dbo’ AND [objects].[Name] like ‘%usp_MySproc%’)
BEGIN
CREATE PROCEDURE [dbo].[usp_MySproc]
(
@SiteID INT,
@JobEntryID INT,
@LastQueried datetime,
@ResourceId INT
)
AS
BEGIN
SELECT 1;
END
END
GO
Here of how to fix it:
IF NOT EXISTS (SELECT NULL FROM sys.objects [objects] JOIN sys.schemas [schemas] ON [objects].schema_id = [schemas].schema_id WHERE [schemas].[Name] = ‘dbo’ AND [objects].[Name] like ‘%usp_MySproc%’)
BEGIN
EXEC(‘CREATE PROCEDURE [dbo].[usp_MySproc]
(
@SiteID INT,
@JobEntryID INT,
@LastQueried datetime,
@ResourceId INT
)
AS
BEGIN
SELECT 1;
END’)
END
GO
How to tell which SQL Server version you are using in SSMS
September 2, 2016 | Posted in SQL Server
DECLARE @ver nvarchar(128)
SET @ver = CAST(serverproperty(‘ProductVersion’) AS nvarchar)
SET @ver = SUBSTRING(@ver, 1, CHARINDEX(‘.’, @ver) – 1)
IF ( @ver = ’7′ )
SELECT ‘SQL Server 7′
ELSE IF ( @ver = ’8′ )
SELECT ‘SQL Server 2000′
ELSE IF ( @ver = ’9′ )
SELECT ‘SQL Server 2005′
ELSE IF ( @ver = ’10′ )
SELECT ‘SQL Server 2008/2008 R2′
ELSE IF ( @ver = ’11′ )
SELECT ‘SQL Server 2012′
ELSE IF ( @ver = ’12′ )
SELECT ‘SQL Server 2014′
ELSE
SELECT ‘Unsupported SQL Server Version’
How To Script Data From A SQL Server Table
June 17, 2015 | Posted in SQL Server
I wanted a way to script off the data in a sql table in some automated fashion rather than having to do it manually and it turns out there is a way to do this in SSMS:
- Right-click on the DB, select Generate Scripts
- Click <Next> through the Introduction screen
- Select Specific database objects then your table, then <Next>
- Click <Advanced> then for “Types of Data to script” select “Schema and Data” then <OK>
- Then save to file\clipboard or new window
It will script off even things like identities so you’ll need to change that if it’s not something you require.
How To Create Class Structure With Properties From SQL Table In VB.Net Or C#.Net
April 27, 2015 | Posted in SQL Server
I wanted to overcome the repetitive task of creating a new class and associated properties every time a new table is introducted in to the system, so I’ve written a stored procedure to do exactly that:
create procedure [dbo].[CreateClassStructureFromSQLTable]
(
@VB bit,
@TableName varchar(255),
@PluraliseClassName bit
)
as
/*
exec [dbo].[CreateClassStructureFromSQLTable] 1, ‘tblSomeTable’, 1
*/
declare @prop varchar(max)
declare @field varchar(255)
declare @field_lower varchar(255)
declare @type varchar (255)
declare @class_prefix varchar(10)
set @class_prefix = ‘X’
if @VB = 0
begin
PRINT ‘public class ‘ + replace(@TableName,’tbl’,@class_prefix) + case when @PluraliseClassName = 1 then ‘s’ else ” end + ‘ : IDisposable’
PRINT ‘{‘
declare cprops cursor for
select distinct ‘public ‘ + ColumnType + NullableSign + ‘ ‘ + ColumnName AS prop
from
(
select
replace(col.name, ‘ ‘, ‘_’) ColumnName,
column_id ColumnId,
case typ.name
when ‘bigint’ then ‘long’
when ‘binary’ then ‘byte[]‘
when ‘bit’ then ‘bool’
when ‘char’ then ‘string’
when ‘date’ then ‘System.DateTime’
when ‘datetime’ then ‘System.DateTime’
when ‘datetime2′ then ‘System.DateTime’
when ‘datetimeoffset’ then ‘DateTimeOffset’
when ‘decimal’ then ‘decimal’
when ‘float’ then ‘float’
when ‘image’ then ‘byte[]‘
when ‘int’ then ‘int’
when ‘money’ then ‘decimal’
when ‘nchar’ then ‘char’
when ‘ntext’ then ‘string’
when ‘numeric’ then ‘decimal’
when ‘nvarchar’ then ‘string’
when ‘real’ then ‘double’
when ‘smalldatetime’ then ‘System.DateTime’
when ‘smallint’ then ‘short’
when ‘smallmoney’ then ‘decimal’
when ‘text’ then ‘string’
when ‘time’ then ‘TimeSpan’
when ‘timestamp’ then ‘System.DateTime’
when ‘tinyint’ then ‘byte’
when ‘uniqueidentifier’ then ‘System.Guid’
when ‘varbinary’ then ‘byte[]‘
when ‘varchar’ then ‘string’
else ‘UNKNOWN_’ + typ.name
end ColumnType,
case
when col.is_nullable = 1 and typ.name in (‘bigint’, ‘bit’, ‘date’, ‘datetime’, ‘datetime2′, ‘datetimeoffset’, ‘decimal’, ‘float’, ‘int’, ‘money’, ‘numeric’, ‘real’, ‘smalldatetime’, ‘smallint’, ‘smallmoney’, ‘time’, ‘tinyint’, ‘uniqueidentifier’)
then ‘?’
else ”
end NullableSign
from sys.columns col
join sys.types typ on
col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
where object_id = object_id(@TableName)
) t
order by prop
open cprops
FETCH NEXT FROM cprops INTO @prop
WHILE @@FETCH_STATUS = 0
BEGIN
set @field = replace(substring(replace(@prop, ‘public ‘, ”), charindex(‘ ‘,replace(@prop, ‘public ‘, ”)),100),’ ‘,”)
set @field_lower = lower(left(@field,1)) + right(@field, len(@field)-1)
set @type = left(replace(@prop, ‘public ‘, ”), charindex(‘ ‘, replace(@prop, ‘public ‘, ”)))
–select @field, @field_lower, @type
print ‘ private ‘ + @type + ‘_’ + @field_lower + ‘;’
print ‘ ‘+@prop
print ‘ {‘
print ‘ get’
print ‘ {‘
print ‘ return this._’ + @field_lower + ‘;’
print ‘ }’
print ‘ set’
print ‘ {‘
print ‘ this._’ + @field_lower + ‘ = value;’
print ‘ }’
print ‘ }’
print ”
FETCH NEXT FROM cprops INTO @prop
END
close cprops
DEALLOCATE cprops
PRINT ‘}’
end
else
begin
PRINT ‘Public Class ‘ + replace(@TableName,’tbl’,@class_prefix) + case when @PluraliseClassName = 1 then ‘s’ else ” end
PRINT ‘ Implements IDisposable’
PRINT ”
declare props cursor for
select distinct ‘Public Property ‘ + ColumnName + ‘ as ‘ + ColumnType AS prop
from (
select
replace(col.name, ‘ ‘, ‘_’) ColumnName, column_id,
case typ.name
when ‘bigint’ then ‘Long’
when ‘binary’ then ‘Byte[]‘
when ‘bit’ then ‘Boolean’
when ‘char’ then ‘String’
when ‘date’ then ‘DateTime’
when ‘datetime’ then ‘DateTime’
when ‘datetime2′ then ‘DateTime’
when ‘datetimeoffset’ then ‘DateTimeOffset’
when ‘decimal’ then ‘Decimal’
when ‘float’ then ‘Float’
when ‘image’ then ‘Byte[]‘
when ‘int’ then ‘Integer’
when ‘money’ then ‘Decimal’
when ‘nchar’ then ‘Char’
when ‘ntext’ then ‘String’
when ‘numeric’ then ‘Decimal’
when ‘nvarchar’ then ‘String’
when ‘real’ then ‘Double’
when ‘smalldatetime’ then ‘DateTime’
when ‘smallint’ then ‘Short’
when ‘smallmoney’ then ‘Decimal’
when ‘text’ then ‘String’
when ‘time’ then ‘TimeSpan’
when ‘timestamp’ then ‘DateTime’
when ‘tinyint’ then ‘Byte’
when ‘uniqueidentifier’ then ‘Guid’
when ‘varbinary’ then ‘Byte[]‘
when ‘varchar’ then ‘String’
end ColumnType
from sys.columns col join sys.types typ on col.system_type_id = typ.system_type_id
where object_id = object_id(@TableName)
) t
order by prop
open props
FETCH NEXT FROM props INTO @prop
WHILE @@FETCH_STATUS = 0
BEGIN
set @field = replace(left(replace(@prop, ‘Public Property ‘, ”), charindex(‘ ‘, replace(@prop, ‘Public Property ‘, ”))),’ ‘,”)
set @field_lower = lower(left(@field,1)) + right(@field, len(@field)-1)
set @type = replace(replace(@prop, ‘Public Property ‘, ”), @field + ‘ as ‘,”)
print ‘ Private _’ + lower(left(replace(@prop, ‘Public Property ‘, ”),1)) + substring(replace(@prop, ‘Public Property ‘, ”), 2,100)
print ‘ ‘ +@prop
print ‘ Get’
print ‘ Return Me._’ + @field_lower
print ‘ End Get’
print ‘ Set(value As ‘ + @type + ‘)’
print ‘ Me._’ + @field_lower + ‘ = value’
print ‘ End Set’
print ‘ End Property’
print ”
FETCH NEXT FROM props INTO @prop
END
close props
DEALLOCATE props
PRINT ‘End Class’
end
Disable Specific Triggers Based On SYS.SQL_MODULES DEFINITION
April 7, 2015 | Posted in SQL Server, Uncategorized
declare @temp table
(table_name varchar(100), trigger_name varchar(100))
insert into @temp
select object_name(parent_id), name
from sys.triggers
where name in
(
select object_name(object_id) from sys.sql_modules where definition like ‘%SEARCH%STRING%’
)
and is_disabled = 0
declare @sql varchar(255)
declare db_cursor cursor for
select ‘ALTER TABLE ‘ + table_name + ‘ DISABLE TRIGGER [' + trigger_name + ']‘ from @temp x
open db_cursor
fetch next from db_cursor into @sql
while @@FETCH_STATUS = 0
begin
exec (@sql);
fetch next from db_cursor into @sql
end
close db_cursor
deallocate db_cursor
SQL Server Table Sizing
November 20, 2014 | Posted in SQL Server
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
(SUM(a.total_pages) * 8) / 1024 AS TotalSpaceMB,
(SUM(a.used_pages) * 8) / 1024 AS UsedSpaceMB,
((SUM(a.total_pages) – SUM(a.used_pages)) * 8) / 1024 AS UnusedSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE ‘dt%’
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
5 desc
Calculating SQL Server Table Sizes
June 23, 2014 | Posted in SQL Server
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) – SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE ‘dt%’
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
5 desc
An error occurred while receiving data: ’10054(error not found)
June 19, 2014 | Posted in SQL Server
Having problems sending broker messages from the initiator over to the target?
GRANT CONNECT ON ENDPOINT::YourEndPoint TO [public]
Locked out of aspnetdb
May 7, 2014 | Posted in SQL Server
Found yourself locked out of aspnetdb and need to reset your password?
Declare @UserName NVarChar(30)
Declare @Password NVarChar(30)
Declare @Application NVarChar(255)
Declare @PasswordSalt NVarChar(128)
set @UserName = ‘SomeUser’
set @Password = ‘SomeP@$$word’
set @Application = ‘/ApplicationName’
Set @PasswordSalt = (SELECT 1 PasswordSalt FROM aspnet_Membership WHERE UserID = ’00000000-0000-0000-0000-000000000000′)
Exec dbo.aspnet_Membership_ResetPassword @Application, @UserName, @Password, 10, 10, @PasswordSalt, -5
Why Can’t I See Queued Service Broker Messages In Sys.Transmission_Queue?
April 11, 2014 | Posted in SQL Server
You need to be in the db_owner() role. The only way around this is to create a stored procedure to execute as dbo which will return this information:
CREATE VIEW sys.transmission_queue AS
SELECT conversation_handle = S.handle,
to_service_name = Q.tosvc,
to_broker_instance = Q.tobrkrinst,
from_service_name = Q.fromsvc,
service_contract_name = Q.svccontr,
enqueue_time = Q.enqtime,
message_sequence_number = Q.msgseqnum,
message_type_name = Q.msgtype,
is_conversation_error = sysconv(bit, Q.status & 2),
is_end_of_dialog = sysconv(bit, Q.status & 4),
message_body = Q.msgbody,
transmission_status = GET_TRANSMISSION_STATUS (S.handle),
priority = R.priority
FROM sys.sysxmitqueue Q
INNER JOIN sys.sysdesend S WITH (NOLOCK) on Q.dlgid = S.diagid AND Q.finitiator = S.initiator
INNER JOIN sys.sysdercv R WITH (NOLOCK) ON Q.dlgid = R.diagid AND Q.finitiator = R.initiator
WHERE is_member(‘db_owner’) = 1
SQL Char Count
February 24, 2014 | Posted in SQL Server
A nice and simple way to find the total instances of a specific char in a SQL varchar:
select (len(’1/2/3/4/5/6′) – len(replace(’1/2/3/4/5/6′, ‘/’, ”))) as char_count
Scale out SQL Server by using Reliable Messaging
October 3, 2013 | Posted in SQL Server
How do large-scale sites and applications remain SQL-based? is a recent article from Michael Rys (Blog|Twitter) that highlights the relational SQL Server based web-scale deployment at MySpace. I have talked before about how MySpace uses Service Broker as a reliable messaging backbone to power the communications between +1000 databases, allowing them to scale-out and partition the user information into individual shards. Here are some more details about this architecture:
- MySpace Uses SQL Server Service Broker to Protect Integrity of 1 Petabyte of Data
- Developing Large Scale Web Applications and Services(video)
- MySpace SQL Server Service Broker (slides)
This new article uses the MySpace deployment as a case study to counter balance the claim that large web-scale deployments require the use of NoSQL storage because relational database cannot scale. BTW I know the SQL vs. NoSQL discussion is more subtle, but I won’t enter into details here. I think a good read on thattopic is NoSQL vs. RDBMS: Let the flames begin!.
Why is reliable messaging a key tenet of implementing a data-partitioned scale-out application? Consider a typical example of an modern web-scale application: users connect, get authenticated and view their own profile, but they are also interested in the status updates or wall messages from their network of friends or followers. It is easy to see how one partitions the user profile data, but how do you partition the user ‘walls’? User A is in a partition hosted on node 1, while user B is in a partition hosted by node 2, when User A update his status, how does this show up on User B’s wall?
One option is to have the application update the wall of User B when User A changes his status. But this prevents scalability, because now writes have to occur on many nodes and the application has to orchestrate all these writes. Think Lady GaGa updating her status, the application has to update the wall of every follower.
Another option is to have the application read the status from User A when displaying the wall of User B. This also doesn’t scale, because reads now occur on many nodes. All those Lady GaGa followers refreshing their wall page have to read from the one node hosting her status, and the node is soon overwhelmed.
A solution is to replicate the write on User A’s status onto User B’s wall. The application only updates the User A status, and the infrastructure propagates the this update to User B’s wall.
But traditional replication was historically designed for replicating entire data sets of fixed schema over static topologies, and it falls short of replicating web-scale deployments of hundreds and thousands of nodes:
- it depends too tightly on physical location and it cannot adapt to rapid changes of topology (nodes being added and removed)
- its based on schema defined filtering which is difficult to map to the complex application specific data routing conditions (this update goes to node 2 because User B follows User A, but that update goes to node 3 because User D follows User C)
- its very sensitive to schema changes making application upgrade roll outs a big challenge
Messaging is designed with application-to-application communication in mind and has different semantics that are more friendly on large scale-out deployments:
- Logical routing to isolate application from topology changes
- Protocol versioning information allows side-by-side deployments making application upgrade roll outs possible
- Data schema can change more freely as peers are shielded from changes by keeping the communication protocol unchanged
With messaging in place the application updates the status of User A and the drops a message into a local outbound queue to notify all friends of A. The reliable messaging infrastructure dispatches this message to all nodes interested and processing of this message results in an update of User B wall. MySpace uses Service Broker as the reliable messaging infrastructure, and they make up for the lack of publish/subscribe by adding a router-dispatcher unit: all messages are sent to this dispatcher and the dispatcher in turn figures out how many subscribers have to be notified, and sends individual messages to them. See the slides linked at the beginning of my post for more details. As a side note, I see that StackExchange is also embarking on the route of creating a message bus for their pub/sub infrastructure, but they use Redis as a message store, see asyncRedis await BookSleeve.
Robert Scoble had a post MySpace’s death spiral: insiders say it’s due to bets on Los Angeles and Microsofton which it claims that MySpace insiders blame their loss to Facebook to, amongst other things, the complexity of this infrastructure:
Workers inside MySpace tell me that this infrastructure, which they say has “hundreds of hacks to make it scale that no one wants to touch” is hamstringing their ability to really compete.
I do no know if the sources quoted by the article are right or wrong, and I was never personally involved with the MySpace deployment, but since I was so closely involved in building SQL Service Broker and as SSB is one of the critical components used by MySpace infrastructure, I am understandably interested in this discussion. Service Broker has a notoriously steep learning curve, there are no tools for administer, monitor and troubleshoot Service Broker deployments, and there is absolutely no support in the client programming stack (the managed .Net Framework). This is why all solutions that deploy Service Broker that I know of are large enterprise shops that are staffed with architects that understand the set of unique advantages this technology brings, and are willing to venture into uncharted waters despite the prospect of being impossible to hire development and ops talent with Service Broker expertise. But also the feedback I hear from these deployments is almost always positive: once deployed, Service Broker just works. Someone told me that the Service Broker solution they had was the only piece of technology that “did not break in the last 3 years” and that covers an upgrade from SQL Server 2005 to SQL Server 2008. See Is Service Broker in SQL Server 2008 compatible with the one in SQL Server 2005? to see how Service Broker helps address infrastructure upgrade problems. Personally I am not surprised that Service Broker turns out to be a cornerstone of the response SQL Server has to give to the NoSQL challenge, but this vitally unheard of technology (89 questions tagged service-broker on StackOverflow at time of writing this) will be quite a surprise answer for many
Working Out Which Service Broker Queues Are Bound To Your Services
October 3, 2013 | Posted in SQL Server
select [service].name, [queue].name
from sys.services [service]
join sys.service_queue_usages [useage]
on [service].service_id = [useage].service_id
join sys.service_queues [queue]
on [queue].object_id = [useage].service_queue_id
where [queue].is_ms_shipped = 0
Printing Blocks Of SQL
October 3, 2013 | Posted in SQL Server
–@sql contains the sql to be printed in segments
DECLARE @CurrentEnd BIGINT; /* track the length of the next substring */
DECLARE @offset tinyint; /*tracks the amount of offset needed */
set @sql = replace( replace(@sql, char(13) + char(10), char(10)) , char(13), char(10))
WHILE LEN(@sql) > 1
BEGIN
IF CHARINDEX(CHAR(10), @sql) between 1 AND 4000
BEGIN
SET @CurrentEnd = CHARINDEX(char(10), @sql) -1
set @offset = 2
END
ELSE
BEGIN
SET @CurrentEnd = 4000
set @offset = 1
END
PRINT SUBSTRING(@sql, 1, @CurrentEnd)
set @sql = SUBSTRING(@sql, @CurrentEnd+@offset, LEN(@sql))
END /*End While loop*/
Debugging Remote SQL Server Service Broker Configuration
October 3, 2013 | Posted in SQL Server
Run command prompt with the following:
cd C:\Program Files\Microsoft SQL Server\100\Tools\Binn
The syntax to then check two services on different servers is:
ssbdiagnose CONFIGURATION FROM SERVICE /text/initiator
-S InitiatorComputer -U InitiatorLogin -p !wEx23Dvb
-d InitiatorDatabase TO SERVICE /test/target -S TargetComputer
-U TargetLogin -p ER!49jiy -d TargetDatabase ON CONTRACT TestContract
Stopping And Starting SQL Server Agent Using Batch Files
October 3, 2013 | Posted in SQL Server
StopSQLServerAgent.bat
@echo off
cls
net stop SQLSERVERAGENT
echo Completed at %date%_%time%
echo Holding result for 24 hours.
ping -n 1 -w 86400000 1.2.3.4 > nul
EXIT
StartSQLServerAgent.bat
@echo off
cls
net start SQLSERVERAGENT
echo Completed at %date%_%time%
echo Holding result for 24 hours.
ping -n 1 -w 86400000 1.2.3.4 > nul
EXIT
Run both files as administrator.
Disaster Recovery: What to do when the SA account password is lost in SQL Server 2005
October 3, 2013 | Posted in SQL Server
You may have faced the issue of losing the SQL Server SA password. Perhaps you followed the security best-practice of removing the builtin\Administrators from the sysadmin server role, and no one you can find is in the sysadmin role. At this point you may think that your only options are to reinstall SQL Server and attach the databases, or to directly access the master database files, which may potentially damage the data.
SQL Server 2005 provides a better disaster recovery option for this scenario that is non-intrusive for master DB and that will help you preserve any objects and data stored in master DB (such as logins, certificates, Service Master Key, etc.) intact. Members of the Windows Administrators group now have access to SQL Server when SQL Server is in started in single-user mode, also known as “maintenance mode “.
Using the single-user mode, SQL Server 2005 prevents a Windows Administrator to abuse this privilege to act on behalf of the sysadmin without being noticed. This allows Windows Administrator accounts to perform certain maintenance tasks, such as installing patches.
In order to start SQL Server in single-user mode, you can add the parameter “-m” at the command line. You can also use the SQL Server Configuration Manager tool, which provides proper controls for the file access and other privileges. To use the Configuration Manager tool to recover your system, use the following steps:
1. Open the Configuration Manager tool from the “SQL Server 2005| Configuration” menu
2. Stop the SQL Server Instance you need to recover
3. Navigate to the “Advanced” tab, and in the Properties text box add “;–m” to the end of the list in the “Startup parameters” option
4. Click the “OK” button and restart the SQL Server Instance
NOTE: make sure there is no space between “;” and “-m”, the registry parameter parser is sensitive to such typos. You should see an entry in the SQL Server ERRORLOG file that says “SQL Server started in single-user mode. After the SQL Server Instance starts in single-user mode, the Windows Administrator account is able to connect to SQL Server using the sqlcmd utility using Windows authentication. You can use Transact-SQL commands such as “sp_addsrvrolemember” to add an existing login (or a newly created one) to the sysadmin server role.
Run the following from command prompt:
sqlcmd -E -S LOCALHOST\MSSQLSERVER (hit enter)
1> EXEC sp_addsrvrolemember ‘DOMAIN\YourAccount’, ‘sysadmin’; (hit enter)
2> GO (hit enter)
Once the sysadmin access has been recovered, remove the “;-m” from the startup parameters using the Configuration Manager and restart the SQL Server Instance
SQL Server Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
October 3, 2013 | Posted in SQL Server
This is a classic issue whilst using SQL Server, where two processes are trying to lock the same resources at the same time. If you are handling this with some sort of retry mechanism like myself, try the following to slow SQL Server down before attempting the command again.
/* delay retry for a random number of seconds >= 4 and <= 9 */
declare @Random int, @Upper int, @Lower int, @time varchar(50);
set @Lower = 4
set @Upper = 9
select @Random = ROUND(((@Upper – @Lower -1) * RAND() + @Lower), 0)
set @time = ’00:00:0′ + cast(@Random as varchar(50))
WAITFOR DELAY @time
SET NOCOUNT ON Improves SQL Server Stored Procedure Performance
October 3, 2013 | Posted in SQL Server
Problem
One of the biggest things that DBAs try to do on a daily basis is to ensure that their database systems run as fast as possible. As more and more users access the databases and the databases continue to grow, performance slow downs are almost inevitable. Based on this, DBAs and developers should do everything they possibly can to keep performance related issues in mind early in the database lifecycle. This is not always easy to do, because of the unknowns and the changes that occur over time, but there are some simple things that can be done and we will touch upon one of these in this tip.
Solution
Sometimes even the simplest things can make a difference. One of these simple items that should be part of every stored procedure is SET NOCOUNT ON. This one line of code, put at the top of a stored procedure turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed. This is performed for all SELECT, INSERT, UPDATE, and DELETE statements. Having this information is handy when you run a T-SQL statement in a query window, but when stored procedures are run there is no need for this information to be passed back to the client.
By removing this extra overhead from the network it can greatly improve overall performance for your database and application.
If you still need to get the number of rows affected by the T-SQL statement that is executing you can still use the @@ROWCOUNT option. By issuing a SET NOCOUNT ON this function (@@ROWCOUNT) still works and can still be used in your stored procedures to identify how many rows were affected by the statement.
Microsoft even realized the issue that this creates and has changed the stored procedure templates from SQL Server 2000 to SQL Server 2005.
Here is the old template style available in SQL Server 2000 without the SET NOCOUNT ON.
| – ============================================= – Create procedure basic template – ============================================= – creating the store procedure IF EXISTS (SELECT name FROM sysobjects WHERE name = N’<procedure_name, sysname, proc_test>’ AND type = ‘P’) DROP PROCEDURE <procedure_name, sysname, proc_test> GO CREATE PROCEDURE <procedure_name, sysname, proc_test> – ============================================= |
Here is the new template style available in SQL Server 2005 with the SET NOCOUNT ON.
| – ================================================ – Template generated from Template Explorer using: – Create Procedure (New Menu).SQL – – Use the Specify Values for Template Parameters – command (Ctrl-Shift-M) to fill in the parameter – values below. – – This block of comments will not be included in – the definition of the procedure. – ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO – ============================================= – Author: <Author,,Name> – Create date: <Create Date,,> – Description: <Description,,> – ============================================= CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> – Add the parameters for the stored procedure here <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0> AS BEGIN – SET NOCOUNT ON added to prevent extra result sets from – interfering with SELECT statements. SET NOCOUNT ON; – Insert statements for procedure here |
As you can see even simple little things such as this can make an overall improvement for your database environment. Stay tuned for other simple tricks and techniques to improve performance.
Original source: http://www.mssqltips.com/sqlservertip/1226/set-nocount-on-improves-sql-server-stored-procedure-performance/
Creating A SQL Server Table, The Easy Way
October 3, 2013 | Posted in SQL Server
I wanted to create a quick table in my database to store some values so that I could later roll back the values in the existing table once I had finished some testing. Rather than creating a temporary table for the lifespan on my SQL connection, instead I created a table with this simple T-SQL statement:
select * into tblTempTable
from tblExisingTable
Voila.
Debugging SQL Server Service Broker Configuration
October 3, 2013 | Posted in SQL Server
Updated: 28/06/2019 :: Consider the following URL to see it helps in resolving messages not being received on the target due to a likely connection issue or if you are seeing “Service Broker received an error message on this conversation. Service Broker will not transmit the message” on the initiator >>>
Original post:
Having probed SQL Server Service Broker for some time trying to diagnose a fault with it’s configuration, I soon found a tool that would do this for me in the tools folder under the installation directory called ssbdiagnose.exe. Below is an example of how to query the configuration of your service broker setup (it will diagnose and return any faults):
Run command prompt (I ran this from machine as I had SQL Service 2008 installed which this tool is part of):
cd C:\Program Files\Microsoft SQL Server\100\Tools\Binn
C:\Program Files\Microsoft SQL Server\100\Tools\Binn>ssbdiagnose -u username -p password -s “server ip” -d “databasename” CONFIGURATION FROM SERVICE ”
http://fromservice” TO SERVICE “http://toservice” ON CONTRACT “http://contract”
The results may look something like the following:
Microsoft SQL Server 10.50.1600.1
Service Broker Diagnostic Utility
D 29997 127.0.0.1 Database1 Service Broker GUID is identical to tha
t of database Database1 on server 127.0.0.1
D 29975 127.0.0.1 Database1 User dbo does not have SEND per
mission on service http://someservice
D 29964 127.0.0.1 Database2 The master key was not found
3 Errors, 0 Warnings
C:\Program Files\Microsoft SQL Server\100\Tools\Binn>
It’s also worth noting that you can run ssbdiagnose against a SQL Server 2005 instance.
Drop SQL CLR Assembly And It’s Dependencies
October 3, 2013 | Posted in SQL Server
Having looked for something that would drop an assemblies dependencies prior to dropping the assembly itself, I couldn’t find anything. So I put this together. Enjoy.
/* review schema information */
select [schema].Name,
[modules].object_id,
[modules].assembly_id,
[modules].assembly_class,
[modules].assembly_method,
[objects].name,
[objects].type,
[objects].type_desc
from sys.assembly_modules [modules]
join sys.objects [objects] with (nolock)
on [modules].object_id = [objects].object_id
join sys.schemas [schema] with (nolock)
on [objects].schema_id = [schema].schema_id
where [modules].assembly_class in (N’NAME’)
begin try
begin transaction;
/* drop clr dependencies */
declare @dynamicSQL nvarchar(max)
declare @schema nvarchar(200)
declare @name nvarchar(200)
declare @type nvarchar(200)
declare db_cursor cursor for
select [schema].Name,
[objects].name,
[objects].type_desc
from sys.assembly_modules [modules]
join sys.objects [objects] with (nolock)
on [modules].object_id = [objects].object_id
join sys.schemas [schema] with (nolock)
on [objects].schema_id = [schema].schema_id
where [modules].assembly_class in (N’NAME’)
open db_cursor
fetch next from db_cursor into @schema, @name, @type
while @@FETCH_STATUS = 0
begin
set @dynamicSQL = N”;
set @dynamicSQL = case @type
when N’CLR_STORED_PROCEDURE’ then N’drop procedure’ + N’ ‘ + @schema + N’.’ + @name + N’;’
when N’CLR_SCALAR_FUNCTION’ then N’drop function’ + N’ ‘ + @schema + N’.’ + @name + N’;’
when N’CLR_TABLE_VALUED_FUNCTION’ then N’drop function’ + N’ ‘ + @schema + N’.’ + @name + N’;’
else N’Object Not Recognised’
end
–print @dynamicSQL;
exec sp_executesql @dynamicSQL;
fetch next from db_cursor into @schema, @name, @type
end
close db_cursor
deallocate db_cursor
/* drop assembly once free of dependencies */
drop assembly [NAME]
if @@trancount > 0
commit transaction;
end try
begin catch
rollback transaction;
print ‘ERROR: ‘ + error_message()
end catch
How to select a value from an XML field in SQL Server
October 3, 2013 | Posted in SQL Server
Need to select some values quickly from an XML field in SQL Server?
select
Field.value(‘(/OuterNode//InnerNode/node())[1]‘, ‘varchar(50)’) as Value
from [schema].tblTemp with (nolock)
Why can’t I add or move columns in SSMS designer view?
October 3, 2013 | Posted in SQL Server
SSMS is by default quite picky about what you can add or move without dropping and recreating a table. You can switch off this default behavior by unchecking the following option:
How can I put a Service Broker message back onto the queue, if my processing fails?
October 3, 2013 | Posted in SQL Server
One of the questions that I had in my mind, when I started working with service broker is about the safety of my messages. Service broker is a reliable messaging platform. It is capable of delivering the message to the target without fail. But what if the target received the message but could not process it?
When a queue reader program reads a message from the queue with the RECEIVE command, the message will be removed from the queue. If the RETENTION setting of the queue is set to ON, the message is not really removed from the queue, but the status is updated to 0 to indicate that the message is read and processed. (We discussed RETENTION in an earlier post). In either case, we will not be able to receive a message for a second time.
To make sure that the messages are reliably processed, we need to put the RECEIVE command in a transaction. The processing of the message should be within the transaction and if an error occurs, the transaction should ROLLBACK. When the transaction in which the RECEIVE operation is executed, is rolled back, the message will not be removed (or updated to ‘read’) from the queue. The next RECEIVE command will fetch the message once again. The following code sample explains this.
-- local variables
DECLARE @XML XML,
@MessageBody VARBINARY(MAX),
@MessageTypeName SYSNAME,
@ConversationHandle UNIQUEIDENTIFIER,
@Response XML
BEGIN TRY
WHILE (1 = 1)
BEGIN
-- start the transaction
BEGIN TRANSACTION
-- do a receive
WAITFOR (
RECEIVE TOP(1)
@MessageTypeName = message_type_name,
@MessageBody = message_body,
@ConversationHandle = [conversation_handle]
FROM dbo.QueueName
), TIMEOUT 60000
-- no messages? let us quit
IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION
BREAK
END
-- cast the messagebody to XML
SET @XML = CAST(@MessageBody AS XML);
-- check if it is the message you are looking for
IF @MessageTypeName = 'The-message-you-are-looking-for'
BEGIN
-- execute the stored procedure to process the message
EXECUTE YourProcedureThatProcessTheMessage @XML
-- construct a response message
SELECT @Response = '<construct-a-response-message/>'
-- send a response back to the caller
SEND ON CONVERSATION @ConversationHandle
MESSAGE TYPE [Your-response-message-type](@response);
-- end the conversation, if you dont need it anymore
END CONVERSATION @ConversationHandle;
END
-- commit the transaction
-- the message will be removed from the queue
COMMIT TRANSACTION
END
END TRY
BEGIN CATCH
-- roll back the transaction
-- the message will not be removed from the queue
ROLLBACK TRANSACTION
END CATCH
Please note that this could create poison messages. When you rollback the transaction and do a RECEIVE, the same message will be fetched again. It could happen that the error can occur again and the transaction will rollback again. If errors like primary-key-violation occurs, every time the message is processed the error will keep occurring. This means we will end up with an infinite loop of RECEIVE, ERROR and ROLLBACKS. I will explain poison messages in the next post.
Tags: Service Broker
Jul 31 2008 3:20PM by Jacob Sebastian
Original Source: http://beyondrelational.com/modules/2/blogs/28/posts/10454/service-broker-how-can-i-put-a-message-back-into-the-queue-if-my-processing-fails.aspx
Debugging a SQL Server non-yielding scheduler issue
October 3, 2013 | Posted in SQL Server
If you have read the How To Diagnose and Correct Errors 17883, 17884, 17887, and 17888 whitepaper, you would know what a 17883 error reported in SQL Server 2000 means. If you read the “SQL Server 2005 – SchedulerMonitor” section of the whitepaper, you will understand that the way non-yielding messages are reported for a SQL Server worker thread is much more refined than it’s predecessor. We report a non-yielding worker thread as a Non-yielding Scheduler message in the SQL Server Errorlog. Since the whitepaper is very comprehensive in explaining SQL Server non-yielding detection algorithms, I shall not reiterate the same. In this post, I shall show you how to track down the cause of a non-yielding scheduler using public symbols in conjunction with the SQL Server Errorlog and Windows Event Logs.
Below is an excerpt from an Errorlog which is reporting a non-yielding scheduler message.
2011-07-24 22:17:45.850 Server * BEGIN STACK DUMP:
2011-07-24 22:17:45.850 Server * 07/24/11 22:17:45 spid 2388
2011-07-24 22:17:45.850 Server *
2011-07-24 22:17:45.850 Server * Non-yielding Scheduler
2011-07-24 22:17:45.850 Server Stack Signature for the dump is 0x00000000000003B6
2011-07-24 22:17:53.380 Server External dump process return code 0×20000001.
External dump process returned no errors.2011-07-24 22:17:53.400 Server Process 0:0:0 (0×990) Worker 0x0000000005A6C1A0 appears to be non-yielding on Scheduler 1. Thread creation time: 12955600342903. Approx Thread CPU Used: kernel 0 ms, user 0 ms. Process Utilization 0%. System Idle 99%. Interval: 70119 ms.
There are lines that I have colored differently in the above excerpt whose significance I shall detail later in this thread. The Windows Application Event Log will report this as an Informational message with the EventID =17883. This is what I have in my application event log for the above reported non-yielding condition:
Process 0:0:0 (0×990) Worker 0x0000000005A6C1A0 appears to be non-yielding on Scheduler 1. Thread creation time: 12955600342903. Approx Thread CPU Used: kernel 0 ms, user 0 ms. Process Utilization 0%%. System Idle 99%%. Interval: 70119 ms.
The above message is the same message reported in the Errorlog excerpt above. Since the Errorlog doesn’t report the MDMP (dump file) number, you will have to track it down using the timestamp when the issue was reported or using the SQL Dumper log in case you are performing post-mortem analysis. The SQL Dumper log name is “SQLDUMPER_ERRORLOG.log” and it is located by default in the SQL Server LOG folder. You will find a message similar to the one shown below which will point you to the correct .mdmp file.
(2A30:2760) 07/24/11 22:17:52, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDump completed: C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\SQLDump0025.mdmp
Now I need to use Windows Debugging Tools to load the .mdmp file. Note that the non-yielding message in the SQL Server Errorlog reported “External dump process returned no errors.”. This means that the dump generation routine didn’t encounter any errors. The most common error that occurs while generating dumps (due to resource crunch – memory or CPU), is a timeout. This means that the dump generation timed out and it is a delayed dump. So the information contained in the dump file may or may not be useful to perform a complete post-mortem analysis. This is when Microsoft CSS would ask you to collect additional data for the next occurrence if the current data available is not sufficient for a complete post-mortem analysis.
So let’s move on to the good part now… i.e. analysis of the dump. Keep in mind that this is a mini-dump which means that the is limited amount of information stored in the dump and we are using public symbols to analyze the dump. Even with private symbols that Microsoft CSS Engineers have access to, there might not be a whole lot they can decipher from a mini-dump and might request additional data!
When you load the dump using WinDBG, you will see the following information:
Loading Dump File [C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\SQLDump0025.mdmp]
User Mini Dump File: Only registers, stack and portions of memory are availableComment: ‘Stack Trace’
Comment: ‘Non-yielding Scheduler’…
…Debug session time: Sun Jul 24 22:17:52.000 2011 (UTC + 5:30)
The above tells you that this is a mini-dump for a non-yielding scheduler condition and the location from where you loaded the dump. Then I use the command to set my symbol path and direct the symbols downloaded from the Microsoft symbol server to a local symbol file cache on my machine. It also tells me the time when the dump was generated.
What is a minidump?
A memory dump which contains the memory for the call stack of all threads, the CPU registers and information about which modules are loaded.
.sympath srv*D:\PublicSymbols*http://msdl.microsoft.com/download/symbols
Then I issue a reload command to load the symbols for sqlservr.exe. This can also be done using CTRL+L and providing the complete string above (without .sympath), checking the Reload checkbox and clicking on OK. The only difference here is that the all the public symbols for all loaded modules in the dump will be downloaded from the Microsoft Symbol Server which are available.
.reload /f sqlservr.exe
Next thing is to verify that the symbols were correctly loaded using the lmvm sqlservr command. If the symbols were loaded correctly, you should see the following output. Note the text in green.
0:019> lmvm sqlservr
start end module name
00000000`00c60000 00000000`04820000 sqlservr (pdb symbols) D:\publicsymbols\sqlservr.pdb\6A3527657A3F4090AB2C96F7008A55C52\sqlservr.pdb
Loaded symbol image file: sqlservr.exe
Mapped memory image file: C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlservr.exe
Image path: C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlservr.exe
Product version: 10.50.1790.0
If symbols were not loaded, then you will see an output as shown below.
0:019> lmvm sqlservr
start end module name
00000000`00c60000 00000000`04820000 sqlservr (export symbols) sqlservr.exe
Loaded symbol image file: sqlservr.exe
Mapped memory image file: C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlservr.exe
Image path: C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlservr.exe
Next I shall use the Thread ID noted above (0×990) in the Errorlog excerpt to switch to the thread and get the callstack for the thread which was reported as non-yielding.
0:019> ~~[0x990]s
ntdll!NtWaitForSingleObject+0xa:
00000000`76e06bda c3 ret
0:019> kL100ntdll!NtWaitForSingleObject
kernel32!WaitForSingleObjectEx
sqlservr!SOS_Scheduler::SwitchContext
sqlservr!SOS_Scheduler::SuspendNonPreemptive
sqlservr!SOS_Scheduler::Suspend
…
…
msvcr80!endthreadex
msvcr80!endthreadex
kernel32!BaseThreadInitThunk
ntdll!RtlUserThreadStart
Looking at the callstack above, I can say that this is not the cause of the non-yielding condition (99.99999% true in most cases)!! The reason I can say this is because I find SwitchContext on the thread stack which means that the thread has yielded. The sequence of events is as follows: SQL Server detected a non-yielding worker thread. By the time, SQL Dumper was initiated to capture a memory dump of the SQL Server process, the thread yielded and the processing moved on to the next work request in the queue.
However, if your dump didn’t have the top functions as the ones noted above but was something like the two functions mentioned below, then you definitely have the right callstack and the issue noted below is due to stuck-stalled I/O.
ntdll!NtWriteFileGather
kernel32!WriteFileGather
The non-yielding scheduler message from the dump I investigated which had the above callstack had the following stuck-stalled IO warnings in the SQL Server Errorlog just prior to the dump getting generated:
SQL Server has encountered 218 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2).
So now I definitely know that the non-yielding condition was due to faulty disk sub-system and the SQL Server instance at this point is just a victim of the issue!
If you see the the following message in the SQL Server Errorlog along with the dump generation message, then it is definitely a delayed dump and it could have some valuable information missing that might have been pertinent to your analysis.
Timeout while waiting for external dump
Most of the common non-yielding conditions are documented in the whitepaper that I referred to above. If you are unable to get a callstack for the non-yielding issue and the symptoms or sequence of events leading up to the issue doesn’t match any KB Article, then it’s time to call CSS to get the memory dump analyzed with the help of the private symbols that the CSS engineers have access to.
Happy debugging!!
Reference:
KB 315263 – How to read the small memory dump files that Windows creates for debugging
SQL Server ALTER DATABASE SET TRUSTWORTHY ON
October 3, 2013 | Posted in SQL Server
Service broker not working for you? Try altering this setting in your database, it may save you a lot of time!
Guidelines for using the TRUSTWORTHY database setting in SQL Server
Summary
You can use the TRUSTWORTHY database setting to indicate whether the instance of Microsoft SQL Server trusts the database and the contents within the database. By default, this setting is set to OFF. However, you can set it to ON by using the ALTER DATABASE statement. We recommend that you leave this setting set to OFF to mitigate certain threats that may be present when a database is attached to the server and the following conditions are true:
- The database contains malicious assemblies that have an EXTERNAL_ACCESS or UNSAFE permission setting. For more information, visit the following Microsoft Developer Network (MSDN) website:
- The database contains malicious modules that are defined to execute as users that are members of a group that has administrative credentails. For more information, visit the following MSDN website:
Note By default, the TRUSTWORTHY setting is set to ON for the MSDB database. Altering this setting from its default value can result in unexpected behavior by SQL Server components that use the MSDB database.
If the TRUSTWORTHY setting is set to ON, and if the owner of the database is a member of a group that has administrative credentials, such as the sysadmin group, the database owner may be able to create and to run unsafe assemblies that can compromise the instance of SQL Server.
More Information
- In an Internet service provider (ISP) environment (for example, in a web-hosting service), each customer is permitted to manage their own database and is restricted from accessing system databases and other user databases. For example, the databases of two competing companies could be hosted by the same ISP and exist in the same instance of SQL Server. Dangerous code could be added to a user database when the database was attached to its original instance, and the code would be enabled on the ISP instance when the database was deployed. This situation makes controlling cross-database access crucial.
- If the same general entity owns and manages each database, it is still not a good practice to establish a “trust relationship” with a database unless an application-specific feature, such as cross-database Service Broker communication, is required. A trust relationship between databases can be established by enabling cross-database ownership chaining or by marking a database as trusted by the instance by using the TRUSTWORTHY property. The is_trustworthy_on column of the sys.databases catalog view indicates whether a database has its TRUSTWORTHY bit set.
Best practices for database ownership and trust include the following:
• Have distinct owners for databases. Not all databases should be owned by the system administrator.
• Limit the number of owners for each database.
• Confer trust selectively.
• Leave the Cross-Database Ownership Chaining setting set to OFF unless multiple databases are deployed at a single unit.
• Migrate usage to selective trust instead of using the TRUSTWORTHY property.
The following code sample can be used to obtain a list of databases that have the TRUSTWORTHY bit ON and whose database owner belongs to the sysadmin server role.
- SELECT SUSER_SNAME(owner_sid) AS DBOWNER, d.name AS DATABASENAME
- FROM sys.server_principals r
- INNER JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id
- INNER JOIN sys.server_principals p ON
- p.principal_id = m.member_principal_id
- inner join sys.databases d on suser_sname(d.owner_sid) = p.name
- WHERE is_trustworthy_on = 1 AND d.name NOT IN (‘MSDB’) and r.type = ‘R’ and r.name = N’sysadmin’
You can run the following query to determine the TRUSTWORTHY property of the MSDB database:
select name, TrustWorthySetting =
case is_trustworthy_on
when 1 then ‘TrustWorthy setting is ON for MSDB’
ELSE ‘TrustWorthy setting is OFF for MSDB’
END
from sys.databases where database_id = 4
If this query shows that the TRUSTWORTHY property is set to OFF, you can run the following query to set the TRUSTWORTHY property to its default value.
ALTER DATABASE MSDB SET trustworthy ON
go
The following table provides more information on the products or tools that automatically check for this condition on your instance of SQL Server and the versions of the SQL Server product that the rule is evaluated against.
| Rule software | Rule title | Rule description | Product versions against which the rule is evaluated |
| System Center Advisor | SQL Server msdb system database has trustworthy option set to OFF | System Center Advisor checks whether the database option Trustworthy is set to OFF for the msdb system database for this instance of SQL Server. If it is set to OFF, then advisor generates an alert. Review the information provided in the “Information Collected” section of the advisor alert and follow the resolutions that are provided in this article. | SQL Server 2008 SQL Server 2008 R2 |
| SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) | Trustworthy Bit | The SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) provides rule to detect when a database has the TRUSTWORTHY property set to ON and whether the database owner is member of the sysadmin server group. If you run the BPA tool and receive a warning that is titled “Engine – Trustworthy Bit,” we recommend that you run the query that is listed in the previous section to identify the database that has the TRUSTWORTHY bit set and that you consider changing the setting to OFF. | SQL Server 2008 SQL Server 2008 R2 |
| SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA) | Trustworthy Bit | The SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA) provides rule to detect when a database has the TRUSTWORTHY property set to ON and whether the database owner is member of the sysadmin server group. If you run the BPA tool and receive a warning that is titled “Engine – Trustworthy Bit,” we recommend that you run the query that is listed in the previous section to identify the database that has the TRUSTWORTHY bit set and that you consider changing the setting to OFF. | SQL Server |
