Database Health Monitor Version 2.1 coming soon

Download PDF

In the next week or two, I will be releasing version 2.1 of the Database Health Monitor application. I am just going through the final testing process now to get it ready for general release. There are a number of new features that have been requested since the release of version 2.0 that will be coming out in version 2.1. The most request feature was the addition of support for the F5 key to refresh reports. There have been several new server or instance level reports added, along with a new section to link to the instance level reports.

Database Health Monitor

Database Health Monitor Version 2.1 New Features

Here is a list of the newly requested features that are complete, and will be available in version 2.1 of the Database Health Monitor.

Read more ›

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

Introduction to SQL Server Class

Download PDF

From September 15th 2015 to October 1st 2015 on Tuesday and Thursday evenings I will be teaching a Introduction to SQL Server class. Interested you can sign up here:  Class Info and Signup

Introduction to SQL Server – Why take this course?

This course is intended as an Introduction to SQL Server for business persons or other professionals who have never used Microsoft SQL Server. Would you like to learn how to interact with a database in a meaningful way? We will cover the basics of getting started, including installation of the SQL Server product with associated tools. You will learn the foundations of structured query language including querying data, creating tables, and bringing data together from multiple tables. Once you understand how to query the data, we will look at building reports for a powerful presentation of your data. Over six evening classes we will go from zero (or minimal) knowledge of SQL Server to a solid understanding of how to query data, collect data, and most importantly how to present that data in a way that makes sense.

This class is made up of 6 sessions from 6-9 pm on Tuesdays and Thursdays starting Sept 15th at the Herald building conference room.

In this course we will be using SQL Server 2014, however almost all the topics we cover will apply directly to SQL Server 2008, SQL Server 2008R2, SQL Server 2012, SQL Server 2014 and SQL Server 2016.

Introduction to SQL Server

 

 

I am teaching this class in a partnership with CodeLily, a Computer Science and Web Development Education Studio located in Bellingham WA.

CodeLily Introduction to Databases: SQL Server

This will be similar to the Introduction to SQL Server class that I have taught at Western Washington University in the past, however it has been updated with new material and new technology.

This Introduction to SQL Server class is in person in a classroom type setting. Sorry if you are out of the area there is no virtual or remote option available.

Related Links

Posted in Classes Tagged with: ,

Silencing Backup Messages with Trace Flag 3226

Download PDF

As a DBA you regularly schedule backups you might have regular full backups, incremental backups and hopefully regular backups. Over time you might realize that your error log is not longer filled with errors, but instead a majority of your error log is filled with backup messages. “Backup Database successfully … “,  “Database backed up…”, “Log was backed up”. This makes it a bit difficult to find the actual errors in this flood of backup messages. Your error log is no longer an error log, but it looks more like a backup log.ErrorLog_flooded

Trace Flag 3226

Trace flags in SQL Server are switches or parameters that can be used to turn on or off different features. If you use trace flag 3226 this will turn off all successful backup messages being written to the error log. There are a couple of ways to use trace flag 3226, one is to use DBCC TRACEON to turn on trace flag 3226 for the currently running instance. Using this option will turn it on until the next time the database instance is restarted.

Read more ›

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

SQL Server Mentoring – Now Available

Download PDF

Yesterday I was asked by a client about consulting questions and for my help without the typical minimum hourly commitment. He pointed out that he has things that he could use help with that don’t require a multi-hour type project. After thinking it over a bit, I decided to add a SQL Server coaching program to my consulting business, I am calling it my SQL Server Mentoring program.

SQL Server Mentoring Process

Here is how it works if you want to utilize my mentoring services.

Step 1:  Set up a mentoring agreement. You can start this with the free 30 minute consultation form to schedule a time to talk to better understand the service, and so that I can better understand your needs and environment.

Read more ›

Posted in SQL Server Tagged with: , , , ,

SQL Server Database Corruption Repair

Download PDF

Database corruption repair with SQL Server is one of those things that you generally don’t see every day, but as a DBA, you are expected to be able to fix it quickly without any data loss. From the risk analysis matrix, it is one of those low frequency high impact scenarios that results in an extremely risky situation. This is one of those situations which could lead to great success, or massive failure depending on your training and skills.

These low frequency, high impact, high risk scenarios are the times that you need to rely on your training, or the training of someone more experienced in these areas.

Read more ›

Posted in Corruption Tagged with: , , ,

What is a Page Split

Download PDF

Tables, and indexes are organized in SQL Server into 8K chunks called pages. If you have rows that are 100k each, you can fit about 80 of those rows into a given page. If you update one of those rows to have more data that contains a variable length field like VARCHAR, NVARCHAR, and others, that will cause the page to overflow forcing a page split. The page split takes about half of the data and moves it into a new page, leaving about half in the original page. Another action that causes a page split is to insert a row that based on the indexing would go into a page that is nearly full, if the inserted row doesn’t fit a page split occurs.

If there is room, and your update or insert doesn’t require a split, this is pretty quick to do since SQL Server is just updating one page and then writing it to disk, and to the transaction log. But if the updated or inserted row doesn’t fit, SQL Server needs to allocate a new page, move about half the rows, and then write both pages to disk and to the transaction log. Additionally the pages in all the indexes that point to the data pages need to be updated. Let’s say your table had 1 clustered index, 4 nonclustered indexes, at a minimum 7 pages would be updated, 1 for the clustered index structure, 4 for the nonclustered indexes, and 2 in the data pages in the clustered index. In this specific example the page split would cause a minimum of 7 times the I/O as an insert or update that didn’t require a page split.

Read more ›

Posted in Performance, Performance Tuning Tagged with: , , , , , , ,

Blog hosting trouble while on vacation

Download PDF

This last weekend I went on vacation to the San Juan islands for 4 nights. Our annual family trip to the islands for some good family time, and fishing.

Here are some photos from the trip.

 

 

Boys

Read more ›

Posted in Uncategorized Tagged with: ,

Error Log: I/O is Frozen On Database

Download PDF

I/O is Frozen on Database. You are scanning your SQL Server Logs and discover the following error messages. There may be a long list of them, one frozen and one resumed for each database on your SQL Server.

I/O Is Frozen On Database

spid61,Unknown,I/O is frozen on database [master]. No user action is required.
spid61,Unknown,I/O is frozen on database [model]. No user action is required.
spid61,Unknown,I/O is frozen on database [msdb]. No user action is required.
spid61,Unknown,I/O is frozen on database [databaseName1]. No user action is required.
spid61,Unknown,I/O is frozen on database [databaseName2]. No user action is required.
spid61,Unknown,I/O was resumed on database [master]. No user action is required.
spid61,Unknown,I/O was resumed on database [model]. No user action is required.
spid61,Unknown,I/O was resumed on database [msdb]. No user action is required.
spid61,Unknown,I/O was resumed on database [databaseName1]. No user action is required.
spid61,Unknown,I/O was resumed on database [databaseName2]. No user action is required.


IO Is Frozen on DatabaseIO Is Frozen on Database

The error message doesn’t sounds good, I/O frozen… Your SQL Server needs its I/O, and how could frozen I/O be anything but bad?

Well it turns out that when Veeam, Veritas, Symantec Business Continuance Volume (BCV) or other third party backup tools that uses a VSS (Volume Shadow Copy Services), it temporarily freezes the I/O, then quickly resumes it. Keep in mind that Veeam for instance is a really awesome tool, that just happens to use a built in SQL Server feature to use snapshots on the backup. Veeam an other backup tools do this to get the most solid backup possible when a virtual machine is being backed up.

Read more ›

Posted in Performance, Performance Tuning Tagged with:

Website Outage

Download PDF

Last night my website (this site) experienced an outage. Sometime during the night the Mysql process hosting my WordPress website crashed. Too bad there isn’t a WordPress solution that uses a SQL Server database.

Things are back now, and the site is working.

Knowing so much about SQL Server and high availability, it is difficult to rely on technology like Mysql where I don’t have that high availability background. Oh well, the site is back now.

Posted in Uncategorized Tagged with:

Steps to Improve ETL Performance Using SSIS

Download PDF

Some of the biggest performance tuning wins that I have had over my career have been related to performance tuning queries that are being called from SSIS. So often the actual query performance gets overlooked in the SSIS environment. A few years ago I was able to tune a query that was being called from an SSIS package and reduce the runtime of that package from over an hour, to just 2 minutes. Its not always that big of a win, and there are other performance tuning options besides just tuning the queries, so lets take a look at some of them.

ETL Performance Using SSIS parallel queries.SQL Server Integration Services (SSIS) is one of those components that can help you out with your ETL (Extract, Transform and Load) work, or it can hinder your process if it is a poor performing ETL. There are many things that can easily cause an ETL to run for hours, when it should be running for minutes.

The following tips are intended as things to consider when performance tuning to improve your ETL Performance Using SSIS.

Read more ›

Posted in Performance Tuning, SSIS Performance 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 SteveStedman.com 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 SteveStedman.com with my newsletter.
Newsletter signup form.

Stedman Solutions

StedmanSolutionsRemoteSkilledDBA