Free SQL Server Training

As part of my new role as Director of Research and Development at Emergency Reporting, I have been asked to help the development team with training so that they can all take and pass the SQL Queries 70-461 Microsoft certification. Emergency Reporting recognizes the value in having all of developers or engineers well trained in SQL Server programming, since this is such a large part of what we do.

After looking it over and understanding all the topics needed for the 70-461 SQL Queries certification, Aaron Buma and I we have worked out a plan to get everyone on the development team through this training.

Based on the amount of value that I have received from the SQL Server community, I have been able to get approval to share this training with anyone who wants to participate.

Free Training

Since we will be doing all this training, the prep work to train one team or hundreds of people doesn’t change much, it’s still the same amount of work. So we have decided to open up the training to anyone who wants to attend via a webcast using Google On Air Broadcasts.

There are two options to access this training. One is to participate in the live training, and the other option is to follow my YouTube channel.

Schedule / Plan

Over the next 45 weeks we will be teaching one or two topics each week on Thursdays at 9:00am (pacific time) for about 70 to 90 minutes each week.

Each week when we cover a topic, we will be covering in detail as to be ready to address any questions that might be asked as part of the 70-461 exam.

Here is the plan for the next few weeks.

  • Jan 29th, 2015 : Aggregating Data and Aggregation Strategies
  • Feb 5th, 2015 : Top (n) Queries and Ranking Functions
  • Feb 12th, 2015 : Predicating and Tiling Ranked Data
  • Feb 19th, 2015 : Set Operators
  • Feb 26th, 2015 : Temporary Named Result Sets
  • March 5th, 2015 : Stored Procedures – Stored Procedure Techniques
  • March 12th, 2015 : Data Recursion

The following topics will be covered week over week until we get through them all.

  • String Functions
  • Time Functions
  • Derived Table Queries (ie Subqueries)
  • Merge Statement
  • Output Clause
  • Logical and Analytical Functions
  • User Defined Functions
  • Table Data Actions
  • Database Schemas and Synonyms
  • SQL Error Messages
  • Error Handling
  • Data Type Usage
  • Special Data Type Options
  • Dynamic SQL
  • Cursors
  • Transactions and Locking
  • Isolation Levels and Concurrency
  • Spatial Data Types
  • Spatial Aggregates
  • XML Modes
  • Shredding XML
  • Shredding, attributes, elements and levels
  • Creating Indexes
  • Creating Indexes with Code
  • Index Analysis
  • XML Queries
  • Xquery
  • XML Data Binding
  • The GUID Data Type
  • Constraints
  • Importing and Exporting XML
  • XML Namespaces
  • XML Schemas and Processing
  • After Triggers
  • Other Triggers
  • XML Indexes
  • Views
  • Updating and Maintaining Views

This schedule is subject to change, but that’s our best guess at the plan for now.

About the Instructors

Steve Stedman … That’s me, for more information on me, just visit my blog at SteveStedman.com. I am the Director of Research and Development at Emergency Reporting, I have spoken at many SQL Saturdays, other development conferences and local events. 25 years of SQL Server experience starting in 1990.

Aaron Buma … Research and Development Engineer at Emergency Reporting. About 10 years of SQL Server experience in high availability environments. Visit AaronBuma.com for more details. Aaron is also known for building and flying RC quadcopters / small UAV’s for aerial photography.

How to participate

Contact either of us through LinkedIn, Youtube, or Google Plus and let us know you want to participate, or just follow me on Twitter or Google Plus to get notified of upcoming sessions.

Just check my Google Plus profile every Thursday at 9:00am (pacific time) or a few minutes early to join in the weekly training.

We will also start using the #SqlClass hash tag on Twitter for notifications of upcoming events and where to find the recorded material.

Reference Material

For everyone attending the classes at Emergency Reporting we will be using the Joes 2 Pros SQL Queries books, however when purchasing them from Amazon, they appear to have been back ordered a bit, and if we are not able to get those, we may choose a different book set.

 

What are your favorite SQL Conferences / Trainings?

I am starting to fill out my schedule for SQL related conferences to attend this year, and I wanted to if anyone has any favorite conferences that I may be overlooking. PASS Summit 2015 is the big, must attend conference, and SQL Saturdays are always good, but I am interested in what other conferences people have found interesting.

So, if you have a favorite SQL conference, please add a reply to this post, I would be very interested in hearing what the community may recommends.

Database Health Beta 9.3 Released Today

Today I had the pleasure of releasing Beta 9.3 of the Database Health Monitor. It has been a whole week since the last beta update, and here is the outline of the new features and bug fixes.

Download at DatabaseHealth.com

New Features

  • New report, Partitioned Tables. This new report shows all of the partitioned tables in a database. Double clicking for drill down shows the new Partitioned Tables Advisor that shows details on the number of partitions, rows in each partition and the range for each partition.
  • The entire color scheme has been updated, not so blue.
  • Added double click action on the one time use query report.
  • The duplicate indexes chart has been migrated over to the new report format and colors.

Bug Fixes

  • Performance improvements and bug fixes on the table size advisor. It is still a bit slow on really huge tables, but it works well on most.
  • Fixed the drilldown when double clicking the chart view on the disk space report.
  • Improved exception handling on the needs parameters report.
  • Increased query timeout on the index fragmentation report.
  • Improved the column layout and sizing on the following reports:
    • Missing Indexes Report
    • Unclustered Tables Report
  • Improved error handling on all background threads when attempting to connect to a database. Better handling when the connection fails.
  • Fixed a bug on the plan cache panel not linking to the right report.
  • The enter key now does the same thing as double clicking for drilldown on all grids in the application.

Other Notes

  • Based on improved crash reporting in beta 9.1 and 9.2, I was able to learn more about the common problem areas in the application. This lead to improved exception handling in many of the areas where a database connection is established, along with better error messages when a connection fails for some reason.

 

The Partitioned Tables Report was a new addition to the Database Health monitor with this release.

The partition tables report shows a list of all the partitioned tables in a database. As shown here, there is just one partitioned table in this test database.

PartitionedTables2

Double clicking on the table [dbo].[orders] brings up the Partitioned Table Advisor which shows the number of rows in each partition, the percentage of the table represented by that partition, and the range items for the partition. In this example the partitions are split over date ranges.

 

 

PartitionedTablesAdvisor

I plan to keep the updates coming for some time.  Every week I am learning something new to help make the product even stronger.

Download at DatabaseHealth.com

-Steve Stedman

Database Health Monitor Beta 9.2 Released Today

I released Database Health Monitor Beta version 9.2 today.  No new features, just lots of bug fixing.

I don’t want to make it sound like database health reports is unstable, it is very stable, however with any software there are occasionally unexpected things that cause a crash. My goal is to get Database Health monitor as close to zero crash reports as possible, and to keep it there.

After doing some fixup on the crash reporting in the preview beta, I am now getting better details when something crashes in the application. For this beta and the next I will be mostly focused on overall stability.

Bug Fixes

  • Improved the column formatting on the Unclustered Tables report and the Connections report.
  • Improvement of the Pie chart pages, including
    • Reducing flashing on pie charts with only once piece.
    • Pie slice should highlight on mouse over not just on click
  • The Active Queries Report, now supports click or double click to show query advisor dialog.
  • Adding better exception handling and error bailout on the real time overview page
  • Cleaning up some of the layout on the technical debt dialogs.
  • Re-factoring code around the beta expiration dates
  • Removing reference to obsolete modules

Other Notes

Based on the increased popularity of Database Health Monitor, with more people using the program, there are more fringe case crash reports coming in.  There were many fixes in this update to fix more of those fringe case crash reports. The overall crash reporting has also been enhanced to allow for better tracking and fixing of crash reports in the future.

 

Remember when you run the install program there is a checkbox to allow the program to send crash reports to the developer. Please keep this checked so that I can have the most information possible when something does crash.

 

Links:

My SQL Server is Out Of Disk Space

As the DBA, you get a call from the IT Team who monitors disk space and they tell you the following:

“One of our server that has SQL Server running on it is running low on disk space. It has gone from around 60% disk utilization to 95% disk utilization just overnight. Whats the problem?”

In this case it happens that this is a server that you have never seen before. Someone installed a SQL Server to test out something product along the way, they never told the DBA about it, and suddenly this sever has become an important part of the company internal tools.

Sound familiar?  I know I have been hit with this before.

 

Right off, you have never seen this server before, you have no established baseline to compare it to, and you need to find out why it is running out of disk space rapidly. For me, some of the first things that would cross my mind would be:

  • Has anyone installed a new database or product new on this server recently?
  • Are any of the databases growing rapidly?
  • Are backup files being saved locally?
  • Are backups being run at all?
  • How big are each databases data files and log files?
  • Is there something outside of SQL Server taking up space?

Before I start troubleshooting I am going to just remote desktop to the server and double check the disk space myself to be sure that someone hasn’t misinterpreted the disk utilization.

DiskSpace1

 

At first glance the free disk space is 4.39GB out of 63GB.

DiskSpace2

 

 

As I am thinking about where to look first, the disk space drops to 2.28GB free.  Now I am very concerned.  I know that the C: Drive is filling up, this sever only has a C: drive, and if the Windows operating system runs out of disk space on the boot drive C:, then the server will crash, and often times will no longer boot.  Now things are serious, and must be dealt with quickly.

Now to start hunting, I could dig out several dozen of my favorite trouble shooting queries, or I could just run Database Health Monitor to start troubleshooting the issue. Database Health Monitor is my quick choice to track down problems like this.

I start up Database Health Monitor and connect to the SQL Server that is running out of space, to start looking around.

Initial View

 

Not immediately jumps out.  I see 12 databases on this SQL Server, and I see that the PerformanceTrouble database is using more CPU than any other database on the system.  Now to start looking at individual databases. The first database listed is called BadDB.  For this demo I am using a SQL Server that I use to test the Database Health Monitor Application, and I tend to use names that are descriptive of what the database is intended to do.

FirstDatabase

 

From this database I can see a few red flags. Well, we learn that the Database and Log files all exists on the C: Drive, and that this could cause the database to run out of disk space if the database grows, but this database isn’t very big, its only a few Megabytes, so I am not worried here.

Next I take a look at the Real Time Reports for Backup Sizes:

BackupSizes

 

For this database I can see that it has grow over the last year from around 2 megabytes to around 10 megabytes. All really small.  I also notice from the File names that the backups are being saved on the C: drive too.

As I click through the rest of the databases I see very similar results, its not great, but none of them are very big until I get to the database called PerformanceTrouble. Database Health Monitor shows this overview.

ProblemDatabase

 

Now this page points out a few things.  In bright red, there is a notice that shows the amount of log space allocated and being used by this database is around 24GB, for a database that is only 341MB in size.  This is an indication that backups are not being run, or that backups are not being run often enough, for instance log backups.  On this same page, I can see that the last full backup was run today at 5:00am.  I then click through the rest of the databases and nothing jumps out anywhere near this problem.

At this point I have spent about 3 minutes running Database Health Monitor, and I have found one problem that is using up about 33% of our entire hard drive.

 

How do we fix this issue?

First if I just doubleclick the Large Log File warning at the bottom of the application, my browser will open with one solution. There is a solution presented that looks a lot like this:

First we run this query to get the names of the log files.


-- check the size of the files.
SELECT size / 128.0 as sizeMB, name
FROM sys.database_files;

LogFileSizing

From that query we see that the name of the log file is PerformanceTrouble_log, the script below would get updated that your actual database name and log file name. The following script will shrink the log file for this database to around 1mb.

WARNING: Running this script will disconnect all other users from the database. Only run this on a production database if you have no other option.

-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE PerformanceTrouble SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (PerformanceTrouble_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE PerformanceTrouble SET RECOVERY FULL;
GO
-- Be sure to do a full backup, then kick off transaction log backups

-- check the size of the files.
SELECT size / 128.0 as sizeMB, name
FROM sys.database_files;

Once the script is run we can see that the log file has been shrunk from the 24GB to be around 1MB.

When we check the drive space on the server it now looks like this:
CDriveAfterScript
Now we are no longer at the the critical point where we think the server is going to run out of space, but there are two things that should be done next.

1. Run a full backup of this database.  If your SQL Server were to crash before the full backup is run you would have no way to recover back to the current point in time.

2. Monitor and confirm if the database log is continuing to grow, or if this was maybe a one time event that caused it to grow this large. If you determine that it is still growing then research and find out what is causing the transaction log to fill up.  You can use many of the features of Database Health Monitor to do this research.

 

The overall troubleshooting process took about 3 minutes, and the time to fix this issue only took around 2 minutes.

Setting the Board Background Image in Uzility

On of the fun things to do with Uzility is to customize your board background image, this allows you to personalize the look of the board to something that fits your personality or interests.

 

 

Steps to set your board background.

  1. Right click on the background of a board.
  2. From the popup menu choose Backgrounds, then Set Board Background.
  3. Browse for a file on your local computer that you want to upload as your board background.
  4. Choose upload, and that file will be set as your board background.