Bellingham SQL Server Users Group – Building a Static Data Warehouse Server

Download PDF

Please help spread the word to anyone who may want to attend this free users group meeting and presentation.

The November meeting of the Bellingham SQL Server Users Group (PASS Chapter) will be held on Wednesday November 18th at 6:00pm.

Welcoming Ron Talmage from SolidQ with his presentation on Building a Static Data Warehouse Server.
The agenda for the meeting will be:

– Welcome and Pizza
– Chapter news and announcements
– Technical presentation (Ron Talmage)
– Socialize and networking

The chapter meeting will be at the following address. Thanks to Derrick Bovenkamp for providing the location for this meeting.

709 West Orchard Drive #4
Bellingham, WA 98225

This is the office right next to Jeckyl and Hyde Ale House on Orchard Drive.
For any questions, please contact Steve Stedman (360)610-7833

Featured presentation:

Building a Static Data Warehouse Server

Sometimes users require underlying data to be static and unchanging. In this session we’ll examine steps used to create a static server from a copy of a live data warehouse server, including implementing clustered columnstore on large partitioned tables, simplifying the management of files and filegroups, and reclaiming large amounts of database space.

Presented by Ron Talmage – Mentor and Co-founder, SolidQ

Ron is a mentor and co-founder with SolidQ. He is a SQL Server MVP and current chapter leader of the Pacific Northwest SQL Server Users Group. He has authored numerous SQL Server white papers and contributes to SQL Server technical publications.

Posted in PASS Chapter Tagged with: , , , , ,

Epic Life Goal Completed: Speaking at PASS Summit – Advanced CTEs

Download PDF

Last week I had the great opportunity to speak at PASS Summit 2015. My presentation was on Common Table Expressions.

Advanced CTEs


Here is the sample code from the presentation.


Related Links:

Posted in PASS Summit Tagged with: , , , , , ,

Advanced Common Table Expressions and Recursive Query Technique

Download PDF

Friday morning at 8:00am at PASS Summit 15 in Seattle, I will be presenting my Advanced Common Table Expressions and Recursive Query Technique session.

Advanced Common Table Expressions

Advanced Common Table Expressions

Here is the abstract:

You might have been introduced to Common Table Expressions (CTEs) and understand the WITH syntax, but want to know more. Learn how recursive queries work with CTEs and how to display hierarchical data. Did you know that you can INSERT, UPDATE, and DELETE data from CTEs? This session covers some common use cases for CTEs, including finding holes in patterns, finding and removing duplicate data, string parsing, and more. Get an in-depth understanding of the performance behind a CTE and learn when a CTE is the right (or wrong) solution. Finally, take a look at some classic recursive algorithms and how they can be implemented with CTEs.

For more details check out the session on the PASS Summit 2015 website.

There is a long history behind this presentation, it originally started as a presentation at SQL Saturday #108 in Redmond in 2012. From there I presented it at several other SQL Saturdays including Vancouver BC in 2012. After presenting on CTEs, at about 8 different SQL Saturdays, I eventually split the session into two, the Introduction to Common Table Expressions, and the Advanced CTEs. This was around the same time that I finished my book on Common Table Expressions which released the day of SQL Saturday 212 in Redmond in 2013. I found that once it was split into the Introduction to Common Table Expressions and Advanced Common Table Expressions that the Advanced session was much more popular.

I was able to practice this session recently at the 24 Hours of PASS in June of 2015, and at the Bellingham SQL Server Users Group meeting just 2 weeks ago.

The Advanced Common Table Expressions is where I cover fun things that you can do with Common Table Expressions, including some really cool formatting techniques for hierarchical data.

I hope to see you there.

Related Links:

Posted in PASS Summit Tagged with:

Transactions Rolled Back in Database

Download PDF

IF you are browsing your error log and come across an error message stating that “1 transactions rolled forward in database ‘msdb'”, or “X transactions rolled back in database ‘msdb'” you might be a bit alarmed. You might also notice similar error messages for master, tempdb or user databases.

msdb transactions rolled back in database


How can this be a good thing? Why are transactions being rolled back or rolled forward?

Read more ›

Posted in SQL Server Tagged with:


Download PDF

Here is a discovery that I made using the Database Health Monitor historic wait monitoring, on a server with slow storage where the backups were being written.

If you are seeing excessive waits on the PREEMPTIVE_OS_GETPROCADDRESS wait type and xp_create_subdir is the command with the wait, and this is occurring at the time your backups are being run, it is a symptom that the storage location for your backups is having I/O difficulties.


I noticed this on a server with an external USB 2.0 attached hard drive that was being used for backups, and on a second server with a USB 3.0 external hard drive. When the backups run, there was a wait for the process to attempt to see if the backup directory exists, and to create it if it did not.

Read more ›

Posted in Performance, Performance Tuning Tagged with: ,

Using SQL Server Compressed Backups

Download PDF

The Quick Scan report in Database Health Monitor detects when you are performing SQL Server backups without compression, when the compression option is available. This applies to Full backups, Transaction log backups and Differential backups.

Not using compressed backups? Why not?

Benefits of compressed backups

  • Faster backup time
  • Faster restore time
  • Less I/O at backup and restore time
  • Since you are using less disk space, you can keep more backups around.

There are a couple minor drawbacks, the compressed backups take more CPU, not much more, but a tiny bit more.  Also the compressed backups don’t compress very much when your database is using Transparent Data Encryption (TDE).

Read more ›

Posted in Database Health Tagged with: , , , , , ,

Free SQL Server Training Videos

Download PDF

Over the last year, I have published many videos to YouTube with Free SQL Server Training. You can find these on my YouTube Channel at the following link:


Most of the focus has been around training people for the 70-461 exam certification.

Full Length Free SQL Server Training Videos

Some of these are full length at around an hour to an hour and a half like this one on Time Functions, Logical Functions, and User Defined Functions.

Other videos are short and focused on a specif topic like this one on Using the TSQL DATEPART Function at just under 3 minutes.

Take a look, enjoy the videos, and hopefully learn something along the way. I hope that sharing these Free SQL Server training videos will help you become stronger at your SQL Server skills.


Need Help

Stedman Solutions, the provider of the Database Health Monitor Application offers consulting solutions, and can help with your SQL Server Administration needs.

Stedman Solutions Remote Skilled DBA Services


Posted in 70-461 Training, Classes Tagged with: , , ,

Max Server Memory – SQL Server

Download PDF

Max Server Memory Setting Explained

If you have more memory than your database and applications on the SQL Server will ever use than this is not a problem, but when you run into memory constraints this setting is much more important.

SQL Server attempts to use as much memory as possible, and when there is no more memory available, SQL Server will use much more I/O due to data and index pages having to be read from disk more often. This works great for SQL Server, but what happens is that SQL Server will take almost all the memory leaving very little for the operating system processes or other applications that are running.

The default for this setting is 2147483647 which is probably more memory that your server has, which effectively tells SQL Server to take as much memory as it wants to.

Read more ›

Posted in Performance, Server Health Tagged with: , , , , , , ,

SQL Server – Dedicated Admin Connection (remote DAC)

Download PDF

In SQL Server there is a special connection that can be used to connect to the database, the remote Dedicated Admin Connection or (DAC). This is a special connection that allows the administrator to connect to the SQL Server even if something has occurred that is preventing connections on the normal port.

One quick way to check if the DAC connection is available is to try connecting to the server name with ADMIN: in front of it from SSMS, like this:

DAC1If the DAC connection is not enabled, you will see an error message that looks like this.DAC1error


Another way to see if it is enabled is to open a normal connection to your database and run a query to check.

EXEC sp_configure 'remote admin connections';

If the running value is 0, then it is not configured.  If it shows 1 then it is configured.

Using TSQL you can turn on the DAC like this:

EXEC sp_configure 'remote admin connections', 1;

Be sure to run the RECONFIGURE command to allow the change to be activated.



Posted in DBA Tagged with: , , , , , ,

SQL Saturday – When corruption strikes – downloads

Download PDF

Here are the slides for my presentation at SQL Saturday, at Las Vegas today.

SQL Saturday Las Vegas

Download the presentation here CorruptionPresentation.pdf



Posted in SQL Saturday Tagged with:

SQL Server Performance Tuning

Need help with SQL Server Performance Tuning, contact Steve at Stedman Solutions, LLC for help, or take a look at the performance tuning page at the blog. We can help with tuning a single query or with figuring out why your SQL Server is running slow. Free 30 minute consultation.

Newsletter Signup

Stay informed of Database Corruption Challenge events, and other things happening at with my newsletter.
Newsletter signup form.

Stedman Solutions