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.

sql server 2014

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.

Read More →

Incorrect Syntax Near ‘BEGIN’. Expecting EXTERNAL.

sql server 2014

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

Read More →

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’

Read More →

How To Script Data From A SQL Server Table

sql server 2014

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.

Read More →

How To Create Class Structure With Properties From SQL Table In VB.Net Or C#.Net

sql_server_logo

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

Read More →

Disable Specific Triggers Based On SYS.SQL_MODULES DEFINITION

sql_server_logo

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

Read More →

SQL Server Table Sizing

sql_server_logo

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

Read More →

Calculating SQL Server Table Sizes

sql_server_logo

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

Read More →

An error occurred while receiving data: ’10054(error not found)

sql_server_logo

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]

Read More →

Locked out of aspnetdb

sql_server_logo

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

Read More →

Why Can’t I See Queued Service Broker Messages In Sys.Transmission_Queue?

sql_server_logo

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 

Read More →

SQL Char Count

sql_server_logo

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

Read More →

Scale out SQL Server by using Reliable Messaging

sql_server_logo

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:

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

Read More →

Working Out Which Service Broker Queues Are Bound To Your Services

sql_server_logo

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

Read More →

Printing Blocks Of SQL

sql_server_logo

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*/

Read More →

Debugging Remote SQL Server Service Broker Configuration

sql_server_logo

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

Read More →

Stopping And Starting SQL Server Agent Using Batch Files

sql_server_logo

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.

Read More →

Disaster Recovery: What to do when the SA account password is lost in SQL Server 2005

sql_server_logo

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

Read More →

SQL Server Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

sql_server_logo

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

Read More →

SET NOCOUNT ON Improves SQL Server Stored Procedure Performance

sql_server_logo

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>
<@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
SELECT @p1, @p2
GO

– =============================================
– example to execute the store procedure
– =============================================
EXECUTE <procedure_name, sysname, proc_test> <value_for_param1, , 1>, <value_for_param2, , 2>
GO

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
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

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/

Read More →

Creating A SQL Server Table, The Easy Way

sql_server_logo

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.

Read More →

Debugging SQL Server Service Broker Configuration

sql_server_logo

October 3, 2013 | Posted in SQL Server

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.

Read More →

Drop SQL CLR Assembly And It’s Dependencies

sql_server_logo

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

Read More →

How to select a value from an XML field in SQL Server

sql_server_logo

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)

Read More →

Why can’t I add or move columns in SSMS designer view?

sql_server_logo

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:

Read More →

How can I put a Service Broker message back onto the queue, if my processing fails?

sql_server_logo

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

Read More →

Debugging a SQL Server non-yielding scheduler issue

sql_server_logo

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 available

Comment: ‘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> kL100

ntdll!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

Original source: http://troubleshootingsql.com/2011/08/13/debugging-a-non-yielding-scheduler-issue/

Read More →

SQL Server ALTER DATABASE SET TRUSTWORTHY ON

sql_server_logo

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:

    CLR Integration Security

  • 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:

    Extending Database Impersonation by Using EXECUTE AS

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

Read More →