Welcome to my technical blog.

I want to collect here the first-hand experiences, tips, and best-practices that I use frequently. I hope you find something here of interest to you :-).

There is no ‘standard’ to my posts. Some are blindingly obvious – where I just had to note something down that tripped me up. Other posts (hopefully) will be deeper.

Mostly I write in the first-person “I did this and it worked”, rather than “if you do this it may work if your environment it similar to mine”. In the hope that you can adapt my succinct notes (that ACTUALLY worked), to your situation.


TSQL Performance Rule #1

There’s no significance to this one being number one 🙂 its just the one I’ve just been thinking about 🙂 I may now have built this up a bit more than is warranted, so hope your not expecting too much from my number one performance rule. Oh, Ok then, here goes …

“All numbers in stored-procedures should be in single quotes”.

Even if they are defined as INT they could potentially force a VARCHAR to be converted to INT.

Consider WHERE SomeColumn = 42. Conversion presidency means VARCHAR’s will always be converted to INT’s never the other way around. The one numeric value above (42) could cause a million rows in the column (“SomeColumn”) to have to be converted to INT to be tested. Significantly affecting performance.

Consider WHERE SomeColumn = ’42’. “SomeColumn” is either numeric of non-numeric. If its INT for example then just one value (the ’42’) has to be converted to INT (taking no time at all). If “SomeColumn” is VARCHAR then there is no conversion.

Shrink TempDB

To shrink the TempDB MDF file (before adding additional NDF files for example), whilst retaining the total size of 50GB …

USE [tempdb]
DBCC SHRINKFILE (N'tempdev' , 8192)

Exporting a Report to Excel

Finance wanted to export their reports into spread sheets but the company Logo and report Title were messing up the rendering.

To fix this I amended the SQL Server 2012 SSRS config file (called “rsreportserver.config“) after taking a copy.

The location of the config file was …

C:\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer

I commented out the line … (please note: I have replaced the greater-than and less-than symbols with square brackets)

[Extension Name="EXCELOPENXML" Type="Microsoft.ReportingServices.Rendering.ExcelOpenXmlRenderer.ExcelOpenXmlRenderer,Microsoft.ReportingServices.ExcelRendering"/]

… and replaced it with these 7 lines …

[Extension Name="EXCELOPENXML" Type="Microsoft.ReportingServices.Rendering.ExcelOpenXmlRenderer.ExcelOpenXmlRenderer,Microsoft.ReportingServices.ExcelRendering"]

To use this, I moved the report Logo and Title into a heading-block within the reports.

Add a Column to a Report Model

I had the odd request to add an extra column to a SQL 2008r2 “Report Model”. I had never heard of one of those, but it turned-out to be a type of amalgamated data-source that the users created there own ad-hock reports from (via Report Builder 1).

To add the extra column I just added it to the SQL View (which was named in the amalgamated data-source definition). Then to refresh the Report Model I downloaded it and uploaded it with a new name.

Report-Builder Cube Data-Source

Had a tricky situation connecting Report Builder 3 to a cube. I was able to copy the connection string from withing SSDT but it still would not work.

I will use “Adventure Works” for illustration.

The solution was in the error message “Either the user, does not have access to the AdventureWorksDW2012 database, or the database does not exist.”

It turned out the database did not exist … as its SSMS Database-Engine name (“AdventureWorksDW2012”).

Connecting SSMS to Analysis Services however showed a different name “AdventureWorksDW2012Multidimensional-EE”

Plugging this into my connection string (with Data Source Type being Analysis services, and Connect Using being Windows Integrated Security) worked eg:-

Data Source=(ServerName\InstanceName);
Integrated Security=SSPI;
Initial Catalog=AdventureWorksDW2012Multidimensional-EE

Annoyingly (grrr!), I found just removing the Initial Catalog worked also (ah bah).

Update Statistics on a whole database

Whilst performance tuning an SSRS report server I wanted to update all the statistics within the two databases ‘ReportServer’ and ‘ReportserverTempDB’.

I chose a simply-coded, two step method (for safety and to keep control).

First I generated the commands (per database) …

Select 'UPDATE STATISTICS [' + [name] + '] WITH FULLSCAN' 
from reportserver.sys.tables
Select 'UPDATE STATISTICS [' + [name] + '] WITH FULLSCAN' 
from reportservertempdb.sys.tables

… before executing them in a separate session.

SQL Refactor steps

I inherited a big horrible stored procedure (sp) created by unioning two other sp’s together to make a big mess.

This sp was feeding a report, and although it was returning the correct data, was taking a significant time to do so.

These are the steps I took to tame it …

1. Change the sp code into a query
2. Save a copy of the original, because you never know 🙂
3. Save the code you are working on (regularly).
3. Run it. Save the output and record the number of rows returned.
4. Expand all views, and views of views, etc
4b. run it after each change – be pedantic – then save.
5. Remove all commented-out lines of code
6. Comment-out any select items (in the outer select) not needed by the report.
7. Working down from the top, comment-out any Select items in sub queries not used by the parent.
8. Save all sub-queries into temp-tables.
9. Tune the slowest sub-query
10. wip

Only emailing a spreadsheet when it contains data.

This was a new one to me! A user subscribed to an SSRS Report but only wanted to receive the email if the report contained data.

There seems to be loads of write-ups on the web about how to set this up. Here is how it went for me …

Firstly, I created the stored-procedure that would return rows from a live table that contained (business logic) errors (EG: “rep_exceptions”).

I created the report and subscribed to it (making sure to paste in my actual email address, not my login).

In the subscription form I specified both a start-date and an end-date that were in the past (ensuring the subscription would never actually fire, and ‘OK’ed the form.

Within SSMS “Job Activity Monitor” I located the job that had been created by this subscription (I looked for jobs that had never run, and then matched the start / end dates in the schedule with those I used in the subscription-form)

I copied the action statement from the job-step eg:

EXECUTE [ReportServer].dbo.AddEvent @EventType='TimedSubscription', @EventData='5ec60080-b30c-4cfc-8571-19c4ba59aaac'

… into a new job. Then augmented it, to only run if there was data …

EXECUTE [ServerName].[dbo].[rep_exceptions]

if @@ROWCOUNT > 0

EXECUTE [ReportServer].dbo.AddEvent @EventType='TimedSubscription', @EventData='5ec60080-b30c-4cfc-8571-19c4ba59aaac'

I scheduled the new-job to run every 8am.

Once this was all tested and working – I changed the SSRS subscription email address to the user (and left me as CC temporarily).

Adding ‘All’ to a report drop-down list

There is a problem when you configure a report parameter to ‘Accept Multiply Items’, in that it won’t work with a stored procedure (SP).

One work-around is to only Accept Single Items, but make one of them ‘All’, like this …

1) Create a separate SP to populate the parameter and add an ‘All’ option …

SELECT 'All' CustId
FROM SomeTable

2) Then amend the main SP by joining to SomeTable, and adding a CASE statement in the WHERE clause, like so …

WHERE SomeTable.CustomerId = CASE WHEN @CustId != 'All' THEN @CustId ELSE SomeTable.CustomerId END

Which translates as …

WHERE SomeTable.CustomerId = SomeTable.CustomerId

… when ‘All’ is selected (which will let everything through), or …

WHERE SomeTable.CustomerId = @CustId

… where ‘All’ is Not selected.

This allows the user to select All or a single value.

However, if you need to select more than one value but not all values – you will need another approach. You will need to use a split-string function.

Powershell: sort by a column name that contains a space

By trial and error I found the answer is to just remove the space. For example to sort by the column “Recovery Model” …

import-module SQLPS; cls
$smoserver = new-object
$smoserver.databases | sort-object RecoveryModel

The secret of Performance Tuning

Sure you can make a list of rules, or follow someone else’s list. But that’s just the junior stuff. The stuff that will be automated soon.

The secret of proficient performance tuning is just one word. And you wont like it.

Knowing this word wont instantly help you. You studied technology in the first place to get away from this messy word. But here it is again. Creativity. Sorry.

Memorising those rules is like learning the alphabet. No one will pay you for that.

MDX Commandments

1) Always use fully qualified names.

selecting from ‘2016’ may work initially as the engine returns the first object it finds with this name (which just so happens to be in the calendar dimension).

However after a time there may be a customer-number ‘2016’ (in the customer dimension) that will be returned erroneously.

2) A Tuple marks the co-ordinates of the data to be returned.

A list of comma separated dimensions that intersect on the required data. EG:
(customer 123, date 10/07/2016, sales-item 456).

3) A Set is a list of related objects

EG:{date 10/07/2016, date 11/07/2016, date 12/07/2016}. A Set can be a list of Tuples.

Re run Subscriptions

When an email server failed overnight I used this script to generate the commands to re-send reports to subscribers …

-- RerunFailedSubscriptions.sql

-- generate the commands to resend report-emails that failed for some reason

select s.LastStatus, LastRunTime, 'exec sp_start_job @job_name = ''' + cast( as varchar(40)) + '''' [Command To Re-run the Job]
from msdb.dbo.sysjobs j  
join  msdb.dbo.sysjobsteps js on js.job_id = j.job_id 
join  [ReportServer$REPORTS].[dbo].[Subscriptions] s  on js.command like '%' + cast(s.subscriptionid as varchar(40)) + '%' 
where s.LastStatus like 'Failure sending mail%';

Using “Select All” into a dynamic query

After changing a report Select statement into dynamic-sql I found “Select All” no longer worked for my “Customers” parameter.

To fix this, in Dataset/Properties/Parameters I changed the Parameter Value to


To translate … after the “.Value” there is … comma, double-quote, single-quote, comma, single-quote, double-quote, close-bracket. So that in the query each value would be surrounded by its own single quotes
simples 🙂

A Report that uses its location

A customer had many copies of the same report – one for each site. And the application database had multiple copies of each table – one for each site. The table names were all appended with the sites name EG: “WP London_SalesHeader”, “WP Barcelona_SalesHeader” etc.

In Report Manager there was a folder for each site, containing sub-folders for different categories of report (EG: /London/Sales, OR /London/Production).

This is a simplified account of how I created a Report that returned only information specific to its location.

In Report Builder I created a hidden parameter called @site of type Text with no “Available Values” and its “Default Values” using the global variable ReportFolder.

As the output from this built-in variable would be like “\Paris\Sales” I had to create an expression for the “Default Value” of @site searching through each site name in turn …

=IIf(Globals!ReportFolder.Contains("Barcelona"),"WP Barcelona",
IIf(Globals!ReportFolder.Contains("Paris"),"WP Paris", "WP London"))

Finally, in the report query I derived the table name using the @site parameter.

declare @cmd varchar(max) = 
'select	[SalesCode],
from	[Production].[dbo].[' + @site + '_SalesHeader]'


(NB: As a best-practice I displayed the value of @site, along with the other parameter choices, in the report sub-title.)

Report Server log – days kept

I viewed our Report Configurations by running this against the Report Server database

Select * from dbo.ConfigurationInfo;

I then changed the number of days the log retained from the default 60 like this …

Update ConfigurationInfo SET Value='366' Where NAME='ExecutionLogDaysKept';

Gauge auto scale

The gauge’s in SSRS do not have an ‘auto’ option like the charts do.  I wanted the ‘min-scale-vale’ on the 500 boundary below the lowest value and the ‘max-scale-value’ that was on the 500 boundary above the maximum value.

For example …

ytd-Balance / ytd-Budget / min-scale / max-scale

865 / 1022 / 500 / 1500

2690 / 325 / 0 / 3000

5346 / 7453 / 5000 / 7500

… here’s the expressions …

=iif(Fields!bal.Value<Fields!bud.Value, Int(Round(Fields!bal.Value/1000/1000,2)*2)*500,
iif(Fields!bal.Value>Fields!bud.Value, Int(Round(Fields!bud.Value/1000/1000,2)*2)*500,nothing))
=iif(Fields!bal.Value<Fields!bud.Value, Int(Round(Fields!bud.Value/1000/1000,2)*2+1)*500,

Recover deleted chart axis

I deleted my Chart date/time axis for a cleaner look – but then changed my mind .


Within Report Builder 3 I recovered it by

  1. making the ‘Properties’ pane visible …


2. Clicking on the chart, then in the Properties pane navigating to Chart, Chart Areas …


3. I clicked on the ellipses (“…”) which brought up a chart-area properties box. Again I clicked the ellipses (next to Axis, Category Axis) …


4. Then changed the Visibility to ‘True’ …




Unstructured Data

How useful is unstructured data really?

In a train station there may be a timetable, an arrivals board, leaves on the line, a chalk board saying you can get a cake half price with a coffee, tannoy announcements apologizing for a delay caused by an earlier signaling error, and a memorable picture from a new movie. Discuss …

Collecting Wait Stats

Based on the work of GS, here is my script to create a Job that collects wait stats every 15 minutes.


USE [msdb]

IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'DBA_CollectWaitStats')
EXEC msdb.dbo.sp_delete_job @job_name=N'DBA_CollectWaitStats', @delete_unused_schedule=1

USE [msdb]

SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR  0 OR @ReturnCode  0) GOTO QuitWithRollback

	@today varchar(50) = (select convert(varchar, getdate(), 112)),
	@nextweek varchar(50) = (select convert(varchar, getdate()+8, 112)),
	@dbname varchar(50) = 'master' --<<<<<>>>>>>>>

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DBA_CollectWaitStats', 
		@description=N'Collects wait stats for performance tuning.', 
		@category_name=N'Database Maintenance', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR  0 OR @ReturnCode  0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Create the table', 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'create table [dbo].[WaitStats] 
	WaitType nvarchar(60) not null,
	NumberOfWaits bigint not null,
	SignalWaitTime bigint not null,
	ResourceWaitTime bigint not null,
	SampleTime datetime not null
IF (@@ERROR  0 OR @ReturnCode  0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Collect current waits', 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'INSERT INTO [dbo].[WaitStats]
SELECT  wait_type as WaitType,
        waiting_tasks_count AS NumberOfWaits,
        signal_wait_time_ms AS SignalWaitTime,
        wait_time_ms - signal_wait_time_ms AS ResourceWaitTime,
        GETDATE() AS SampleTime
FROM    sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
AND	[waiting_tasks_count] > 0

IF (@@ERROR  0 OR @ReturnCode  0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR  0 OR @ReturnCode  0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every 15 mins for a week', 
IF (@@ERROR  0 OR @ReturnCode  0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR  0 OR @ReturnCode  0) GOTO QuitWithRollback
GOTO EndSave


My Hekaton template


-- 1) add filegroup (if not already there)

USE [master]
begin try
end try begin catch end catch

-- 2) add filestream file into filegroup (unless already done)

USE [master]
begin try
ALTER DATABASE [DemoDW] ADD FILE ( NAME = N'DemoDB_hek', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\DemoDB_hek' ) TO FILEGROUP [MemoryOptimizedFG]
end try begin catch end catch

-- 3) remove table if it already exists

USE [DemoDW]
begin try
drop table recentsales
end try begin catch end catch

-- 4) create memory optimized table (hekaton!)

USE [DemoDW]
create table recentsales
[Name] varchar NOT NULL,
Price decimal NOT NULL


PostgreSQL Terms

A Cluster is a single, complete, running, PostgreSQL server (IE: cluster of databases)

  • One PostgreSQL Server
  • Listening on one port (may be multiple addresses)
  • One set of data files (including tablespaces)
  • One set of Write Ahead Log

Operations done on a Cluster:

  • Initialization (initdb)
  • Start / Stop the cluster
  • File-level Backup / Restores
  • Streaming Replication

Objects defined at Cluster level

  • Users / Roles
  • Tablespaces
  • Databases

PostgreSQL – Administration

Maintenance Tasks

  • Keep autovacuum enabled most of the time
  • VACUUM regularly as well
  • Check for unused indexes

Warnings: (unless you know what you are doing …)

  • Avoid using VACUUM FULL
  • REINDEX CONCURRENT does not exist (yet)
  • Do not use HASH INDEXES
  • Do not use fsync = off


  • PostgreSQL docs are about 2000 pages
  • Technically accurate
  • Written and maintained by the developers


  • Superuser is too powerful for most use cases (SECURITY DEFINER functions)
  • Use a distinct userid for replication
  • GRANT minimal access rights


  • Maintenance releases happen about every 3 months
  • For best security – upgrade to latest maintenance release
  • Major release upgrades are harder
  • UDR technology (thats UniDirectional Replication) will make Major release upgrades much easier from 9.4+


  • PostgreSQL is designed to be extensible
  • Many new features enabled via extensions (EG: pgaudit, postgis)
  • Use them!


  • GUIs do not allow you to apply changes in a transaction or easily record your actions
  • Use scripts for any administrative changes
  • Test them, before applying

Schema Change: adding a foreign key now split into two parts :-

  1. Apply constraint going forward (with quick write-lock)
  2. Check data already in the table (a background task)


PostgreSQL study notes – psql


  1. non-interactive usage
  2. command history (up/down arrow)
  3. tab completion (sans Windows)
  4. commands terminate with semi-colon and can wrap lines
  5. defaults to supplying currently logged-in username as pg use


  1. explore ‘psql’
    a. ‘psql –version’ returns version of postgresql client
    b. ‘psql -l -U postgres’ lists installed db’s then exits.
    postgreSQL installs 3 default db’s
    1. ‘postgres’ – management db. contains user accounts, global settings, etc
    2. ‘template0’ – vanilla read-only db
    3. ‘template1’ – changable copy of template0, used as template for new db’s
    c. ‘psql’ with no options enters interactive mode (duh)
    a hash-mark ending the interactive-prompt denotes a ‘superuser’ eg: ‘postgres=#’
    d. ‘\h’ – returns sql-specific help eg: ‘ALTER TABLE …’
    ‘\h create’ filters above to just ‘CREATE …’ commands
    e. ‘\?’ – returns ‘psql’ specific help eg: ‘\?’ ie: usable metasequences ie shortcuts
    f. ‘\l’ – returns list of DBs. ‘\l+’ additionally returns DB sizes
    g. ‘\du[+]’ – returns list of users with access to postgresql.
    h. ‘\!’ open shell from session (‘exit’ from shell = back to psql)
    i. ‘\! [command] – runs command in shell non-interactively and returns to psql.
    j. ‘\i filename’ – execute command(s) in the file ie psql or sql commands
    k, multiple commands can be run on one line. Separate with space, terminate with semi-colon (eg: ‘\l \du;’)
    L: ‘\c’ – connect to another database or host eg: ‘\c template1’
    m. ‘\d’ list tables\views etc in current DB, ‘\dS’ list system tables, ‘\dS+’ list system tables with sizes.
    n, \q quit
  2. c. ‘psql –help’ (or ‘psql -?’) psql option switches & defaults (short version then long version) eg: -U (username – short version), -l (list – short version), –version (long version) …


PostgreSQL study notes – Installation

Download from Enterprise DB, gui is the one to go for, remember xhost on linux

1. Install. For prod you should indicate that data files are stored independently of the source tree. A ‘Cluster’ is not a classic Cluster IE: a Server Cluster, just means all the databases on this particular box.

2. Explore the footprint of the install. ‘\\bin\ contains utilities like psql.exe (terminal monitor). \\data\  contains all databases and 3x config files & pg_log/ (log files), pg_xlog/ (write ahead log folder). postmaster.opts (startup options)

3. Provide access to internal docs via web-browser bookmark eg: file:///C:/Program%20Files/PostgreSQL/9.5/doc/postgresql/html/index.html

4. Add \\bin file to path – for psql.exe etc (eg: C:\Program Files\PostgreSQL\9.5\bin). posqlgresql clients default to submitting the current logged-in users name as the DB user name (eg: “psql” without -U will assum user is windows-user).

5. Add system variable ‘PGUSER=postgres’ workaround so psql etc wont try to login to utilities as o/s-user


More thoughts against Triggers

For Rob & Karl – Triggers run outside of transactions. An insert that fires a trigger may be rolled back, but the trigger rolls on.

Triggers introduce a long-term maintenance headache. You can read a stored-procedure from top to bottom and imagine you understand what it does. But unless you examine every tables it touches – you don’t. Little bits of code may be running silently which augment or even reverse some of the logic within the stored-procedure.

Triggers are used by lazy developers to ‘bolt on’ new features to applications, rather than track-down all the code that could insert/update/delete from a table and add the code (or a link to it) there.

This would be forgivable if the application code was closed or propitiatory, but never when the application is open to the application developer, who just cannot be bothered to integrate code changes properly, and cares not-a-jot about long-term maintenance headaches.

(slow breaths, slow breaths)

PostgreSQL study notes – Features

  1. Object Relational Database Management System (ORDBMS) objects (eg: tables) can be related in a Hierarchy: Parent -> Child
  2. Transactional RDBMS: SQL statements have implicit: BEGIN; COMMIT: statements. SQL statements may also have explicit BEGIN COMMIT statements
  3. developed at UC Berkeley like along with bsd-unix
  4. One process per connection: master process = “postmaster” auto-spawns per new connection
  5. Processed (pid’s) use one cpu-core per connection: o/s may spawn new connections on a different cpu-core. no cross-core queries
  6. Multiple helper processes, which appear as ‘postgres’ instances, always running eg: stats collector, background writer (protect agains sudden failure ), auto-vacuum (cleanup/ space reclaimer), wal sender (that’s write ahead log)
  7. max db size: unlimited – limited by available storage.(terabytes, perabytes, exabytes)
  8. max table size: 32tb – stored as multiple 1gb files – changable (could be prob for some o/s’s)
  9. max row size: 400gb
  10. max column size: 1gb (per row ie: per field)
  11. max indexes per table: unlimited
  12. max identifier length (db objects – tables, columns, triggers, functions@ 63 bytes. this is extensible via source code
  13. default listener tcp port 5432. so may install postgresql as non-privilaged user
  14. users are distinct from o/s users
  15. users are authenticated globally (per server), then assigned permissions per database.
  16. inheratance. tables lower in hierachy may inherit columns from heigher tables (ie parents) so long as no contraints eg foreign keys.
  17. case insensative commands – without double quotes (eg: select * from syslog;)
  18. case sensative commands – with double quotes – (eg: select * from “syslog”;)
  19. three primary config files, located in postgres-root A.pg_hba.conf (host based access) B.postgresql.conf general settings C. pg_ident.conf – user mappings
  20. integrated log rotation (config by age or size)


Scripting languages are wonderful things. They use subsets of English and are therefore easy to learn (EG: update, delete, get, put).

However capitalization is totally redundant. A capital letters marks the beginning of a sentence, but scripts do not use sentences.

One alphabet is enough (a to z) who needs another one (A to Z) that is completely equivalent?

Imagine responding to the adhoc query “Please email me a list of most ordered items over Christmas” with “in what font?” lol

Although … one trick that I have used over the years, with having two different ways to express the same data,  is to temporarily change the capitalization of text to double-check for myself that Replication etc is actually working (before the days of tracer-token poo sticks). Changing the data look without changing the data value.

Another sneaky trick is making mass changes to data whilst adding a flag (to only the changed data). For example changing every field containing ‘unsubscribe’ to ‘uNsubscribe’, or ‘yes’to ‘yEs’.

And then repeating with un-flagged fields until only ‘unsuscribe’ or ‘Yep’ remain (lol).

This (typical DBA belt & braces) method almost always guarantees you will not induce any unintended processing errors further down the line, as the data always remains the same length, type and meaning.


Senior Consultant Motivations

Autonomy, Mastery, and Purpose.

  1. To prioritize their own time.
  2. To master their profession.
  3. To do important work that matters.

SQL Deadlock graph – arrows

In a SQL deadlock graph the direction of the arrows is an interesting thing.


With my mechanistic head on, I am imagining it as …

  1. Spid-a requested a lock, and then got a lock (a two-way trip)
  2. Spid-b requested a lock, and then got a lock (arrow ends-up pointing at spid)
  3. Spid-a requested a lock, and is waiting (a one-way thing)
  4. Spid-b requested a lock, and is waiting (arrow pointing away from spid)

Capture Deadlock Graph using Profiler

To Capture a Deadlock Graph using Profiler (NB: with SQL 2008 and above you can also use an extended-event).

  • File / New trace
  • Connection details
  • Use Template / Blank
  • Events Selection / Locks ..1) DeadlockGraph 2) Lock:Deadlock 3) Lock:Deadlock Chain
  • Event Extraction Settings / Save Deadlock XML events seperately / (somefilename)
  • Each deadlock in a distinct file
  • All Deadlocks
  • Run
  • (wait)
  • File / Export / Extract SQL Server Events / Extract deadlock Events / (somefilename2)

Log-shipping Restore error: empty file

I noticed a log-shipping RESTORE job had started failing. Looking back through the job history I found the last two “good” executions contained errors …

*** Error: Could not apply log backup file ‘SomePath\SomeFile.trn’ to secondary database. The volume is empty.

I looked at the path\file specified and found the file was zero size.

I looked on the network-share where the files are backed-up-to \ copied-from, and found the same file was NOT zero size.

I manually copied the file from the network-share to the destination folder (on the DR server), overwriting the empty file.

Log-shipping recovered over the next few hours.

Check every Linked Server

I was unable to cobble together some Powershell code that I could execute within a job-step to check our linked-servers were working.

So I resorted to making the best of the built-in, but flawed, “SP_testlinkedserver” (Its a flawed procedure as if a link fails, it crashes, slowly).

The code below, when ran in a job-step overnight, will dynamically create one job for each linked-server on the box. The job(s) will then run and email the “DBA” operator every linked-server that fails, before deleting themselves.

-- testlinkedservers.sql

-- get list of all linked servers on this box

		srv_name varchar(MAX), 
		srv_providername varchar(MAX), 
		srv_product varchar(MAX), 
		srv_datasource varchar(MAX), 
		srv_providerstring varchar(MAX), 
		srv_location varchar(MAX), 
		srv_cat varchar(MAX))
	INSERT INTO #temp EXEC sp_linkedservers

-- loop

	SELECT TOP 1 @name = srv_name FROM #temp

	-- create the job code

	SELECT @jobId = job_id FROM msdb.dbo.sysjobs WHERE (name = N''DBA - LinkedServerTest ' + @name + ''')
	IF (@jobId IS NULL)
	EXEC msdb.dbo.sp_add_job @job_name=N''DBA - LinkedServerTest ' + @name + ''', 
		@description=N''No description available.'', 
		@category_name=N''[Uncategorized (Local)]'', 
		@job_id = @jobId OUTPUT

	-- create the job-step code

	IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId AND step_id = 1)
	EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N''one'', 
		@os_run_priority=0, @subsystem=N''TSQL'', 
		@command=N''sp_testlinkedserver [' + @name + ']'', 

	-- create instantiation code

	EXEC msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N''(local)''


	-- create the job


	-- run the job

	SET @run = 'EXECUTE msdb.dbo.sp_start_job ''DBA - LinkedServerTest ' + @name + ''''

        -- move to next row in loop

	DELETE FROM #temp WHERE srv_name = @name


Log-Shipping Monitor incorrect after outage

After a virtualization issue caused an unscheduled rebooted of production, I found the DR (log-shipping) monitor incorrectly reporting issues.

It seems the linked-server was no longer working, as @@servername returned NULL on Prod.

On Production SP_AddServer failed as the servername was in sys.servers – but not with server_id 0 (as needed for @@servername).

Removing the incorrect entry with SP_DropServer failed as there were remote- connections using it. And SP_DropRemoteLogin failed as there was not a remote users called NULL.

The fix was to remove log-shipping first using the GUI, which was only partially successful. Then manually, by deleting jobs from prod and DR, and truncating system-tables in MSDB starting log_shipping~ (on both servers).

Once log-shipping was cleaned off both machines I could use … EXEC SP_DropServer ‘ProdServer’, ‘droplogins’ followed by EXEC SP_AddServer ‘ProdServer’, LOCAL successfully. Now the server-name was correctly at the top of sys.servers the only task left was to schedule a reboot so Select @@ServerName would pick-up the new value.

After which I could re-configure log-shipping.

Why do Diff Restores fail

Diff restores have to happen directly after a full restore, and will fail if a full backup was taken between the two being restored.

Move Databases

I wanted to move about 50 databases on a Sharepoint server off the C-drive
(yes I know).

Sadly the only place I could move both datafiles and logfiles to was the D-Drive
(I know, I know).

Here’s the code I wrote to help me …


-- to move a user-database to different-drives on the same-server

USE [master]

-- backup

	DECLARE @dbname VARCHAR(max) = 'SomeDatabaseName' -- database name

	DECLARE @backup_cmd VARCHAR(MAX) = 'BACKUP DATABASE ['+ @dbname + ']
	TO DISK = N''\\SomeNetworkShare\SomeServerName_' + @dbname + '.bak''

	SELECT (@backup_cmd)
	--EXEC (@backup_cmd)

-- kill connections

	DECLARE @kill_cmd VARCHAR(MAX) = 'DECLARE @kill varchar(8000) = '''';
	SELECT @kill=@kill+''kill ''+convert(varchar(5),spid)+'';'' from master..sysprocesses 
	WHERE dbid=db_id(''' + @dbname + ''') and spid>50;
	EXEC (@kill);'

	SELECT (@kill_cmd)
	--EXEC (@kill_cmd)

-- restore

	DECLARE @restore_cmd VARCHAR(MAX) = 'RESTORE DATABASE [' + @dbname + ']
	FROM DISK = N''\\SomeNetworkShare\SomeServerName_' + @dbname + '.bak''
	WITH FILE = 1,  
	MOVE N''' + @dbname + ''' TO N''D:\SQL_Data\' + @dbname + '.mdf'',
	MOVE N''' + @dbname + '_log'' TO N''D:\SQL_Log\' + @dbname + '_log.ldf'',

	SELECT (@restore_cmd)
	--EXEC (@restore_cmd)

Estimate backup size

To get a quick estimate of the full backup size of our user databases – for planning – I ran this across production and pasted ‘database_name’ and ‘reserved’ into a spreadsheet.

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; if db_id()>4 EXEC sp_spaceused'


My favorite configuration of sp_WhoIsActive is …

EXEC [master].[dbo].[sp_WhoIsActive] @get_plans=1, @get_additional_info = 1
--, @get_task_info = 2
--, @sort_order = '[cpu] desc'
--, @filter_type = 'session', @filter = '2018'
--, @filter_type = 'login', @filter = 'windowslogin'
, @output_column_list = '[dd%][session_id][block%][sql_text][sql_command][login_name][CPU%][wait_info]

Who deleted that data?

Sadly I could not find out.

Going forward – To capture deletes on a table I set-up a regular ‘after delete’ trigger with some extra columns to hold system functions.

This allowed me to capture the date/time, PC-Name and login that originated deletes. Here is my working lab …


--create table to be monitored and add some data
	CREATE TABLE t1 (c1 INT, c2 int)
	INSERT INTO t1 VALUES (1,7), (2,8), (3,9)

-- create audit table

-- check contents of both tables
	SELECT * from t1
	SELECT * FROM t1_audit

-- create trigger
	CREATE TRIGGER trg_ItemDelete 
	ON dbo.t1 
	INSERT INTO dbo.t1_audit(c1, c2, c3, c4, c5, c6)
			FROM Deleted d

-- delete a row (firing the trigger)

-- check contents of both tables again
	SELECT * from t1
	SELECT * FROM t1_audit

-- tidy up
	IF OBJECT_ID ('trg_ItemDelete', 'TR') IS NOT NULL DROP TRIGGER trg_ItemDelete;
   	drop TABLE t1
	drop TABLE t1_audit

Two SSDT’s

Microsoft seems to have two products called SSDT (SQL Server Data Tools)

1. A development environment for SSIS etc.

2. A source control tool that uses TFS.

To install 1. You choose SSDT as an option whilst installing SQL Server 2008r2 or 2012.

To install 2. It is a separate download for Visual Studio 10 and 12, or a post installation option for Visual Studio 13.

(SSDT is really just a badly integrated set of tools. I find treating it as two distinct products keeps me sane whilst googling)

Log Space

I noticed a jump in logfile size the other day and was wondering how to predict a autogrowth event.

I know old data is truncated after a log-backup but that’s internal and not normally visable.

I came up with this to run across production …

-- To help find near-full logfiles that may autogrow soon.

-- create table to hold raw data
CREATE TABLE #temp (DBName varchar(100), SizeMB int, UsedPct float, [STATUS] bit)

-- populate table
INSERT #temp EXEC('DBCC SQLPERF(logspace)')

-- output
SELECT DBName, SizeMB, UsedPct FROM #temp --WHERE UsedPct > 90 -- 90% full

-- clean-up

Orphaned Users

Here’s a quick script to fix orphaned users after a migration …


-- create temp table

-- populate temp table with orphaned logins
	INSERT #orphans(oname,osid)
	EXEC sp_change_users_login @Action='Report';

-- loop to fix / or else create login with default pw
	WHILE (SELECT COUNT(*) FROM #orphans) > 0
		SELECT TOP 1 @name = oname FROM #orphans 
		SET @cmd = 'EXEC sp_change_users_login ''Auto_Fix'', ''' + @name + ''', NULL, ''B3r12-3x$098f6'';'
		DELETE FROM #orphans WHERE oname = @name
		EXEC (@cmd)

-- tidy up
	DROP TABLE #orphans 

Making index changes to Production

These days I use a SQL Job called ‘DBA – index maint’.

Whenever I have an index change to make I paste the script into a new step, name that step with today’s date, change the ‘start step’ to that step, and schedule it to run once overnight.

This gives me a history and outcome, along-side the exact action.

SQL Safe error “Cannot connect to SQL Server instance”

This was fixed by re-installing SQL Safe. Bonus – Here is a working restore command with move

EXEC [master].[dbo].[xp_ss_restore] 
	@database = 'SomeDatabase',
	@filename = 'J:\backups\SomeDatabase.BAK', 
	@backuptype = 'Full',
	@withmove = 'SomeDatabase_data "J:\sql_data\SomeDatabase_data.mdf"',
	@withmove = 'SomeDatabase_log "J:\sql_log\SomeDatabase_log.ldf"',
	@recoverymode = 'recovery',
	@replace = '1';

Compress all tables

As part of my management of our MDW I wrote this to help compress the user-table in the database.

FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]

Diff Backup / Restore (part 3)

On my Reporting Server I created the SQL Job ‘DBA – Restore MyDatabase’ with two steps 1) Restore MyDatabase, and 2) Add Users.

Step 2 just de-orphaned some user accounts EG:-

EXEC sp_change_users_login 'Auto_Fix', 'uidServerLink';

Step 1 contained this code …

-- Restore

	EXECUTE [master].[dbo].[DatabaseRestore] 
		@dbName = 'MyDatabase',
		@SourceServer = 'MySourceServer',
		@backupPath = 'M:\Backups'

-- Change recovery model

	ALTER DATABASE MyDatabase set recovery SIMPLE

Diff Backup / Restore (part 2)

On the Production Server I created the Job ‘DBA – Backup/Restore MyDatabase’ with two steps 1) Backup MyDatabase, and 2) Restore MyDatabase.

Step 2 just started the Restore job on the Reporting server (detailed in “Part 3”)

Step 1 needed to check that no random backups had happened in the last 24 hours before starting a Diff Backup …

-- If its Friday or the LSN's do not match - do a FULL backup

	DECLARE @DBName VARCHAR(100) = 'MyDatabase' --<< Database Name
	IF (SELECT DATEPART(dw, GETDATE())) = 6	--<< = Friday
	OR (SELECT MAX(differential_base_lsn)
	    FROM [MyProdServer].[master].[sys].[master_files]
	    WHERE [name] LIKE '%' + @DBName + '%')
	   (SELECT MAX(differential_base_lsn)
	    FROM [MyReportServer].[master].[sys].[master_files]
	    WHERE [name] LIKE '%' + @DBName + '%')

		SELECT 'We can only do a FULL backup'
		EXECUTE [master].[dbo].[DatabaseBackup] 
			@Databases = @DBName,
			@Directory = N'\\MyReportServer\backups', 
			@BackupType = 'FULL', 
			@CleanupTime = 1, --<< ONE HOUR
			@CleanupMode = 'BEFORE_BACKUP',
			@Compress = 'Y',
			@CheckSum = 'Y',
			@LogToTable = 'Y'

-- Else do a DIFF backup

		SELECT 'we can do a diff backup' 
		EXECUTE [master].[dbo].[DatabaseBackup] 
			@Databases = @DBName,
			@Directory = N'\\MyReportServer\backups', 
			@BackupType = 'DIFF',
			@CleanupTime = 168, --<< ONE WEEK
			@CleanupMode = 'BEFORE_BACKUP',
			@Compress = 'Y',
			@CheckSum = 'Y',
			@LogToTable = 'Y'

Diff Backup / Restore (part 1)

Although Ola’s Backup solution works great …

For this project I needed a corresponding Restore procedure, so I could setup nightly Diff Backup / Restores (from Prod to Reporting). Without having to write too much code 🙂

I modified the code from here for our environment.

In my next posts I will detail the SQL Jobs for this project.

USE [master]

CREATE PROCEDURE [dbo].[DatabaseRestore]
		@dbName sysname,
		@SourceServer NVARCHAR(500),
		@backupPath NVARCHAR(500)


/* To restore backups created from ola.hallengren's backup solution (RS) */

			@lastFullBackup NVARCHAR(500),
			@lastDiffBackup NVARCHAR(500),
			@backupFile NVARCHAR(500)

	DECLARE @fileList TABLE (backupFile NVARCHAR(255))
	DECLARE @directoryList TABLE (backupFile NVARCHAR(255))

/* Kill any connections */

	DECLARE @kill VARCHAR(8000) = '';
	SELECT  @kill = @kill + 'kill ' + CONVERT(VARCHAR(5), spid) + ';'
		FROM [master].[dbo].[sysprocesses]
		WHERE dbid = DB_ID(@dbName)
		AND spid > 50;
	EXEC (@kill);

/* Match that of Olas output */

	SET @backupPath = @backupPath + '\' + @SourceServer + '\' + @dbName + '\'

/* Get List of Files */

	SET @cmd = 'DIR /s /b /O D ' + @backupPath
	IF (SELECT value_in_use
		FROM sys.configurations
		WHERE name = 'xp_cmdshell') = 0
	BEGIN /* cmd shell is disabled */
		EXEC sp_configure 'show advanced options', 1 RECONFIGURE
		EXEC sp_configure xp_cmdshell, 1 RECONFIGURE
		INSERT INTO @fileList(backupFile) EXEC master.sys.xp_cmdshell @cmd
		EXEC sp_configure 'xp_cmdshell', 0 RECONFIGURE
		EXEC sp_configure 'show advanced options', 0 RECONFIGURE
	ELSE /* cmd shell is enabled */
		INSERT INTO @fileList(backupFile) EXEC master.sys.xp_cmdshell @cmd

/* Find latest full backup */

	SELECT @lastFullBackup = MAX(backupFile) 
	FROM @fileList 
	WHERE backupFile LIKE '%' + @SourceServer + '_' + @dbName + '_FULL_%.bak'

	SET @cmd = 'RESTORE DATABASE [' + @dbName + '] FROM DISK = ''' 
	   + @lastFullBackup + ''' WITH NORECOVERY, REPLACE'
	SELECT (@cmd); EXEC (@cmd)

/* Find latest diff backup */

	SELECT @lastDiffBackup = MAX(backupFile)
	FROM @fileList 
	WHERE backupFile  LIKE '%' + @SourceServer + '_' + @dbName + '_DIFF_%.bak'
	AND RIGHT(backupfile, 19) > RIGHT(@lastFullBackup, 19)

/* check to make sure there is a diff backup */

	IF @lastDiffBackup IS NOT NULL
		SET @cmd = 'RESTORE DATABASE [' + @dbName + '] FROM DISK = ''' 
			   + @lastDiffBackup + ''' WITH NORECOVERY'
		SELECT (@cmd); EXEC (@cmd)
		SET @lastFullBackup = @lastDiffBackup

--/* check for log backups */

--	DECLARE backupFiles CURSOR FOR 
--	SELECT backupFile 
--	FROM @fileList
--	WHERE backupFile LIKE  '%' + @SourceServer + '_' + @dbName + '_LOG_%.trn'
--	AND RIGHT(backupfile, 19) > RIGHT(@lastFullBackup, 19)

--	OPEN backupFiles 

--/* Loop through all the files for the database */

--	FETCH NEXT FROM backupFiles INTO @backupFile 

--		BEGIN 
--		   SET @cmd = 'RESTORE LOG [' + @dbName + '] FROM DISK = ''' 
--			   + @backupFile + ''' WITH NORECOVERY'
--		   SELECT (@cmd); EXEC (@cmd)
--		   FETCH NEXT FROM backupFiles INTO @backupFile 
--		END

--	CLOSE backupFiles 
--	DEALLOCATE backupFiles 

/* put database in a useable state */

	SET @cmd = 'RESTORE DATABASE [' + @dbName + '] WITH RECOVERY'
	SELECT (@cmd); EXEC (@cmd)



Having uninstalled UCP and reinstalled MDW I found the MDW job “sysutility_get_views_data_into_cache_tables” failing at step-3 with an error message about invalid synonyms.

The fix was to re-create the MSDB sysnonyms from a clean SQL 2012 server.

Namely …

CREATE SYNONYM [dbo].[syn_sysutility_ucp_databases] 
FOR [msdb].[dbo].[sysutility_ucp_databases_stub]

CREATE SYNONYM [dbo].[syn_sysutility_ucp_filegroups] 
FOR [msdb].[dbo].[sysutility_ucp_filegroups_stub]

CREATE SYNONYM [dbo].[syn_sysutility_ucp_dacs] 
FOR [msdb].[dbo].[sysutility_ucp_dacs_stub]

CREATE SYNONYM [dbo].[syn_sysutility_ucp_smo_servers] 
FOR [msdb].[dbo].[sysutility_ucp_smo_servers_stub]

CREATE SYNONYM [dbo].[syn_sysutility_ucp_volumes] 
FOR [msdb].[dbo].[sysutility_ucp_volumes_stub]

CREATE SYNONYM [dbo].[syn_sysutility_ucp_computers] 
FOR [msdb].[dbo].[sysutility_ucp_computers_stub]

CREATE SYNONYM [dbo].[syn_sysutility_ucp_logfiles] 
FOR [msdb].[dbo].[sysutility_ucp_logfiles_stub]

CREATE SYNONYM [dbo].[syn_sysutility_ucp_datafiles] 
FOR [msdb].[dbo].[sysutility_ucp_datafiles_stub]

CREATE SYNONYM [dbo].[syn_sysutility_ucp_space_utilization] 
FOR [msdb].[dbo].[sysutility_ucp_space_utilization_stub]

CREATE SYNONYM [dbo].[syn_sysutility_ucp_cpu_utilization] 
FOR [msdb].[dbo].[sysutility_ucp_cpu_utilization_stub]

DBA Rule #8

Avoid 3rd Party management software. Backup software doubly so.