Detailed SQL Server Partition Script

I’ve been working with data warehouses again recently and needed to help out the rest of the team who were not familiar with partitioning in SQL Server. It’s one of those subjects that really requires hands on experience with to truly appreciate the mechanics of partitioning, and for me it especially helps if I can visualize a concept.

I’ve always thought that the best way to explain partitioning as imagining that any one table in SQL Server has a partition count of exactly one. When we actually partition the table on a range value on a column then we introduce multiple copies of that table, and that it is the meta data that is replicated for the table. And it is from this copying of the table behind the scenes that helps with archiving and query performance. This is probably not far off from how partitioning actually works. Continue reading

The Subtleties of COPY_ONLY Backups

Recently I have been looking at the COPY_ONLY feature for taking backups of a database. COPY_ONLY was introduced in SQL Server 2005, and allows you to take ad-hoc backups of either the log or a full backup without breaking the backup chain for differential backups. The COPY_ONLY backup is not part of the restore log: so when restoring from a full backup you can ignore it. The same here can also be said for log COPY_ONLY backups; it’s an ad-hoc backup that does not alter the transaction log at all; there’s no clearing down of the log. Continue reading

The 5 Do’s and 10,000 Don’ts of CV Writing

Disclaimer: there isn’t really 10,000 don’ts, in fact there is only one. But it is pretty darn important. I’ve written and read enough CV’s to have a pretty good idea what constitutes a good CV and what constitutes good bin liner and so I thought I’d share my thoughts. Let’s start with the Do’s:


  1. Keep It Pithy: I’ve had to read many CV’s in my time, and nothing is more off putting than a CV that is longer than Milton’s Paradise Lost. Remember, the hiring manager will have a pile of CV’s and perhaps will dedicate all of 5 minutes in deciding who makes the initial shortlist and whose to bin, so help them make a quick decision by keeping your CV short. The optimal length of a CV should be no more than a page, or 2 pages if you have to. But no one is going to care that 20 years ago you worked on a Saturday washing cars. You may have a great job history, but try not to go back too far. And the older the job, the less detail you need to put in. This still gives you a chance to shows something you’re really proud of in an old role without going into too much detail.
  2. Keep it Pertinent: You need to keep in mind that no 2 jobs are the same yet chances are you will apply for these different roles with the same CV. So you need to focus on the profession you’re applying for. If you’re applying for a job that is a DBA, keep that as your focus as you write it. The hiring manager is not going to care if your hobbies are going out with your friends and taking long walks on the beach. Cutting out the fraff like this will also help keep it short. You don’t want your CV to be the next Voynich Manuscript .
  3. Keep it Presentable: So you’ve got to keep it pithy and pertinent, yet you also need to keep it clear. So this is where most people who do keep it short and pertinent may fail in trying to cram everything in. Consider your font type: using something like Calibri over Arial or Verdana. By all means keep the margins narrow, but don’t over do it. Use bullet points as opposed to full sentences. Brevity is the key. If you want to draw the managers attention to something, highlight a word or two in bold. And use spell and grammar check! Despite technologies best efforts, I write with many typos, because I am lazy when it comes to typing. Rather embarrassingly, as a DBA, I still misspell “FROM as “FORM”, yet spell check will not pick this up. So get someone to proof read it. This is different from critiquing your CV: just make sure the sentences make sense and that there’s no daft typos.
  4. Show The Value: the benefits of certain technologies that you know about may not immediately present their benefit to the hiring manager, so you need to demonstrate that you understand the business value of your expertise. Listing technologies shows nothing other than you know how to write a list of technologies. You know how to write PowerShell modules… great. Who cares? You wrote a series of PowerShell modules that transferred backup files over a dodgy FTP connection and would retry any failed transfers so that no one had to log in over the weekend and check the progress of transfers? OK, that sounds much better.
  5. Plan and Prepare: there’s a military adage called the 7 P’s . Mild expletive aside, it’s important to take the time to write your CV and give it the attention it deserves. Your CV is the first impression that hiring managers have of you. If you just bash out some bullet points and don’t put the effort into applying the four points above, your putting out the impression that you aren’t really that serious about looking for a new job, so why should the hiring manager be serious about calling you in for an interview?

Continue reading

Get Run Duration of a SQL Agent Job

The MSDB system database holds all the information for any SQL Agent jobs set up on the server. But it is not straightforward to query the system tables to get information like the duration of jobs out of the tables in the correct format. With that in mind, here’s a little script that will return the total duration of the SQL Agent jobs. There are a couple of “WHERE” statements you can include or exclude if you want to include a specific job, or if you want to include all the steps of the jobs.

SELECT job_name AS JobName
	,run_datetime AS DateOfRun
	,run_duration AS RunDuration
	SELECT job_name
		,SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' + SUBSTRING(run_duration, 5, 2) AS run_duration
			,run_datetime = CONVERT(DATETIME, RTRIM(run_date)) + (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4
			,run_duration = RIGHT('000000' + CONVERT(VARCHAR(6), run_duration), 6)
		FROM msdb..sysjobhistory h
		INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
		WHERE step_id = 0-- comment this out to get break down of each step
		) t
	) t
--where job_name = 'add job name here'
ORDER BY job_name
	,run_datetime DESC

Get Last Backup Date of Database

I’ve been working hard on my backup/restore skills lately, getting to grips with the fundamentals and making sure that I understand backup/restore best practices and running examples to make sure I’m ready in the event of a disaster. It’s a cliche to say that you don’t want to be doing this in a real life disaster when everyone is huddled around your desk, but it’s true! Tasks like tail log backups, a type of backup that backs up the log file even if it is offline or damaged, is something that you may need to run in some scenarios. A post by Paul Randal goes into depth about this. Knowing how you can run a tail of the log backup and when its possible, and even how to attach the log to a different server are crucial in disaster recovery scenarios, so it’s well worth reading and running through the demos.

Not understanding backup/restore processes can have greater repercussions applies if you have log shipping running; although full backups do not break the backup chain, log backups that are not applied to the standby server means that you can lose a database that the business rely on to run reports on through the day. If you do need to run a log backup and you do not want to break the log chain, use the COPY_ONLY option when backing up the log.

Some time ago, I wrote a detailed script that returns the database backup history using the system tables, but sometimes all you want ot know is how to cut to the chase. So I have stripped the script back (more than a little) to return the latest full backup of all databases on the instance.

--get last backup of a database
MAX (bck.backup_finish_date) as FinishTime
FROM MSDB.SYS.databases db
LEFT OUTER JOIN msdb.dbo.backupset bck ON bck.database_name =
group by

Using the left outer join to join from the sys.databases table to the sys.backupset table returns all databases that have no backups, which in most cases is far more important than the last backup time of your databases!

Get Database Restore Progress

A couple of weeks back I posted a script that checks the progress of any backups running on and instance. A few days later I needed a script to check the progress of a restore. Fortunately enough, the backup script can be re-used with a very simple change. This simple script to check if any restores are running, how long they have been running and what the anticipated complete time will be.

Happy scripting!

    ,B.TOTAL_ELAPSED_TIME / 60000 AS [Running Time]
    ,B.ESTIMATED_COMPLETION_TIME / 60000 AS [Remaining]
        FROM sys.dm_exec_sql_text(B.SQL_HANDLE)
        ) AS COMMAND
    ,sys.dm_exec_requests B
ORDER BY percent_complete DESC

SQL Server 2016 CTP 2.2 Build Has been Replaced

I received an email from the TechNet evaluation Centre this morning, stating that the CTP for SQL Server 2016 (v2.2) has been replaced as of yesterday. Apparently the previous release caused downtime with AlwaysOn Availability Groups during rolling upgrades. Though I have SQL Server 2016 installed on an Azure box, I’ve not played around with AlwaysOn in 2016. An upgrade is recommended for any users who wish to upgrade to SQL Server 2016 CTP 2.2, including AlwaysOn users. If you have already downloaded the English parts from 28th July onwards, you already have the latest build. The previous release for CTP 2.2 was 23rd of July.

Download 2016 Now