SQL Server Community Blogs

NOCOUNT and linked servers

MartinBell — Sat, 11 Jul 2009 15:16:00 GMT

The other day I was looking at calling remote stored procedures on a linked server when I hit upon this problem or feature, depending on you point of view!!...(read more)

Best practices on setting NOCOUNT

MartinBell — Sat, 11 Jul 2009 15:15:00 GMT

Setting NOCOUNT ON in stored procedures can have performance benefits, but that is not the only reason to SET NOCOUNT....(read more)

String concatenation with max types stops plan caching

simonsabin — Sat, 11 Jul 2009 14:57:22 GMT

Erland Sommarskog (http://www.sommarskog.se/) was recently investigating delimited strings and was finding odd behaviour, which he wanted verifying. The behaviour was that he was getting recompiles with certain functions he was using for string splitting.

I had a look and found exactly the same behaviour.

I started dissecting the code and found the issue was with string concatenation and use of tables. These two sps identify the problem

create procedure usp_CollationBug

@param nvarchar(max)

SELECT top 1 @param +N',' AS output

FROM sys.objects

create procedure usp_CollationBugTest

dbcc freeproccache

exec usp_CollationBug N'1,2,3'

SELECT o.name,*

FROM sys.dm_exec_cached_plans cp

OUTER APPLY (sys.dm_exec_query_plan(cp.plan_handle) qp

LEFT JOIN sys.objects o ON qp.objectid = o.object_id)

WHERE qp.dbid = db_id()

and o.name = 'usp_CollationBug'

ORDER BY o.name

If you execute usp_CollationBugTest then you will find that there is no record returned in the last query, i.e. there isn't a cached query plan.

So what is causing it. Well if you take off the string concatenation (+ N',') or the FROM sysobjects you will get a query plan cached

alter procedure usp_CollationBug

@param nvarchar(max)

SELECT @param +N',' AS output

exec usp_CollationBugTest

alter procedure usp_CollationBug

@param nvarchar(max)

SELECT top 1 @param AS output

from sys.objects

exec usp_CollationBugTest

Both of these should return a cached plan.

So I thought about using a CTE, using CHAR to append the string, or using varchar and not Nvarchar

alter procedure usp_CollationBug

@param nvarchar(max)

;with param as(select @param + ',' value)

SELECT top 1 param.value AS output

from sys.objects

cross join param

exec usp_CollationBugTest

alter procedure usp_CollationBug

@param nvarchar(max)

SELECT top 1 @param + char(44) AS output

from sys.objects

exec usp_CollationBugTest

alter procedure usp_CollationBug

@param varchar(max)

SELECT top 1 @param +',' AS output

from sys.objects

exec usp_CollationBugTest

But unfortunately none of these work.

So far I've found two solutions, one is to force the collation of the expression and the second is to not use MAX datatypes

alter procedure usp_CollationBug

@param nvarchar(max)

SELECT top 1 @param + N',' COLLATE SQL_Latin1_General_CP1_CI_AS AS output

from sys.objects

exec usp_CollationBugTest

alter procedure usp_CollationBug

@param nvarchar(4000)

SELECT top 1 @param + N',' COLLATE SQL_Latin1_General_CP1_CI_AS AS output

from sys.objects

exec usp_CollationBugTest

I thought about this more and thought about using STUFF, I came up with the following solution that does result in a cached plan

alter procedure usp_CollationBug

@param nvarchar(max)

SELECT top 1 stuff(@param,LEN(@param),1, right(@param,1) + N',') AS output

from sys.objects

exec usp_CollationBugTest

I then thought about my post when is varchar(max) not a varchar(max) This highlights that you have to make sure your input variables are of a max type to get a max type out. So I tried the following, and found it worked.

alter procedure usp_CollationBug
@param nvarchar(max)
as
SELECT top 1 @param + cast(N',' as nvarchar(max)) AS output
from sys.objects
go
exec usp_CollationBugTest
go

Many people use MAX data types as an easy opt out, DONT as it will lead to this problem if you are concatenating

The forcing of the collation should not be a problem as you have to specify it for your server, database and columns

So now you know the solution, why is it important. Query plans are essential for the performance of queries that are regularly executed, to avoid the compile time, this is especially true in OLTP systems.

If you do have a query that is executed 100s of times per second , avoiding compilation will more than likely improve performance

CTE in a View

Madhivanan — Sat, 11 Jul 2009 10:51:00 GMT

It is possible to use Common Table Expression in a View. But newbies wonder why the following is not possible create view numbers as with numbers ( n ) as ( select 1 as n union all select n + 1 from numbers where n < 10000 ) select n from numbers option ( maxrecursion 0 ) Which when created results to the error Msg 156, Level 15, State 1, Procedure numbers, Line 15 Incorrect syntax near the keyword 'option'. The correct way of doing it is to create a view without option ( maxrecursion...(read more)

Dependency tracking hell

simonsabin — Sat, 11 Jul 2009 09:35:54 GMT

I've recently started on a project to move a system from one database to another. The system consists of normal database objects, integration services packages and SQL agent jobs.

As part of the excercise I'm trying to identify dependencies so we make sure when we move, we move all the objects we need.

For database objects its not too bad. I am using SQL 2005 and so sqldependencies can give me these dependencies (sort of, not 100% reliable). When I get to SQL Agent jobs and SSIS packages I'm stuck. It comes down to painful had checking and lots of "Find in Files" to find dependencies

I would love a product that I could point at a table and build the dependency across jobs, packages as a minimum.

Visual Studio Database Professional GDR R2 - Support for SQL 2008

simonsabin — Sat, 11 Jul 2009 09:32:05 GMT

I've just been installing VS a fresh and went looking for the Visual Studio Database Professional GDR and found that a GDR R2 had been released that supports SQL 2008. You can download it here http://www.microsoft.com/downloads/details.aspx?familyid=BB3AD767-5F69-4DB9-B1C9-8F55759846ED&displaylang=en#filelist

So on top of all the great things that you get with GDR you also get support for all the new features in SQL 2008, including the types, the TSQL Statements, Certificates etc.

And don't forget that if you have Visual Studio Team System Developer Edition you can get the Database edition for free. In Visual Studio 2010 they will be combined into a single product.

SQLBits yn fynd Gorllewin

MartinBell — Tue, 07 Jul 2009 17:37:00 GMT

SQLBits yn fynd Gorllewin... what does that mean?...(read more)

SQL Server Technical Roll up for July 2009

tonyrogerson — Mon, 06 Jul 2009 13:05:00 GMT

Thanks to Vicki Collins for this technical rollup; some interesting stuff to check out... News Microsoft Releases SQL Server 2008 Service Pack 1 Microsoft has released SQL Server 2008 Service Pack 1 with enhancements meant to ease deployment but no brand new features. http://www.eweek.com/c/a/Database/Microsoft-Releases-SQL-Server-2008-Service-Pack-1-687774/ SQL Server to Get Real-Time Boost The next release of Microsoft's SQL Server database, code-named "Kilimanjaro," will offer master...(read more)

Housekeeping backup and restore history

DavidWimbush — Mon, 06 Jul 2009 10:03:00 GMT

Every backup and restore event is logged in history tables in msdb and these tables can get out of hand, especially if you're using log shipping. Here's one way to sort it out that will catch up with the backlog and then keep your history tables trimmed.

This code will delete the oldest days worth of history as long as it's more than 30 days ago:

--Delete earliest day of backup and restore history if it's older than 30 days.
--This way you can clear a long history a bit at a time.
 
use msdb
go
 
set nocount on
 
declare @oldest_backup_date    smalldatetime
    , @oldest_restore_date    smalldatetime
    , @oldest_date        smalldatetime
 
 
--Find the oldest backup date (can be null)
select    @oldest_backup_date = min(backup_finish_date) 
from    backupset with (nolock) 
where    backup_finish_date is not null
 
set @oldest_backup_date = isnull(@oldest_backup_date, '01-JUN-2079')
 
 
--Find the oldest restore date (can be null)
select    @oldest_restore_date = min(restore_date) 
from    restorehistory with (nolock) 
where    restore_date is not null
 
set @oldest_restore_date = isnull(@oldest_restore_date, '01-JUN-2079')
 
 
--Determine the earlier of the two dates
if @oldest_restore_date < @oldest_backup_date
    set @oldest_date = @oldest_restore_date
else
    set @oldest_date = @oldest_backup_date
 
print 'Oldest date is ' + convert(varchar(10), @oldest_date, 103)
 
 
--If it's more than 30 days ago delete it
if @oldest_date >= dateadd(day, -30, getdate())
begin
    print 'no action'
end
else
begin
    print 'deleting history'
 
    --sp_delete_backuphistory deletes everything earlier than @oldest_date.
    --Add 1 day otherwise it won't delete anything.
    set @oldest_date = dateadd(day, 1, @oldest_date)
 
    exec sp_delete_backuphistory @oldest_date
end

Before you implement it though, I advise you to add some indexes to msdb. Normally I wouldn't screw around with system tables but I made an exception here. These tables are poorly indexed and adding some indexes makes so much difference. I found this post on Geoff Hiten's blog and brought the script up to date:

-- msdb performance indexes for SQL Server 2005
-- History:
-- 12/12/2005 Geoff N. Hiten Created (http://weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tuning.aspx).
-- 06/07/2009 David Wimbush   Commented out indexes that Microsoft have implemented.
--                            Added an index on restorehistory restore_date.
 
use msdb 
go 
 
--backupset 
--implemented by Microsoft
--create index IX_backupset_backup_set_id on backupset(backup_set_id) 
--go 
--implemented by Microsoft
--create index IX_backupset_backup_set_uuid on backupset(backup_set_uuid) 
--go 
create index IX_backupset_media_set_id on backupset(media_set_id) 
go 
create index IX_backupset_backup_finish_date on backupset(backup_finish_date) 
go 
create index IX_backupset_backup_start_date on backupset(backup_start_date) 
go 
 
--backupmediaset 
--implemented by Microsoft
--create index IX_backupmediaset_media_set_id on backupmediaset(media_set_id) 
--go 
 
--backupfile 
--implemented by Microsoft
--create index IX_backupfile_backup_set_id on backupfile(backup_set_id) 
--go 
 
--backupmediafamily 
--implemented by Microsoft
--create index IX_backupmediafamily_media_set_id on backupmediafamily(media_set_id) 
--go 
 
--restorehistory 
--implemented by Microsoft
--create index IX_restorehistory_restore_history_id on restorehistory(restore_history_id) 
--go 
--implemented by Microsoft
--create index IX_restorehistory_backup_set_id on restorehistory(backup_set_id) 
--go 
--NEW:
create index IX_restorehistory_restore_date on restorehistory(restore_date) 
go 
 
--restorefile 
create index IX_restorefile_restore_history_id on restorefile(restore_history_id) 
go 
 
--restorefilegroup 
create index IX_restorefilegroup_restore_history_id on restorefilegroup(restore_history_id) 
go 

Roll your own log shipping

DavidWimbush — Sun, 05 Jul 2009 21:02:00 GMT

I tried to use SQL Server’s built-in log shipping to set up a disaster recovery facility but I found there were some serious limitations. So, after some research, I decided to do it myself. Here’s what happened.

Requirements

A disaster recovery plan is useless unless you can test it, preferably at least once a year. To be really sure, I think you need to prove you can fail over, use, and fail back the production systems. It’s all very well testing on a test setup – you have to make sure it works – but there’s no substitute for confirming that it actually works in the environment it’s meant to preserve. There are so many things that have to fit together and, in a disaster situation, you really don’t want find yourself saying “Crap, we didn’t think about that!” and explaining to your boss that the business is going to be down for a day or two instead of the hour or two you’d confidently promised.

So you have to be able to smoothly fail over to the standby servers, have all the data consuming systems gracefully adapt to the new data sources, confirm they all work, and then reverse the process. Without losing any data. At least once a year. Easy!

Some specific technical requirements:

Setup and configuration must be scriptable. I don’t want to screw around with multi-page wizards and write reams of documentation about it. If I have to rebuild the server I want to get it done quickly and easily, and scripts are the way.
I need it to tell me if it goes wrong, and then I need some tools to help me understand where I am and how I got there so I can fix it.
When failed over, I want to be able to log ship back the other way. In a real disaster this is probably not an issue but it’s vital for testing the solution. Once live transactions have been applied to the standby server you have to log ship them back to the normal live server or lose them.
I decided to have a time lag of 3 hours before transactions are applied to the standby server. We thought we could use this to give us a chance to rescue data that was deleted by mistake.

Trying Out-of-the-Box Log Shipping

I filled in about 11 pages of wizard, setting up the source, standby and monitor. Then I captured the script, thinking I could just copy it for each database and find and replace the database name. But the script doesn’t run! I spent quite a while searching for how to fix and couldn’t find an answer. At this point I decided I could live without the scriptability so I went through the wizard again, building a detailed installation guide for our domain.

It all looked great at first. There’s a report on the monitor server to show the progress of backups, file copies and restores. I failed over, putting the source database into standby mode ready to receive logs. But when I tried to restore the standby database right up to date there was no way to override the 3 hour delay I’d specified. I went into the wizard, took out the delay, restored all logs and brought the database online. My test application adapted to the new data source. Not ideal in a real disaster with everyone breathing down my neck but I can live with it.

Now to set up log shipping back the other way. I go into the wizard and it won’t let me set up the monitor. Apparently you can only monitor shipping between two servers in one direction. If you remove the initial shipping you can set it up going the other way. So that’s about 11 pages of wizard per database to failover, and the same to fail back. That’s enough of that!

My Way

I looked around hoping to find that somebody else had already done a full solution but I couldn’t find one. I felt Pop Rivetts Does Log Shipping came the closest so I cannibalised it into what I wanted.

I have the following jobs on both servers:

LogShipBackup
Does a log backup on selected databases, compresses the file with WinRAR, copies it to a share on the other server, and does housekeeping on the files and the backup/respore history tables in msdb. The copy stage copies over any files that are not on the other side so it can catch up if connection has been lost for a bit.

LogShipBackupAlert
Sends me an email if the LogShipBackup job has not run recently and successfully. LogShipBackup will send me an email if it fails but that won’t alert me if it doesn’t run.

LogShipRestore
Decompresses and restores logs in sequence on selected databases, and does housekeeping on the files and the backup/respore history tables in msdb. You can specify a delay so that logs aren’t applied immediately. It checks the backup history so it doesn’t trip up if a file is copied over again. It actively kills connections to a database to ensure the restore can go ahead.

LogShipRestoreAlert
Sends me an email if the LogShipRestore job has not run recently and successfully. LogShipRestore will send me an email if it fails but that won’t alert me if it doesn’t run.

LogShipFailOver
Disables the LogShipBackup and LogShipBackupAlert jobs, backs up the tail of the log on selected databases, puts them in read-only standby mode, and copies the backup files over to the other server. The copy stage copies over any files that are not on the other side so it’s quite resilient.

LogShipBringOnline
Disables the LogShipRestore and LogShipRestoreAlert jobs, restores all logs in sequence on selected databases and brings those databases online.

The jobs LogShipBackup and LogShipBackupAlert are enabled on the primary server, and LogShipRestore and LogShipRestoreAlert are enabled on the standby server. I run them every 15 minutes. To fail over, run LogShipFailOver on the primary and then LogShipBringOnline on the standby server.

Apart from the alert jobs, each job has one or more steps that perform the appropriate actions on a database that is shipping or receiving logs. The jobs rely on a set of stored procedures installed in both servers’ master databases.

The code is available for download here. If you’re interested, please feel free to check it out and use it if you want to. If it doesn’t quite do what you want you can always fix that. I’d be interested to know what you think of it. I’d also be very keen to hear from you if you’ve found ways round the problems I found with the built-in log shipping.

Recommended Reading
To find out more about log shipping I recommend the following:

Brad McGehee’s article about doing it yourself. It dates back to SQL 2000 days but log shipping still seems to work just the same.
Paul Randal’s posts about how backup and recovery work.

Leeds Area SQL Server User Group Meeting, Leeds - Monday 20th July

MartinBell — Fri, 03 Jul 2009 10:39:00 GMT

Managing a large SQL Server estate If size matters Martin Cairney would have the bragging rights!! Martin Cairney Martin Cairney has over 10 years experience as a SQL Server DBA. In his time he has worked for one of the largest IT services companies in the world deploying and supporting SQL Server installations from versions 6.5 through to 2005. Martin has supported some the SQL Server estate of some major Government Departments and Financial Institutions as well as a variety of occasional consultancy...(read more)

SQL Nuggets

MartinBell — Fri, 03 Jul 2009 10:15:00 GMT

What are SQL Nuggets?...(read more)

The next SQLbits

simonsabin — Thu, 02 Jul 2009 00:43:08 GMT

We'll be announcing the date and venue for the next SQLBits on Monday

So keep posted, we've got some really exciting news.

I am really excited about the whole event. Its going to be bigger and better than the others.

ROW Constructors are dead, long live Table Value Constructors!

MartinBell — Wed, 01 Jul 2009 21:48:00 GMT

New documentation on ROW CONSTRUCTORS indicate the feature has had a name change to Table Value Constructors...(read more)

Cambridge usergroup meeting on Monday 6th - SQL 2008

simonsabin — Tue, 30 Jun 2009 13:19:11 GMT

Don't forget if you are in the Cambridge area on Monday (6th July 2009) we are doing another usergroup meeting.

This one is going to be on SQL Server 2008 features.

We are holding this at the head quarters of Redgate software who are kindly sponsoring the event.

To register go to http://sqlserverfaq.com/events/183/SQL-Server-User-Group-Cambridge-SQL-Server-2008.aspx

I look forward to seeing you there.

Don't destory your certificate if you dabble with SQL 2008 Encryption

simonsabin — Tue, 30 Jun 2009 13:11:37 GMT

Martin is continuing to post some great new posts on his blog. I thought the last one is a real nasty one.

http://sqlblogcasts.com/blogs/martinbell/archive/2009/06/29/After-disabling-tde-you-still-requires-certificates-to-restore-the-database.aspx

If you have enabled TDE on a database and encrypted some data then remove the TDE and backup, you may not be able to restore that backup if you don't have the certificate.

I am guessing that this is to do with the encryption when you turn TDE on being a background task, I expect the same is true in reverse, i.e. pages stay encrypted until a background process decrypts them.

I wonder if the database ever gets back to a totally unencrypted version

How to stop that annoying beep on your server

simonsabin — Tue, 30 Jun 2009 09:45:37 GMT

Many years ago I was working on a project that had some large batch processing in it. To do this processing we had some dedicated servers, one of which sat under the desk next to me.

Apart from the noise of the fan soudning like we had actually bought a jet fighter the most annoying thing about this processing was that it resulted caused the servers beep to continually sound. Something in the code caused it to fire.

Boy it was annoying.

We were never able to find out how to stop it, I think in the end we disconnected it. Well last week I found out and it was so annoyingly simple.

net stop beep

Yep thats it, thanks to the trainer that pointed this out last week. As you can imagine, having 12 training machines all beeping is not something you really want

To permanently stop it you have to disable the service using

sc config beep start= disabled

Whats really odd is that beep doesn't appear in the list of services

After disabling TDE you still requires the certificate to restore the database

MartinBell — Mon, 29 Jun 2009 22:46:00 GMT

Missing certificates can cause problems when restoring databases even if they are not being used!...(read more)

SQL Server version has a known compatibility issues with Windows 7 RC. This warning is genuine and applies to all SQL Server editions, including all Express editions.

ssqa.net — Mon, 29 Jun 2009 10:35:00 GMT

There is no need to guess about the rootcause of the above error message, as it clearly mentions about compatibility issues as a warning.... read more !...(read more)

Hyperlinks in custom reports

simonsabin — Mon, 29 Jun 2009 07:31:50 GMT

One feature introduced in SQL Server 2005 is reporting in management studio. You have a set of standard reports that allow you to see in a nice report format output from dmvs and the default system background trace. Whats neat is that you can write your own custom reports. For those that long after the taskpad view of a database from SQL 2000 Enterprise manager with the help from Jasper Smith I created this http://sqlblogcasts.com/blogs/simons/archive/2007/03/28/Update-to-the-taskpad-custom-report.aspx

The custom report feature is almost fantastic, there are 3 main features I think make them awkward to use.

1. You can select text from the report and copy it
2. You can't add additional parameters to allow additional filtering, i.e. filter audit changes for today.
3. You can't have hyperlinks.

The last one I raised a connect item for https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240407.

If we had this you could write reports that had links to KB articles, blog posts, or internal KB systems. Similar to what most monitoring systems provide.

If you think this is a good idea then please vote on the connect item. I've been asked by the tools team to see if it is something people want.

Some other connect items that you might be interested in supporting if you think they are worthwhile.

Use expressions for data sources - https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=238233

Be able to combine data from different data sources - https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240268

Be able to run reports against SQL 2000 database - https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240476

Disk Partition Alignment Best Practices for SQL Server

StevenWhite — Fri, 26 Jun 2009 14:57:00 GMT

Just noticed that there is a MS new paper on Disk Partition Alignment.

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=5b343389-f7c9-43d0-9892-ddcf55890529

Happy weekend reading

Hopping mad frog wants to learn SQL Server

tonyrogerson — Fri, 26 Jun 2009 10:32:00 GMT

I always like working at home with my patio door open, we had a really heavy rain shower and I got a visitor. Anyway, this video is one of the instances of me chasing him/her out of my office. So much for prepping for my LiveMeeting this afternoon :) http://www.youtube.com/watch?v=vrq_0woVynE Tony....(read more)

Live Meeting: Creating a Star Schema and OLAP Cubes

sqlartist — Thu, 25 Jun 2009 19:06:00 GMT

Hi, I just finished my first live meeting for the UK SQL Server user group on the subject of creating a star schema and OLAP cubes. I think it went well, it was recorded so Ill let you all be the judge of that - presentation to follow. I spoke way too fast, Tony Rogerson told me I sound "well Cockney" - but depressing as in my own head I sound like Hugh Grant. I spoke way too fast as I am inclinded to do but even though I got through most of my content I defintely forgot loads and apologies...(read more)

MSSQL 2008 SP1 Install - Server is in script upgrade mode. Only administrator can connect at this time

StevenWhite — Thu, 25 Jun 2009 11:40:00 GMT

After taking the usual database backups etc. I went to install SP1 on a SQL 2008 server (RTM)
following the normal routine of stopping the sql server services and firing up the service pack install.

All went fine, and the service pack finished. Anyway I rebooted the server to check everything was clean on startup.

When the server came back I checked the eventlog, and that the services started fine etc.
Next I used management studio to connect to the server only to get the message
'Server is in script upgrade mode. Only administrator can connect at this time.'

Firstly I checked the sql log file and the event log - nothing to say why.
Hence while I searched the web for this message and found a MS connect article

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=416860

It turns out that the upgrade scripts run on the first startup, hence the message.
Once the scripts have completed (which took a while), evertthing was sorted and normal access restored.

Start a SQL Agent Job in SQL

DavidWimbush — Wed, 24 Jun 2009 07:58:00 GMT

How do you start a SQL Agent job in SQL? I recently found myself wanting to be able to do this and it turns out it's really easy:

exec msdb.dbo.sp_start_job 'Check Integrity'

It returns as soon as the job has started, without waiting until the job has finished. You could use it to run a job from a script or a stored procedure. But the way I use it is in the last step of a job in order to run jobs in sequence without having to guess in the schedules how to time them. No wasted time and no danger of the second job running before the first one has finished.