Year / 2010

by Arvind Shyamsundar

Replication and Linked Servers

Problem

I recently hit upon an issue with trying to setup a linked server to an instance which was already a subscriber to a publication. When replication is setup, it actually creates a remote server for the subscriber. However that ‘remote server’ is not configured for data access. So if you try to use that server, you would end up with:

Msg 7411, Level 16, State 1, Line 1
Server ‘foosub’ is not configured for DATA ACCESS.

Also, any attempt to add a similarly named linked server would fail with the error message below:

EXEC master.dbo.sp_addlinkedserver @server = N’foosub’, @srvproduct=N’SQL Server’

Msg 15028, Level 16, State 1, Procedure sp_addlinkedserver, Line 82
The server ‘foosub’ already exists.

Troubleshooting

Next, I tried to add a linked server (using the SQL Native Client) but with a different name (MYSRV) but pointing to the right server (foosub). My initial attempt yielded the following error:

The linked server has been created but failed a connection test. Do you want to keep the linked server?

——————————
ADDITIONAL INFORMATION:

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

——————————

Named Pipes Provider: Could not open a connection to SQL Server [53].
OLE DB provider "SQLNCLI10" for linked server "MYSVR" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "MYSVR" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". (Microsoft SQL Server, Error: 53)

Solution

The problem turned out that I had not used the right ‘provider’ string. Here is the script which finally worked for me:

EXEC master.dbo.sp_addlinkedserver @server = N’MYSVR’, @srvproduct=N’foosub’, @provider=N’SQLNCLI10′, @provstr=N’Server=foosub;Database=master;Trusted_Connection=yes’

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N’MYSVR’, @locallogin = NULL , @useself = N’True’
GO

Or if you are more comfortable using the SQLOLEDB provider, here’s a sample:

EXEC master.dbo.sp_addlinkedserver @server = N’MYSRV’, @srvproduct=N’SQLOLEDB’, @provider=N’SQLOLEDB’, @datasrc=N’foosub’, @provstr=N’Data Source=foosub;Initial Catalog=master’, @catalog=N’master’

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N’MYSRV’, @locallogin = NULL , @useself = N’True’
GO

You can also do this from the SSMS GUI by using ‘‘SQLNCI10’ or ‘SQLOLEDB’ as the Provider.

With this, I can subsequently access remote tables as such:

SELECT * FROM MYSVR.master.sys.tables

Hope this is useful! Please leave a comment if you find it useful.

by Arvind Shyamsundar

My Favorite SQL Server Blogs

At our workshops and during other customer interactions, we are usually asked for links to good blogs and reading materials. Here is a list of my favorite SQL-related blogs, arranged in no specific order.

http://blogs.msdn.com/craigfr: Craig Freedman on Query Processing (QP)

http://blogs.msdn.com/sqlprogrammability: Plan cache, parameterization etc.

http://blogs.msdn.com/sqlserverstorageengine: Storage Engine

http://blogs.msdn.com/repltalk: Replication support team

http://blogs.msdn.com/psssql: CSS SQL Support team

http://www.sqlskills.com/blogs/PAUL: Paul Randal with lots of tips

http://blogs.msdn.com/sqlqueryprocessing/default.aspx: Query Processing again

http://blogs.msdn.com/sqlserverfaq: Other CSS SQL Support team members

http://blogs.msdn.com/davidlean: Good series of posts on SQL Spatial features

http://blogs.msdn.com/sql_pfe_blog: Our global SQL PFE team blog

http://blogs.msdn.com/robertbruckner: Good information on SSRS

http://www.sqlcat.com: The SQL Server Customer Advisory Team (SQLCAT)

http://blogs.msdn.com/sqlreleaseservices: Learn about the latest releases (Service Pack / Cumulative Update) from SQL product team

I will be updating this list as and when I remember / discover other useful blogs. I hope this is useful for the community!

by Arvind Shyamsundar

Hack of the day: Shrink all log files in the instance

Before I proceed, I will add two disclaimers:

  1. This script is provided as-is for knowledge purposes. It is not a recommendation, or in any way intended for production usage. No warranty or guarantee is made about the correctness of the script. Use it at your own risk.
  2. We do not recommend using SHRINK operations on any production databases. This script is provided for demonstration purposes only and that too for test or development servers. For more details, please refer to the series of posts by Paul Randal at http://www.sqlskills.com/BLOGS/PAUL/category/Shrink.aspx

With that behind us, here’s what I would like to share. On my laptop I keep running large queries and many times those will grow the log file. Though my database is in simple recovery mode, a single long running transaction can cause this kind of growth.

So I would sometimes like to reclaim disk space (oh so precious on a laptop!) periodically. I would however not like to shrink the data file, just the log file. (Shrinking data files can cause fragmentation issues to occur.)

The problem is I normally operate on like 5-6 databases on my laptop. I would not want to use the GUI nor script this manually each time. So here is a simple script-generator, which will generate the necessary DBCC SHRINKFILE commands, which you can then execute as per your requirement.

drop table #f
go
create table #f (name sysname, fileid int, filename sysname, filegroup sysname null,
size sysname, maxsize sysname, growth sysname, usage sysname)
go
exec sp_MSforeachdb ‘declare @s varchar(8000); use ?;
truncate table #f; INSERT #f (name, fileid, filename, filegroup, size, maxsize, growth, usage )
exec sp_helpfile
select @s = ”use ?; DBCC SHRINKFILE (” + name + ” )” from #f where usage = ”log only”
print @s ‘

Really crude script, but it does save me some time once in a while.

by Arvind Shyamsundar

Priority boost details – and why it’s not recommended

Some times, we see customer has (accidentally or otherwise) enabled the option ‘boost priority’ for SQL Server worker threads. In general Microsoft does not recommend that you set this option. Why?

First a bit of background. When we set the ‘priority boost’ option using sp_configure what is happening is that after restart the SQL engine will call Win32 API SetPriorityClass() and passes in HIGH_PRIORITY_CLASS (if you are debugger savvy, you can set breakpoints on these APIs and check what is happening – that’s what I did, no source code is required to verify this). From MSDN:

HIGH_PRIORITY_CLASS

0x00000080

Process that performs time-critical tasks that must be executed immediately. The threads of the process preempt the threads of normal or idle priority class processes. An example is the Task List, which must respond quickly when called by the user, regardless of the load on the operating system. Use extreme care when using the high-priority class, because a high-priority class application can use nearly all available CPU time.

It then proceeds to call SetThreadPriority() with priority as THREAD_PRIORITY_HIGHEST. For this combination of Process Priority Class and Thread Priority Level, the base priority level of these worker threads is 15. The only ones higher than this in the hierarchy of the OS are any threads which have process priority class set to REALTIME_PRIORITY_CLASS (which should be a very rare case for any application.) this means that many SQL worker threads are running at a priority level which is close to the highest on the system. Hence, they will tend to be selected frequently by kernel dispatcher to execute on the CPU.

So what is the effect?

There is clear precedent in the support teams of priority boost causing unresponsive servers. Sluggish UI / mouse / keyboard movements are other common symptoms if this setting is interfering with the capability of the OS to give (non-SQL) threads their desired quantum on the CPU. On a cluster, having priority boosted SQL threads can cause other critical threads such as the resource monitor’s IsAlive poll thread to timeout, thereby causing unwanted failover. Therefore we do not recommend to set priority boost to 1, especially in clustered instances.

Reference links:

SetPriorityClass: http://msdn.microsoft.com/en-us/library/ms686219(VS.85).aspx

SetThreadPriority: http://msdn.microsoft.com/en-us/library/ms686277(VS.85).aspx

Effective Base Priority: http://msdn.microsoft.com/en-us/library/ms685100(VS.85).aspx

Windows Priority levels: http://www.microsoft.com/mspress/books/sampchap/4354c.aspx and http://www.microsoft.com/mspress/books/sampchap/4354d.aspx