Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Ctrl-alt-geek

Matt Bowler is a DBA at Trade Me, and teaches database design and administration courses at the local institute of technology. A recent but avid discoverer of SQL Server, Matt is a regular contributor to forums at MSDN, Experts Exchange and SSC and he blogs at mattsql.wordpress.com.

Tracking down CPU spikes using process explorer and DMVs

What is it about 4:45 pm on a Friday afternoon that makes CPUs spike, drives crash, databases corrupt? I wish I knew but I don’t. I do know that I have been on the receiving end a few times. Just shutting down the last applications when a call or a… Read more

2 comments, 5,685 reads

Posted in Ctrl-alt-geek on 15 October 2014

The full text error and why I was happy to see it.

The Situation: I was recently involved in the migration of a group of core databases from three SQL Server 2005 instances to three SQL Server 2012 instances. There were quite a few moving parts that needed to be considered as part of the upgrade including replication, CLR, service broker and… Read more

2 comments, 5,035 reads

Posted in Ctrl-alt-geek on 7 October 2014

Migrating SQL Server logins

Once upon a migration I would use sp_help_revlogin to migrate logins and remap the SQL server logins using sp_change_users_login. During some recent migration work I realised that SQL Server Management Studio could do most of the work of sp_help_revlogin. I still needed to use sp_change_users_login – but I came… Read more

8 comments, 2,195 reads

Posted in Ctrl-alt-geek on 7 May 2014

Rolling back transactions with cursors and local variables.

I’ve been doing some work on a credit card payment system lately. Obviously this needs to be robust and consistent so I’ve been working on error handling and “transactionalising” certain parts of the process.

I needed to answer a couple of questions that I had along the way. What happens… Read more

0 comments, 164 reads

Posted in Ctrl-alt-geek on 10 April 2014

Quick Tip: Migrating a SQL Server Mirroring Witness server.

If you’re like me, you might be thinking “I want some more things to think about when I migrate between SQL Servers.” … No? Okay, fair enough. There’s a whole list of things to think about when migrating from one SQL instance to another including:

  1. Logins.
  2. Jobs/Alerts/Operators.
  3. Credentials/Proxies.
  4. Linked Servers.

Read more

1 comments, 206 reads

Posted in Ctrl-alt-geek on 27 March 2014

Quick tip: Querying AD with xp_logininfo

We all have blind spots in our knowledge. We don’t know they are there and when we are thinking through an issue our mind quickly rejects some plans because of these blind spots. This happened to me recently when I was tasked with doing a security audit of a SQL… Read more

1 comments, 184 reads

Posted in Ctrl-alt-geek on 13 March 2014

Issue with sys.dm_db_index_usage_stats in SQL Server 2012

I’d be struggling to remember a week where I hadn’t run a query against the dmv sys.dm_db_index_usage_stats to find unused or under used indexes (or even heaps). It would have to be in the top shelf of many DBA’s toolboxes. So I was alarmed when I came across this post Read more

5 comments, 1,687 reads

Posted in Ctrl-alt-geek on 27 February 2014

SQL Server configuration files

Problem: You require the ability to perform a repeatable standardised installation of multiple instances of SQL Server or the ability to create as-built documentation of an installation.

Solution: I’ve made a few attempts at documenting a SQL Server install, and all the various options that were chosen at each step,… Read more

2 comments, 2,165 reads

Posted in Ctrl-alt-geek on 13 February 2014

Conditional Aggregates

The Problem: A legacy table contains amounts and a char column indicating whether the amount is a credit or a debit. We want an aggregate (over a given range) of the amounts. We need to add the credits and subtract the debits.

The Solution(s): Two possible approaches to the problem… Read more

6 comments, 2,149 reads

Posted in Ctrl-alt-geek on 4 February 2014

My Favourite SSMS productivity tips

It’s a well known fact that any sitcom that runs for a certain length of time will release a clip show. Similarly any blog about SQL Server will end up doing a top tips for using SQL Server Management Studio. Here then, in no particular order, are mine:

1.… Read more

0 comments, 968 reads

Posted in Ctrl-alt-geek on 13 January 2014

Apply SP1 to SQL Server 2012 on active/active Windows Server 2012 cluster.

A while ago I blogged my about my approach to service packing a SQL Server 2008R2 active active cluster. Time to apply SP1 to SQL 2012 on top of a Windows 2012 failover cluster. The basic approach is similar but there are a couple of subtle differences.

The key… Read more

4 comments, 332 reads

Posted in Ctrl-alt-geek on 8 January 2014

Quick tip: Could not load package “xxx” because of error 0xC0014062

I struck this error in one of my SSIS packages, I found lots of tips and suggestions online for the error but none of the solutions worked for me. So I want to add my solution to the mix so that it might give the next person searching an extra… Read more

2 comments, 1,106 reads

Posted in Ctrl-alt-geek on 6 January 2014

Duplicate Statistics

The Setup:

Standard best practise is to have auto create and auto update statistics set for SQL Server databases. But there is no corresponding setting to remove statistics that are no longer necessary. Unless explicitly managed by the DBA these statistics could be causing excess resource usage.

Demo:

Create a… Read more

5 comments, 1,571 reads

Posted in Ctrl-alt-geek on 28 August 2013

Quick tip: WMI alerts and SQL Server Agent permissions.

I came across an issue today while trying to create a WMI alert for a mirroring state change. The error was something along the lines of WMI Access denied. Error: 0×80041003.

My first port of call was to verify the WMI query by using the WBEMTest utility.

This is a… Read more

3 comments, 378 reads

Posted in Ctrl-alt-geek on 15 August 2013

In Memory OLTP with SQL Server 2014

SQL Server 2014 CTP is available for download and evaluation and it contains a couple of exciting performance enhancements. One of these is OLTP databases optimised to be memory resident.

So I wanted to have a play – and I immediately came across a couple of gotchas:

Gotcha 1:… Read more

1 comments, 1,240 reads

Posted in Ctrl-alt-geek on 8 July 2013

Wildcards and Indexes

I was asked a good question the other day, one that I wasn’t sure of the answer. The question was “does SQL Server use an index if you have a wildcard in the search predicate?”

My initial thought was it would if the wildcard was not the first character but… Read more

1 comments, 443 reads

Posted in Ctrl-alt-geek on 22 May 2013

Quick tip: Using sp_server_diagnostics

In SQL Server 2012 a new procedure sp_server_diagnostics returns a payload of information about server health and performance. The sproc is leveraged by the Windows Failover Cluster service and is designed to run in repeat mode. However the sproc can be run in non-repeat mode and loaded into a temp… Read more

3 comments, 2,296 reads

Posted in Ctrl-alt-geek on 3 April 2013

SQL Server 2008 Statistics: What does a DBA need to know?

I’ve been looking into the behaviour of statistics in SQL Server 2008 – so I’d like to present an overview from a DBA perspective.

What are statistics?

Statistics are a database scoped object that are used by the query optimiser in creating query plans. They describe the distribution of values… Read more

0 comments, 4,932 reads

Posted in Ctrl-alt-geek on 14 February 2013

Restore database permissions

Introduction:

I came across a couple of subtle gotchas around the permissions required to restore a database in SQL Server 2008+ recently. I’m interested in situations where a DBA needs to allow certain users the ability to restore a given database, for example refreshing UAT, while maintaining minimum permission levels… Read more

8 comments, 1,539 reads

Posted in Ctrl-alt-geek on 10 January 2013

Migrating SQL Server Databases that use Database Master Keys

There’s a lot of things to consider when migrating databases between SQL Server instances. I want to provide some tips for dealing with Database Master Keys, and in particular the case when those keys are encrypted by the Service Master Key.

Before getting into the details, let’s take a quick… Read more

10 comments, 1,803 reads

Posted in Ctrl-alt-geek on 13 November 2012

Older posts