T-SQL formatted duration

I have a database backup that takes more than two days, and I want to look at the duration. But looking at the result of a DATEDIFF the figures are difficult to compare direct.
That took mo to format the duration to something readable. At first I wanted to format to ISO-8601, but that turned out to be too cumbersome in Transact-SQL. Especially getting the leading zeros was a challenge that I usually
Jeff Smith wrote back in 2007 a great article "Working with Time Spans and Durations in SQL Server", but I wanted to avoid the strange date-like part and the multiple columns he worked with.

To combine the different parts into one single-column answer I use part indicators like 'd' for days, 'h' for hours, 'm' for minutes and 's' for seconds. That I think is quite common indicators and should be Immediately recognizable to most people. Also non-IT end users.

Most log solutions give a duration with a lot of seconds, like a SQL Server  database backup. But that can be difficult to comprehend and compare directly

DECLARE @duration INT = 221231;  -- seconds
SELECT CAST(@duration / 86400 AS varchar(3)) + 'd' + CAST((@duration % 86400) / 3600 AS varchar(2)) + 'h' + CAST((@duration % 3600) / 60 AS varchar(2)) + 'm' + CAST(@duration % 60 AS varchar(2)) + 's'

This gives the output

The motivation for this blog entry is database backup history, and the formatting above can be used against the history
 ,backup_set_id, media_set_id, name
 ,backup_start_date, backup_finish_date
 ,CAST(DATEDIFF(SECOND, [backup_start_date], [backup_finish_date]) / 86400 AS varchar(3)) + 'd '
   + CAST((DATEDIFF(SECOND, [backup_start_date], [backup_finish_date]) % 86400) / 3600 AS varchar(2)) + 'h '
   + CAST((DATEDIFF(SECOND, [backup_start_date], [backup_finish_date]) % 3600) / 60 AS varchar(2)) + 'm '
   + CAST(DATEDIFF(SECOND, [backup_start_date], [backup_finish_date]) % 60 AS varchar(2)) + 's' AS [duration_formatted]
 ,(backup_size / 1024 / 1024 / 1024) as [backup_size_gb]
 ,(compressed_backup_size / 1024 / 1024 / 1024) as [compressed_backup_size_gb]
FROM msdb.dbo.backupset
--WHERE [database_name] = 'my_large_database'
ORDER BY [backup_finish_date] DESC

You can comment in or out lines in the statement above to get what you need in the given situation.

Maybe one can use STUFF to insert values in pre-formatted output, but that I could not get to work in first shot. And it did not really add significant value to the output...


PowerShell script template

I have made this simple template to start a new PowerShell script file. It helps me to get the basics right from the beginning. Also it supports my effort to write all scripts with advanced functions.

.PARAMETER <Parameter Name>

Filename  : <Filename>
<Date and time, ISO8601> <Author> <History>

Get-Help about_Comment_Based_Help

#Requires -Version 4
Set-StrictMode -Version Latest

#Import-Module G:\Teknik\Script\Sandbox\Module.sandbox\Module.sandbox.psm1

#region <name>

function Verb-Noun {
  <Description of the function>
  <parameter description>
  <link to external reference or documentation>
  <timestamp> <version>  <initials> <version changes and description>
  [Parameter(Mandatory=$true, ValueFromPipeLine=$true)]

Begin {
  $mywatch = [System.Diagnostics.Stopwatch]::StartNew()
  "{0:s}Z  ::  Verb-Noun( '$param1' )" -f [System.DateTime]::UtcNow | Write-Verbose

Process {

End {
  [string]$Message = "<function> finished with success. Duration = $($mywatch.Elapsed.ToString()). [hh:mm:ss.ddd]"
  "{0:s}Z  $Message" -f [System.DateTime]::UtcNow | Write-Output
}  # Verb-Noun()

#endregion <name>

###  INVOKE  ###

#<function call> -Verbose -Debug

PowerShell script documentation

To PowerShell is defined a syntax for comment-based help in scripts (about_Comment_Based_Help). And there are many more keywords than I have used above, but my personal PowerShell style is covered with these keyword. Feel free to create your own script template with our own personal selection of standard keywords.
Using only standard keywords ensure that your scripts can be presented in a central place with nice and useful documentation. And the presentation can be automatic updated without redundant documentation tasks.
Michael Sorens has on SimpleTalk posted a series of great posts about automatic documentation on a library of PowerShell scripts. The first post is called "How To Document Your PowerShell Library" where the basics are presented in a brilliant way.


2017-03-15 : Blog post created.
2017-05-17 : Section about script documentation added. Inspired by comment from my old friend and colleague Jakob Bindslet.


DBA Backlog

As a Database Administrator both in projects and daily routines like administration or operations all kinds of things show up. Normally they are associations that doesn't really fit in the plans or the context, but still relevant or even important in the long run.
Usually these things and ideas end up on post-it notes, whiteboards or another volatile medium. If you are lucky to be in a large project with experienced developers and project managers some items might be saved.

Another way is to create your own backlog of ideas, points of interest, nice to haves and other things that might make your works better. In ITIL these things will be considered part of Continual Service Improvement (CSI) as input to the Plan part of the Deming Circle.

I have collected a set of attributes I have used to establish a backlog in a project or a team. Not all attributes are relevant in each situation. Sometimes it is also necessary to twist an attribute to make the backlog usable.

And to my proposal for a general backlog:
Attribute NameAttribute Description
TitleShort and descriptive title of the backlog item.
ThemePredefined themes to sort the backlog on a "dimension" like technology. This is very useful when the backlog grows.
{ SQL Server | SSDB | SSIS | ... }
DescriptionProse description of the backlog item with as many details and thoughts as possible.
The description can easy change and grow over time, even before the backlog item is activated and processed.
PriorityThe priority of the backlog item. Predefined values that makes sense, even to senior management.
{ Critical | High | Medium | Low }
AbstractionThis item can be added to the backlog if you are working with defined abstraction layers from idea to solution. This is sometimes seen in agile development methods like SAFe.
{ Epic | Story | Task }
StatusStatus of the backlog item. The values are inspired by Kanban to support an effective execution of the backlog.
"Keep the Ready queue short".
{ Backlog | Ready | Doing | Done }
EstimateA quick estimate of the time needed to process and finish the backlog item.
The item is finish when the issue is solve and documented – completely. As in agile development.
{ Day | Week | Month | Quarter | Year }
DeadlineDate of deadline, if one is required. If a deadline exists it is usually given by business or management.
ChangedTimestamp of last change to the backlog item.
Consider to enable automatic versioning on the backlog.
CreatedTimestamp of when the backlog item was created. Usually when the item was entered into the backlog.
Changed byThe person who last changed the backlog item.
Created byThe person who created the backlog item. Usually the person who entered the item into the backlog.
TangibleThis is more a business attribute that tells about where business can relate to the backlog item.
OwnerBusiness owner of the backlog item.
ResponsibleThe person who is responsible on the backlog item. Usually the person who is to get the job done.

I personally have good experiences on building a backlog as a SharePoint list rather quick and effective. This also gives all the SharePoint features on filtering, printing and other trivialities.
Another possibility I have tried a few times is using the backlog features i Microsoft Team Foundation Server (TFS). It works really nice also for Database Administrators.


2017-04-02 Blog post created.
2017-05-08 Abstraction element added to backlog schema.


SQL Server 2016 SP1

Microsoft has just released Service Pack 1 for SQL Server 2016.
The release is announced in several blog posts, e.g.
There are some rather interesting details on Microsoft database technology in general from the Connect() conference in the blog post "Announcing the Next Generation of Databases and Data Lakes from Microsoft" on SQL Server Blog (Data Platform Insider).
The official descriptions on SQL Server 2016 SP1 editions from Microsoft is more a presentation than technical details.

The installation set on Service Pack 1 can be downloaded from Microsoft Download. The release information (KB3182545) gives the details on what is fixed, but as the information is a collection of links it will take some time to get through all the details.

The new version number after the upgrade is 13.0.4001.0, and the installation will restart the services.

The installation set is named "SQLServer2016SP1-KB3182545-x64-ENU.exe" and takes 551 MiB. If the installation set is unpacked it takes 733 MiB.
The installation can be with the GUI by executing "setup.exe" or by command line - as usual.
Log files are generated as by previous SQL Server installations and upgrades. Usually in a folder named with a timestamp in the path "%ProgramFiles%\Microsoft SQL Server\130\Setup Bootstrap\Log\", e.g. the folder name "20161116_205924".
According to the logfile "Summary_<servername>_<foldername>.txt" (5 KiB) the installation took about four minutes on my workstation. The logfile "Details.txt" (9.14 MiB) contain about 59000 lines in my case. Usually I only look into Details.txt when I have an installation error...

This was the initial - and common - findings. When I get more they will surface here.



For the first time in a long time we have a security update for SQL Server with MS16-136.
Some technical details on the update itself are in KB3199641.

The update fixes elevation of privilege on three different components in SQL Server:

  • Database Engine (RDBMS); there are three CVE's spread over the different major versions of Database Engine. The documentation speaks of "improperly handles pointer casting" without further details.
  • Master Data Services - MDS; There is a cross-site-scripting (XSS) vulnability in the MDS API. This could be in the web application part of the API.
  • Analysis Services - SSAS; in this case the vulnability is due to "improperly checks FILESTREAM path.".
  • SQL Server Agent; the vulnability lies in "incorrectly check ACLs on atxcore.dll". This file is a part of the SQL Server Agent ActiveX subsystem, which is - finally - removed from SQL Server with the 2016 version.

The update is for SQL Server 2012 and newer. SQL Server 2008 (R2) are not hit by this security issue.
And not a word about SQL Server 2005, 2000 - or older ;-)


2016-11-09 Post created with initial references.
2016-11-27 Details on SQL Server components added.