DavidWimbush

I was spring cleaning some old scripts and I came across my first attempt at establishing a backup strategy. 12 hours later I'm still feeling a bit queasy. When I first put on the DBA hat, we were running with the simple recovery model and doing a full backup every night. I did some homework and thought I'd got it all figured out. My killer improvement? Use full recovery and every night do a log backup and then a full backup. That way we could recover to any point in time. Brilliant!*

Since then I've learned a lot. I know I'm much better than I was. But I can never quite shake that small nagging doubt: how much of what I'm doing now - believing it's good practice - will I one day look back on and shudder? But this is a good thing. It keeps me on my toes, continually re-evaluating what I'm doing, and hungry to learn.

*Just to be clear, I'm being sarcastic. This NOT a good solution. If you have any doubts:

1. Read Books Online about recovery models and backups.
2. Read what Paul Randal has written about it in his blog and in TechNet Magazine.
3. Check out Ola Hallengren's database maintenance solution before you write your own.

Nearly three years ago I wrote about how to query the ReportServer database and see who is running which reports. Since then I've made it a bit more focused. Which reports are being used the most? Which users are the busiest and which reports are they looking at? Here's what I came up with.

(NB: This is for 2008 or 2008 R2 Reporting Services in standalone mode. I've no idea what you get in SharePoint mode.)

Report Heatmap

Which reports are being used the most?

If you click on a report you see...

Report Usage

Who has been using that report?

Report User Heatmap

Who are the busy users?

If you click on a user you see...

Report User History

Which reports has that user has been running?

Feel free to download the RDL files and stored procedures and use them yourself.

Here's a funny thing I discovered while working with a table I created by importing data from Excel. (I know, I only have myself to blame.) A number of the column names ended up with a trailing space on the end. They were Y/N type columns where the value was either Y or null. I decided to update all the nulls to Ns so the nulls wouldn't trip me up later. So I typed a query like this: update <table> set <column> = isnull(<column>, 'N'). I noticed Intellisense had underlined the column name but I knew what I was doing and assumed it was still refreshing its cache. The update ran fine but when I tried to select that column I got a 'no such column' error. WTF?

I can only assume this is a side-effect of the way you can update things like rowset functions and not just tables. I've done a bit of playing around and found that (surprise surprise) some variants will work and some will fail. Try it for yourself:

create table dbo.TestTable
(
 [TestColumn ] varchar(20) null
);
go

--This update will give the result: (0 row(s) affected)
update dbo.TestTable
set TestColumn = 'hello';
go

--But this update will fail:
--Msg 207, Level 16, State 1, Line 2
--Invalid column name 'TestColumn2'.
update dbo.TestTable
set TestColumn2 = 'hello';
go

Interesting stuff, particularly if you're doing BI. BISM tabular and Power View will not be in Standard Edition, only in the new - presumably more expensive - Business Intelligence Edition. That kind of makes sense as you need a fairly pricey edition of SharePoint to really get all the benefits, but it's a shame there won't be some kind of limited version in Standard Edition. And Always On will be in Standard Edition but limited to 2 nodes. I really expected Always On to be Enterprise-only so this is a great decision. It allows those of us working at a more modest scale to benefit and raises the fault tolerance of SQL Server as a product to a new level.

Read all about it here: http://www.microsoft.com/sqlserver/en/us/future-editions/sql2012-editions.aspx

I just lost half an hour of my life which I'll never get back on this. Don't make the same mistake. I extracted some customer details for a mailing (it's OK, they ticked the box that they want to hear about our special offers) but Marketing complained that some of the address data contained nulls. I checked the spreadsheet I'd given them and they were right. So I checked my query and followed the trail back to the source record for an example. The value in AddressLine2 was Null. But my select statement said isnull(AddressLine2, '') as AddressLine2 and it was returning Null. WTF?! The sharp-eyed reader has probably already noticed that the value was the string 'Null', not the empty value NULL. I got there in the end but it took a while. Good job it wasn't upper case or I might have had to ask one of the developers for help!

If you get this problem and it's upper case you could try using the lower() function which will change the case of 'NULL' but not NULL:

declare @a varchar(10) = null;
declare @b varchar(10) = 'NULL';
select lower(@a) as a, lower(@b) as b;

We had an interesting problem while I was on holiday. (Why doesn't this stuff ever happen when I'm there?) The sysadmin upgraded our Exchange server to Exchange 2010 and everone's subscriptions stopped. My Subscriptions showed an error message saying that the email address of one of the recipients is invalid. When you create a subscription, Reporting puts your Windows user name into the To field and most users have no permissions to edit it. By default, Reporting leaves it up to exchange to resolve that into an email address. This only works if Exchange is set up to translate aliases or 'short names' into email addresses. It turns out this leaves Exchange open to being used as a relay so it is disabled out of the box. You now have three options:

1. Open up Exchange. That would be bad.
2. Give all Reporting users the ability to edit the To field in a subscription. a) They shouldn't have to, it should just work. b) They don't really have any business subscribing anyone but themselves.
3. Fix the report server to add the domain. This looks like the right choice and it works for us. See below for details.

Pre-requisites:

• A single email domain name.
• A clear relationship between the Windows user name and the email address. eg. If the user name is joebloggs, then joebloggs@domainname needs to be the email address or an alias of it.

Warning: Saving changes to the rsreportserver.config file will restart the Report Server service which effectively takes Reporting down for around 30 seconds. Time your action accordingly.

Edit the file rsreportserver.config (most probably in the folder ..\Program Files[ (x86)]\Microsoft SQL Server\MSRS10_50[.instancename]\Reporting Services\ReportServer). There's a setting called DefaultHostName which is empty by default. Enter your email domain name without the leading '@'. Save the file. This domain name will be appended to any destination addresses that don't have a domain name of their own.

The simple answer: very! Thanks very much to everyone who was involved in making it happen - both organisers and speakers. For a bargain price I got a deep dive day on performance monitoring and troubleshooting and two days of a wide variety of subjects. And it's not just people regurgitating Books Online. This is practical, 'street smarts' know-how from people who really do this stuff for a living. If you're not sure how to pay for it, here's one approach. Last time I went to the free day and my boss was so impressed with all the good stuff I brought back that, when I asked if he would pay for the full monty this time, he didn't even hesitate.

You get much more than just information though: there are other, less tangible benefits. It's as good as a holiday to just geek out on SQL stuff and be exposed to aspects you haven't used much. I came back full of new ideas and raring to make a difference. You also shouldn't underestimate the boost you get from just mingling with others of our kind. I don't know what it's like where you work but at my company I am the DBA team and it gets a bit lonely sometimes.

Then there's the chance to meet the rock stars and learn from them. We're lucky to work in a largely fact-based field where just having a loud voice doesn't get you very far. These people are prominent because they're good at their thing. I've now met quite a few and, without exception, they've been very approachable and helpful. This time I got to chat with Aaron Bertrand and Kevin Kline and, apart from being great SQL guys, they were both really good company.

Half the SQL Server CAT Team were over from Redmond and available all three days to talk about anything. There are sponsor companies showing you there products. And not just sales staff. At one stand I was looking into a monitoring product and it turned out the guy showing me round it was the lead developer. You can find out more in five minutes like this than you can in an hour of reading specs and documentation on their website.

Last but not least, rest assured that there is swag. A year's subscription to SQL Server Magazine, 6 months' access to an on-line library of nearly 700 technical books, serious prize draws galore, and so on. And I won second prize in the SQL trivia bingo!

I'm sure I'm missing plenty of other stuff. SQL Bits is not a trade convention. It's SQL Server people getting together, sharing what they know, and learning from each other.

The BBC has a rather sobering article about time zones here: http://www.bbc.co.uk/news/world-12849630. Fascinating stuff that explains how time zones work and how dates and times came to be so complicated.

Next time somebody gives you an earful about how your query gave them the wrong data because of some subtlety about the time portion of a datetime, a time zone difference, or daylight saving time, you could always point them to this. It might make you look less bad. (I'm just saying.)

I had an odd one yesterday. I was sorting out internal fragmentation in our transaction logs (see Kim Tripp's blog http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx) and I noticed that on our core production server the model database transaction log was over 200MB and had 163 virtual log files (VLFs). Clearly something was logging transactions in there and causing repeated log file growth. But what? Stumped, I asked Ask SQL Server Central and found out, thanks to 'meltondba'. (I suspect that's not his real name.) The model database defaults to the full recovery model and we're backing up the system databases every hour on this server. As explained by CSS here http://blogs.msdn.com/b/psssql/archive/2009/11/24/doctor-this-sql-server-appears-to-be-sick.aspx, a full backup is recorded in the transaction log. And of course if you stay in full recovery and never clear out the transaction log it will keep on growing.

What's the problem with this? Well, there are several. The model database is the template from which new databases are created. I created a new database on this server (create database [dw]) and it had a 3MB data file and a 200MB log file with 3 VLFs. The disk space for the log file and backups, and the time required for backups. OK it's not major but it all adds up. If enough 'small stuff' like this accumulates it can really dent the performance of your system.

What should you do? I'd suggest changing the model database to the simple recovery model. You don't want new databases to be in full recovery unless you're going to actively manage the logs, and you don't want to bother with that unless you need point in time recovery. Backup the log, shrink it and set it to a small size, say 3MB. Kim Tripp's blog post shows all the code for this. When you create a new database you'll want to set the file sizes and growth characteristics anyway.