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:

PREEMPTIVE_OS_GETPROCADDRESS and xp_create_subdir

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.

PREEMPTIVE_OS_GETPROCADDRESS and xp_create_subdir

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:

https://www.youtube.com/steveStedman

 

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;
GO
RECONFIGURE;
GO

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:

Featured Report: Backup Status Report

Download PDF

As part of the Database Health Monitor version 2.1 release there were many new report includes, one of those was the backup status report. A quick way to page through all of your SQL Servers and check on the status of backups.

Here is an example of the backup status report showing a database that hasn’t had any recent backups, notice the amount of red text to quickly point out the databases with no recent backups.

Backup Status

Here is an example of a database with recent backups (Note the screen shot was take on September 3rd in the afternoon.

Backup Status

On of my favorite new features added to the Database Health Monitor in the version 2.1 release is the ability to page up and down through the different instances you are connected to. Lets say you have 30 SQL Servers that you need to check on, you can just connect to all of those with Database Health Monitor, then when you are viewing the report use the CTRL+Page Up or CTRL+UP arrow to jump to the previous instance, and you can use CTRL+Page Down or CTRL+Down arrow to jump to the next instance. This makes it very quick and easy to scan through all your SQL Servers and get a visual representation of their status regularly.

You can download Database Health Montor for free today at the Database Health Monitor download page at http://DatabaseHealth.com

Is it really free???   People keep asking me this, YES its really free, free for personal use, free for commercial use, free for consultants, corporations, anyone who wants to use it.

Enjoy!

Posted in Database Health, DBA Tagged with: , ,

Database Health Monitor version 2.1 Just Released

Download PDF

I have just released version 2.1 of Database Health Monitor, this release involved 2 months of development since version 2.0 was released in July.

People often times ask me if this is free, or free to use in production, or free to use on more than one server. The answer is yes indeed, this is a totally free application to use on as many SQL Servers as you want, fully featured, not a trial version. Yes its free. I know that can be hard for some to understand in today’s world, but yes it is free.

Download link for Database Health Monitor

 

Database Health Monitor version 2.1

Version 2.1 Release Notes

Released September 7th 2015.
Two months since Database Health Monitor version 2 was released and there have been many new features and bug fixes.

New Features

  • Added support for the F5 key to refresh all report pages.
  • Hotkeys added for paging up and down, and back and forth through instance level reports.
    • CTRL + up arrow, or CTRL + PGUP to switch to the previous instance.
    • CTRL + down arrow or CTRL + PGDOWN to switch to the next instance.
    • CTRL + left arrow to switch to the previous report.
    • CTRL + right arrow to switch to the next report.
  • For historic monitoring, it is no longer required to enter your username and password to set up monitoring if you are monitoring the current instance.
  • From the connection advisor you now have the ability to kill the session you are viewing.
  • The main list of databases has been renamed to the Database Health Monitor – Performance Dashboard.
  • The performance dashboard now includes a summary of total server memory, and for SQL Server 2008 and newer it shows the available memory.
  • The performance dashboard now shows the active query count. Those that are in memory and running for more than a second.
  • Quick Scan Report – many additional checks
    • Reporting for autoshrink and autoclose added to the Quick Scan Report.
    • Check for the default max server memory setting.
    • Check for bloated error logs that haven’t been cycled in a while.
    • Adding check for DBCC CheckDB never run, or not recently run.
    • Check for page verify option of NONE or TORN_PAGE_DETECTION.
  • Added a percentage column to the CPU used by database report.
  • Instance reports panel added into the server overview page. Previously you could only get to the instance reports from the right click popup menu, now they are easier to get to.
  • Instance level reports added.
    • Backup Status Report
    • Configuration Values
    • CPU By Database
    • Database by Size
    • I/O by Drive
    • Job History
    • Sessions by Login
    • Trace Flags
    • What is Active
  • Right click ability to kill sessions from the current connections report.
  • Added a help text panel on the bottom left, which gives additional details on some of the reports.
  • CTRL+a for select all on the connections advisor dialog.
  • CTRL+a for select all on the backup advisor restore chain script.
  • CTRL+a for select all on the one time query advisor.

 

With all these new features and all of the existing features, this application is becoming extremely useful to DBAs and database developers worldwide.

One of my favorite new features in this version is the ability to page through the instance level reports and to quickly switch SQL Server instances with the CTRL+Up, CTRL+Down, CTRL+Right, and CTRL+Left key combinations. This saves me so much time when I am checking in on my databases.

Download it for free today at the Database Health Monitor download page at http://DatabaseHealth.com

Enjoy!

Posted in Uncategorized Tagged with: , , , , , , ,

Presenting at SQL Saturday Las Vegas

Download PDF

This Saturday, September 12th, 2015 I will be presenting at SQL Saturday Las Vegas. My presentation is on Database Corruption, here are the details.

When Database Corruption Strikes – Will you be ready?

Duration: 60 minutes

Track: Enterprise Database Administration & Deployment

You are working along month after month with no problems in your database. Suddenly someone reports that their query won’t run. They get an error stating “SQL Server detected a logical consistency-based I/O error“, or something even scarier. Do you know what to do now? We will walk through 3 or 4 actual corrupt databases exploring ways to go about finding and fixing the corruption. More importantly we will explore how to prevent further data loss at the time corruption occurs. Learn what things you should do to protect yourself when corruption strikes. Learn what to avoid that will make things worse. You will leave with a checklist of steps to take when you encounter corruption. By the end of this session you will be ready to take on corruption, one database at a time.

SQL Saturday Las Vegas

The presentation is scheduled for 11:00 am to Noon in room 2767 at the College of Southern Nevada campus in Vegas.

Read more ›

Posted in Corruption, 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 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

Archives