SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Undercover

David Fowler and Adrian Buckman, two database nerds who love nothing more than to spend their time, reading about, researching and sharing all things SQL Server. David is a DBA with over 15 years production experience of SQL Server, from version 6.5 through to 2016. He has worked in a number of different settings and is currently the technical lead at one of the largest software companies in the UK. After working in the motor trade for over 11 years Adrian decided to give it all up to persue a dream of working in I.T. Adrian has over 3 years of experience working with SQL server and loves all things SQL, Adrian currently works as a Database Administrator for one of the UK’s Largest Software Companies.

Selecting a Random Row From a Table

A couple of times recently I’ve seen the question asked, ‘How can I select a single row at random from a table?’.

There are often a few ways of doing this suggested, most seem to rely using CTEs or temp tables.  I thought I’d share, in a quick post a… Read more

1 comments, 130 reads

Posted in SQL Undercover on 20 July 2018

T-SQL Tuesday #104 – My Undercover Toolbox favourites

This months T-SQL Tuesday is being hosted by Bert Wagner , you can find the invitation for this months topic here.

Bert has a great topic for this month, he wants us to write about code you’ve written that you would hate to live without.

I have found the SQL… Read more

2 comments, 2,229 reads

Posted in SQL Undercover on 10 July 2018

Backup Your On Premise SQL Server Directly to an AWS S3 Bucket

I’ve been having a little play around with AWS recently and was looking at S3 (AWS’ cloud storage) when I thought to myself, I wonder if it’s possible to backup up an on premise SQL Server database directly to S3?

When we want to backup directly to Azure, we can… Read more

0 comments, 231 reads

Posted in SQL Undercover on 18 June 2018

Resend an email sent from SQL Server using TSQL

Sometimes emails from SQL Server go missing, especially when you share an inbox with colleagues. On most occasions it doesn’t always matter as the job that generated the email can simply be re ran to produce the email once again, but what about those emails that contain time specific information?… Read more

3 comments, 1,878 reads

Posted in SQL Undercover on 5 June 2018

Convert a Positive Number To a Negative In SQL With a Spot of Bitwise Logic

Now this is something that I’ve seen asked a few times and it’s always a question that gets a fair number of different answers.  How do we go about converting a positive number to a negative, or the other way around?

I’ll give you my solution and it uses a… Read more

3 comments, 616 reads

Posted in SQL Undercover on 24 May 2018

Digitally Signing a Stored Procedure To Allow It To Run With Elevated Permissions

Have you ever had the need to give elevated permissions via a stored procedure above that what the user calling the procedure might have?

Perhaps you’ve got a table in your database that contains top secret information and you only want that information to be accessed by via a proc,… Read more

1 comments, 2,109 reads

Posted in SQL Undercover on 2 May 2018

T-SQL Tuesday #101 – My day of Essential SQL Server tools.

This month’s T-SQL Tuesday is brought to you by Jens Bestergaard – the subject is Essential SQL Server tools, the original invitation can be found here.


Here is how my day of using SQL tools went:

It all started at 00:00 this morning with planned maintenance, I was… Read more

0 comments, 957 reads

Posted in SQL Undercover on 10 April 2018

Encrypting SQL Server Database Backups

We all go to great lengths to make sure that our databases are secure (or at least I really hope that we do), we make sure that only authorised users have access and then only to the databases and levels that they need.  We’re careful that all our passwords conform… Read more

2 comments, 2,134 reads

Posted in SQL Undercover on 4 April 2018

‘Object Reference Not Set to an Instance of an Object’ When Failing Over an Availability Group Using SSMS <= 17.5

Here’s a quickie that I’ve just stumbled across.

I’ve just tried to initiate a manual failover of an availability group and received the following error.

After a spot of head scratching and thinking that there was something wrong with my AG setup, it turns out that there’s a bug in… Read more

0 comments, 235 reads

Posted in SQL Undercover on 29 March 2018

TempDB Filling Up On Secondary Replicas

Have you ever had an issue where TempDB was filling up on your secondary replicas?  Do those secondaries happen to be read only replicas?

When I have an issue with tempdb filling up the first thing that I usually do is try to figure out exactly what the space has… Read more

2 comments, 3,112 reads

Posted in SQL Undercover on 21 March 2018

TSQL Tuesday #100: A Look To The Future

So for this month’s TSQL Tuesday, Adam Mechanic has asked us to take a look into the future and think about what things might look like when TSQL Tuesday #200 comes around (apparently that’ll be June 2026).  So I thought I’d have a little bit of fun with this… Read more

1 comments, 314 reads

Posted in SQL Undercover on 13 March 2018

What if it all grows out?!

The other day I got thinking about what would happen if all databases on a single instance grew out, every single one of them! but not just once, what if they all grew out three, four or fives times overnight – what would things look like?

Well I know the… Read more

1 comments, 166 reads

Posted in SQL Undercover on 12 March 2018


Here’s a quick one for you.  If you happen to be managing SQL Servers with a large number of databases and availability groups, it can sometimes be difficult to keep track of which database belongs to which availability group.

sp_WhatsMyAG will tell you just that.  You can either provide it… Read more

1 comments, 224 reads

Posted in SQL Undercover on 7 March 2018

Log Shipping – Error: Failed to update database “DATABASE NAME” because the database is read-only

Here’s a strange one that I’ve recently come across.  I had a customer report that their log shipping restore jobs were chock a block of errors.  Now, the logs seem to have been restoring just fine but before every restore attempt, the job is reporting the error,

Error: Failed to… Read more

1 comments, 338 reads

Posted in SQL Undercover on 22 February 2018

Getting started with the SQLUndercover Inspector

Here is a quick guide to getting started with the SQLUndercover Inspector.


  • SQL 2012 or higher
  • SQL Server Agent
  • A database to store the the data collected by the agent jobs (this can be one you use for your other DBA needs it doesn’t have to be a dedicated…

Read more

2 comments, 216 reads

Posted in SQL Undercover on 19 February 2018

T-SQL Tuesday #99 : Getting Decked Out in Rubber and Lycra, My Non-techy Passion #TSQL2sday

So this month’s T-SQL Tuesday invitation comes from Aaron Bertrand.  It’s a non-technical post this month as he asks us to write about a non-SQL or tech related passion.

For me, my passion is Triathlon.  I first got hooked on the sport back in around 1998 when I got into… Read more

2 comments, 694 reads

Posted in SQL Undercover on 13 February 2018

The SQLUndercover Inspector is now available!

The SQLUndercover Inspector is now available on GitHub, a quick run down of what it does can be found here: Introducing: The SQLUndercover Inspector, a quick video introduction can be found here and upcoming video tutorials will be released on Undercover TV in due course.

Thanks for reading… Read more

0 comments, 895 reads

Posted in SQL Undercover on 11 February 2018

sp_AGReconfigure 1.1 is now available

sp_AGReconfigure 1.1 is now available HERE and on GitHub

V1.1 includes a new Parameter and some minor bug fixes:


NULL – DEFAULT:  Ignore Readable secondary check (acts as though you are using V1)

0 – Produce a statement to switch readable secondary off if switched on

1 – Produce… Read more

1 comments, 284 reads

Posted in SQL Undercover on 8 February 2018

UNDERCOVER TOOLBOX: Get Details of All Open Transactions

It was 3am in the morning and I was asleep and enjoying a delightful dream (I knew it was a dream because I was surrounded by drifting clouds, singing angels and hundreds of softly humming SQL Servers where the hardware had been sensibly provisioned and all code carefully optimised) when… Read more

6 comments, 337 reads

Posted in SQL Undercover on 7 February 2018

Undercover TV – We’ve got a YouTube channel!

We’ve now got ourselves a shiny new YouTube channel at UndercoverTV.

We’ll be using this to host our monthly podcasts as well as the occasional tutorial video and anything else that we might decide to throw together, so pop over there, subscribe and watch out for new content as it… Read more

0 comments, 294 reads

Posted in SQL Undercover on 1 February 2018

Older posts