January 22, 2019
Attaching SQL Databases Without Log Files

If you’ve lost a database log file and you’re in critical need of the data, well… that would never happen ( ͡° ͜ʖ ͡°) Nevertheless, this is might be a handy random fact to know, that you can attach a data (.mdf) file without the associated log (.ldf) file in SQL Server. This post will […]

Read More
January 20, 2019
Backup & Restore (WITH FILE)

In this post I’m going to show what the WITH FILE = X option does when backing-up or restoring a SQL Server database. microsoft_documentation The first thing we need to know is that a backup file can contain multiple databases, and even multiples of the same database. If you run a quick backup command on a […]

Read More
January 13, 2019
Applying Data Retention to a SQL Table

There are many reasons why you’d need to delete old data from a table within a database, some of which include; # GDPR (adhering to data retention policies) # Ever increasing tables becoming tougher to manage (e.g. a replication refresh is taking 2 days) # SQL Server Express limitations (10GB max database size) # The table […]

Read More
October 22, 2018
Using Custom Connection Colours in SSMS

SSMS has an option that allows you to change the colour of your query banner, which is useful if you want visually mark a server that you work with every-day. A while back, when I was frequently switching between live & test SQL instances, I set them to red/green. Red obviously meaning be cautious! Nowadays, […]

Read More
October 9, 2018
Count rows within CSV files using PowerShell

This post is a quick run-through of using a PowerShell script to count rows within a bunch of CSV files. You might find this useful for verifying row counts, if importing multiple CSV files into a database. First, open PowerShell ISE on your machine. Have a quick look at your directory. Now, I’ll count the […]

Read More
October 2, 2018
Default Data Export Settings in SSMS

Exporting data out of SQL is an easy task, but there’s always a few snags that’ll crop up here and there, like always. This is just a quick note on the default export file options when using SQL Server Management Studio. To export a query the simplest way, it would be for us to click […]

Read More
October 1, 2018
Checking Table Sizes in SQL Server

There are many ways to check the size of a table in SQL Server. The way you'll do it will likely depend on what you're task at hand is. For example, a quick look at the Disk Usage by Top Tables report on a database you've never seen before would instantly show you the heavy […]

Read More
September 16, 2018
Backing Up a SQL Server Database

Backing up a database is something I’ve always found easier done within a query window. There’s not much to it; [crayon-5c47fd43ea9bc351367972/] That’s what I’m used to writing. I’ve no need to worry about the copy_only option these days, or check SQL Server Editions to see if I can use compression. Good times that was. Measuring […]

Read More
September 4, 2018
Restoring a SQL Server Database

This is a simple guide to restoring a full backup file in SQL Server, covering a just a few options available during so. As always, have a read through Microsoft’s documentation page before-hand for proper info. 1. Open SSMS and connect to your SQL Server. 2. Right click Databases and select Restore Database. 3. Select […]

Read More
August 30, 2018
Batch Deletions in SQL Server

This script will delete rows within a table that are out-with the specified retention period. This can be useful if you’re running deletes on large tables, and particularly if; #- Deletes are taking a long time & you’re finding it difficult to track progress. #- You have a short maintenance window each day to perform […]

Read More
July 9, 2018
Copying Dates into Excel

There’s often a need to quickly copy/paste a database query output into an Excel sheet, rather than exporting as a .csv file. When you do this for a DATETIME column though, it’ll show up in Excel looking something like this. I don’t think Excel likes the milliseconds. This is the data I tried to copy […]

Read More
July 8, 2018
Stack Exchange Data Explorer

The Stack Exchange Data Explorer is an amazing place where you’ll find all the data that is stored for those Q&A’s they’ve got going. The interesting stuff is down here though 😉 (number 24 in the list) You can view other folk’s queries on each database, as well as create a query of your own… […]

Read More
July 1, 2018
More on SPIDs… and Killing Them

Something I learned today while looking at an old script. The script kills all SPIDs on a database. I had an instant urge to kill system SPIDs just to see what would happen. And the result was… That’s a good thing though right? One less thing to be worrying about. You can’t kill system SPIDs. […]

Read More
June 30, 2018
Change Highlighted Colour of Text in WordPress

This is one from my fix list as the default highlighted text colour on my theme was green. A quick Google search showed me the way. To make the change, I had to add the following to my theme's stylesheet. There are many WordPress plugins that will allow you to change this. Some will also […]

Read More
June 18, 2018
SQL Server Support

What’s the plan when you don’t know how to fix a production issue? You can prepare this situation by reading and keeping up to date. But when you’re sweating behind a keyboard, you haven’t moved out your chair for 4 hours and you’re being chased for an update, what do you do?... I thought I'd […]

Read More
May 21, 2018
Logging sp_whoisactive to a table

This is a follow-on post of sp_who, sp_who2 & sp_whoisactive and is a run-through of logging SQL activity to a table. Even if you are equipped with a SQL Server monitoring tool, you still may be having trouble finding that slow/blocking query that runs during the night. Running sp_whoisactive periodically (e.g. every 30/60 seconds) and […]

Read More
May 21, 2018
sp_who, sp_who2 & sp_whoisactive

Sp_whatnow? sp_who, sp_who2 and sp_whoisactive are stored procedures that allow you to view current users, sessions, and processes within a SQL Server instance. You'd want to see this for identifying things like blocking or checking general activity. sp_whoisactive is definitely one of my favourite stored procedures. Created by Adam Machanic, this tool gives you an […]

Read More