Admin: Sizing your transaction log

You will often hear DBA folk telling you how you need to size your transaction log appropriately and, like data files, avoid shrinking it. Why is this?

Well, lets try using a finance theme with a Credit Card analogy which records all your purchases but it also has a limit. When you fill your credit card and reach your limit you either need to pay off the balance or extend the limit by asking/begging your issuer for a bigger limit. Its possible of course (and will probably be successful) but will take up some of your time and may involve the provider asking some awkward questions. Of course, there may be times when you need to extend your limit, perhaps when you're moving house but there is a cost to it and its something you want to avoid doing too frequently. Hopefully when you set up your credit card, you do so with an appropriate limit based upon knowledge of your spending habits.

All of this is the same with a transaction log. It too has a limit which is its size and when it gets full it will either stop taking transactions or ask the operating system for more space (if set to Autogrow). Think of auto grow as akin to asking your credit card provider for that extension which will most probably granted but not without effort, here the time it takes to provision the file growth. While the file is growing, transactions will be queued up meaning your application can't process its requests and things will start to get ugly. Alternatively, rather than extend the limit of your log, you can perform a transaction log backup and "clear the balance" or if your database is in SIMPLE recovery mode, this behaviour is done on checkpoint.

Your ideal scenario is to have auto grow set on your log but with a maximum size set on the file (you don't want this credit card brining down the whole of the bank!!) and ideally it will be sized at a level which won't require too many file growth operations. You want to avoid shrinking the physical file as you don't want to repeatedly go through the "limit extension" process as its just unecessary cost. If you know your app needs 10GB, then just set it at that.

SSRS: Using SSIS as Data Source - a gotcha

Although using SSIS as a data source for SSRS isn't a supported configuration in SQL Server and has indeed been deprecated from 2008R2, i've been trying to make use of it for a particular scenario (using SQL2008). Its not the most straight forward of processes as it requires changes to config files, careful use of dataset names and plenty of security jiggery pokery but i'm not going to touch on that here.

The issue I came across was due to me having SQL2005 on my machine previous to upgrading to 2008. When I developed my report with SSIS as the datasource in BIDS, everything worked perfectly and it was only when it was deployed to the report server that I got the error message:


Initially, I thought it was a permissions problem (as thats what the majority of issues appear to be using this configuration) but after exhausting all other possibilities and actually READING the error message it became apparant that the issue was a little more fundamental than that. Logging had shown that the package wasn't even being executed and the message bears that out. The issue seemed to be more that the command being passed to the SSIS engine wasn't correct. It was then that i'd remembered that I'd upgraded and there was a good chance that it was trying to call my SQL2005 version of DTEXEC. Fortunately, i'd seen another post from Jens which suggested how the problem might be fixed.

Hey presto, changing the version of the SSIS extension element in rsreportserver.config did the trick (with a restart of the SSRS service).



TO:



So I may still have plenty of security issues to resolve and as the feature is no longer supported in SQL2008R2 and above, I doubt whether I'll go ahead and use it in Production but hopefully someone may find this useful.

Admin: Disk Configuration RAID 0+1

I saw an interesting question SQL Server Central recently on RAID Configurations. Essentially, it was asking which RAID level is more tolerant to multiple disk failures RAID 1+0 or RAID 0+1.

I have to be honest, i'd never even heard of RAID 0+1 before then and it got me thinking exactly what the configuration would look like. So i understand that 0 is striped and 1 is mirroed but how does mirror/stripe differ to stripe/mirror? Reading wikipedia gave me some information but to be honest, it still wasn't 100% clear so i had to get out my pencil and paper to try and visualise the difference.

I think i've got it, hopefully aided by the following diagram:



Essentially, if you have a RAID0+1 array, the whole sum of the disks is mirrored, not the individual disks (10 = 10). So if you have a disk failure, then the mirror is broken as there is no quorum disk to calculate the missing disk data and read requests can only be serviced by the one stripe set (7 != 10). This leaves you exposed to any disk failure in the remaining stripe set giving you a increased risk of data loss. In a RAID 1+0 array, as each disk is mirrored requests can be serviced by either array and as long as you don't suffer a failure of a pair of mirrored disks, your data is available.

I'm not quite sure what the benefits are of using one over the other. Once there has been a disk failure, I suppose you get better write performance with 0+1 as it is effectively a RAID0 array but i'm not sure that counts as a benefit. I'll be sticking with RAID 1+0.

T-SQL: The complete(?) guide to XML

As this blog is as much of a resource for me as it is for anyone else, I thought i'd share a link to a superb article written by Jacob Sebastian on using XML in SQL Server.

http://beyondrelational.com/blogs/jacob/archive/2008/06/26/xquery-labs-a-collection-of-xquery-sample-scripts.aspx

Over the past few years, I've found myself using XML in SQL Server applications more and more frequently both in ETL and in database tables. I'm not sure if this is a result of me becoming more lazy, my database design skills degrading or whether I'm just embracing the technology that MS has provided but whatever the reason, i find it incredibly useful.

Powershell: ISO Date Format

I've been looking to generate some database scripts on a regular basis and wanted to keep them in folders based upon date. As is my want, I like to keep the dates in ISO format so that they can be easily ordered through windows explorer.

The main problem I found using the DatePart functions in Powershell was that for the first 9days/months of the year, it returns just a single digit rather than padded with a zero (e.g 201148 rather than 20110408). I could have got round it with some extra string manipulation, but, a little bit of guidance from @theBoardz showed that you can just use the ToString() method on the get-date Cmdlet.

$dt = (get-date).ToString("yyyy" + (Get-date).ToString("mm" + (Get-date).ToString("dd"

UPDATE: can actually do this in a single line! Thanks Alan!

(get-date).ToString("yyyyMMdd")

T-SQL: Get Updated Value

There are many ways to get the value that a column has just been updated to. Often, it'll be being set to an explicit value that you already know, but there are occasions (eg GETDATE() and using Functions) where you might not know for sure what the value has been updated to.

You could use the OUTPUT clause available in SQL2005 and later and for auto-increments you have SCOPE_IDENTITY and friends.

However, here's a neat little trick that I've seen once or twice before and involves setting the variable in the SET clause of your UPDATE statement and looks like this:

CREATE TABLE dbo.Beatles
(IdVal INT, Nm NVARCHAR(55))
GO
INSERT INTO dbo.Beatles SELECT 1, 'John'
INSERT INTO dbo.Beatles SELECT 2, 'Paul'
INSERT INTO dbo.Beatles SELECT 3, 'George'
INSERT INTO dbo.Beatles SELECT 4, 'Ronny'
GO
DECLARE @NewName NVARCHAR(55)

UPDATE dbo.Beatles
SET @NewName = Nm = 'Ringo' -- look at this funky syntax!!
WHERE IdVal = 4

SELECT @NewName
SELECT * FROM dbo.BeatlesWHERE IdVal = 4

Ok, so its not going to set the world on fire, but its something that you may come across so can't hurt to be aware of.

T-SQL: Get first day of the current month

You get a lot of people asking to find out first/last days of months based upon a certain date. Essentially, you can work out nearly anything you want just by getting day 1 of your current month and then using the date functions.

Although this is posted in hundreds of places on the web, for my own sanity as much as anything i'm going to post it here too.

DECLARE @TestDate DATE = '20110417';

SELECT DATEADD(M, DATEDIFF(M, 0, @TestDate), 0) AS NewDate;

-- NewDate
-- 2011-04-01 00:00:00.000