John McCormack DBA

SQL Server Databases and Cloud

  • Personal
    • About
  • Free Training
    • SQL Server on Amazon RDS (Free Course)
    • Free practice questions to help you pass DP-900
  • Save money in Azure
    • Azure IaaS SQL Backups – Stop burning money
    • Your Azure SQL Database and Managed Instance is too big
    • Turn the cloud off at bedtime to save 70%
    • Your Azure SQL Virtual Machine might be too big
    • Save money with Azure SQL DB serverless
    • Save up to 73% with reserved instances
    • Delete unused instances to save money in Azure
  • Hire me
    • 60 minute cost optimization
    • Let me solve your SQL Server problems
    • Take a look at my Sessionize speaker’s profile

Troubleshooting Transactional Replication in SQL Server

4th February 2016 By John McCormack Leave a Comment

SQL Server ReplicationThis might make me the odd one out but I actually really like replication. It took me a while to get comfortable with it but when I did and when I learned how to troubleshoot transactional replication confidently, I became a fan. Since I exclusively use transactional replication and not snapshot replication or merge replication, this post is only about transactional replication and in particular, how to troubleshoot transactional replication errors.

In the production system I work on, replication is highly reliable and rarely if ever causes the DBA’s headaches. It can be less so in our plethora of dev and qa boxes, probably down to rate of change in these environments with regular refreshes. Due to this, I’ve had to fix it many times. As I explain how I troubleshoot replication errors, I assume you know the basics of how replication works. If you don’t, a really good place to start is books online. It describes how replication uses a publishing metaphor and describes all the component parts in detail.

[Read more…]

Filed Under: front-page, Guides Tagged With: replication error 1205, replication error 21074, replication error 3729, SQL server, SQLNEWBLOGGER, transactional replication

My route to achieving MCSE Data Platform

28th January 2016 By John McCormack 2 Comments

This is my first non technical blog. Rather, it is about career and personal development.

70-462 Administering SQL Server 2012 databasesWhy did I want to achieve MCSE Data Platform?

Well, at first I didn’t. It seemed like a million miles away from where I started. My initial goal was just to pass the 70-462 exam (Administering Microsoft SQL Server 2012 Databases) as my boss had suggested it would be a good thing to learn. This was the first I had heard about Microsoft certifications. From there, with each subsequent exam pass, I just extended my goal to MCSA SQL Server 2012 and then MCSE Data Platform.

Timeline

I had set the target of passing 70-462 by the end of 2013 which would be just over 1 year as a DBA. The great thing about the certification is you can do the 70-461, 70-462 and 70-463 in any order. Since most of my limited knowledge was about Database Administration and the subject I needed to learn most about initially was also Database Administration, this was a no brainer.

[Read more…]

Filed Under: front-page, Personal Tagged With: 70-461, 70-462, 70-463, 70-464, 70-465, certifications, mcp, mcsa SQL Server 2012, mcse data platform, microsoft learning

The EXCEPT operator in T-SQL

19th January 2016 By John McCormack Leave a Comment

The except operator in t-sql

EXCEPT OperatorWhen I first read about the except operator in t-sql and saw how simple it was to use, it knew I would be using it a lot. And I have.

I first came across it when studying for the 70-461 exam (Querying Microsoft SQL Server 2012). I keep trying to tell my colleagues about it but it seems they already know. So the only logical thing to do was write a blog post about it.

What does it do?

The EXCEPT OPERATOR compares two sets of data and performs set difference. In other words, it returns all of the rows from query 1 EXCEPT when an identical row is returned from query 2. This makes EXCEPT a quick and easy way to compare two sets of data without using 3rd party tools or more complicated JOIN syntax.

What is the syntax?

[Read more…]

Filed Under: front-page, T-SQL Tagged With: adventureworks example, compare sets of data, set operator, t-sql, T-SQL EXCEPT operator

How to write a PIVOT query in T-SQL

12th January 2016 By John McCormack Leave a Comment

sql server pivotIn this post, I explain how to write a simple PIVOT query in T-SQL and why we might want to use the PIVOT operator. The Oxford English Dictionary definition of PIVOT is

The central point, pin, or shaft on which a mechanism turns or oscillates.

So what we are doing here is TURNING THE DATA AROUND (PIVOTING) for visual purposes. The object of the exercise is to return 3 wide rows of data, instead of the 36 unique rows in the table.

Create the table and data which we will use in out PIVOT query

Lets create a database table which records the size of different tables within a database over time.

[sql]
CREATE TABLE TableSize
(
TableSizeId int identity (1,1) Primary Key,
DatabaseName sysname,
Name sysname,
DataKB INT,
Collection_Date Date
)

[/sql]

[Read more…]

Filed Under: front-page, T-SQL Tagged With: aggregation, grouping, How to write a PIVOT query in T-SQL, PIVOT, PIVOT Operator, spreading, SQLNEWBLOGGER, t-sql, T-SQL PIVOT, t-sql scripts

Style over substance

6th January 2016 By John McCormack 2 Comments

When it comes to managing emails, I can easily receive about 600 a day. This is probably far too many (although that’s a subject for another day). The challenge with receiving so many emails, especially so many system generated errors and warnings is that they can fail to stand out. Important information can be missed. A lack of formatting can also make it difficult to understand what the problem is, if any.

Here is an example of a boring system generated message.

Boring SCOM AlertTo get around this, I always try to use some pretty HTML in my output just to help with the appearance and to make the message easier to read. I don’t go overboard, just a header tag, something like <h4> is usually enough (unless you want it really big) and of course I like to include my output in a table, usually with a splash of colour in the table header.

[Read more…]

Filed Under: front-page, Guides, T-SQL Tagged With: html email, send html email from sql server, sql, SQL server, style over substance, t-sql

  • « Previous Page
  • 1
  • …
  • 13
  • 14
  • 15
  • 16
  • Next Page »
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy

John McCormack · Copyright © 2025

 

Loading Comments...