Skip to content

SQL Server

Using SQL Server Compressed Backups

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 »Using SQL Server Compressed Backups

Max Server Memory – SQL Server

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 »Max Server Memory – SQL Server

Presenting at SQL Saturday Las Vegas

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 »Presenting at SQL Saturday Las Vegas

mssqlsystemresource Database

I was looking through my SQL Server error logs to confirm that CheckDB was being run as I had scheduled based on my previous post to run DBCC CheckDB on all databases. I wanted to confirm that there was no corruption, and that all of the databases had been checked, and not had been missed. Going through this I noticed the logging of all of my databases, but one extra database showed up, the mssqlsystemresource database.

mssqlsystemresource database

Now the mssqlsystemresource database is an internal SQL Server database that is used by SQL Server, it also gets replaced by SQL Server when you do an upgrade of your SQL Server database. It is hidden so that people don’t have access to it, and it doesn’t show up when you run the undocumented sp_msforeachdb it doesn’t include the mssqlsystemresource database. Additionally the sys.databases view doesn’t include the mssqlsystemresource database.

Read More »mssqlsystemresource Database

DBCC CheckDB All Databases

If you use the SQL Server maintenance plans to run DBCC Check DB, you are not allowed to include TempDB in your DBCC CheckDB sequence. For a while I was using maintenance plans, and ending up adding a separate job step to run TSQL to just check TempDB.

Rather than using two different steps, the regular maintenance plan, and a special step for Temp DB, I have instead dumped the regular maintenance plan step, and just used the following TSQL code to run DBCC CheckDB against all databases on the SQL Server.

Option 1: DBCC CheckDB All Databases using sp_msforeachdb

I have included Option 2 below since there are some pretty serious flaws in the sp_msforeachdb script as shown in option 1, specifically sp_msforeachdb will occasionally skip databases. It also has trouble with databases created with certain characters.  Anyway if you want it, here is, but I would recommend paging down to Option 2 which is much more reliable. I would like to thank Patrick Flynn who showed me the flaws in sp_msforeachdb.

The stored procedure sp_msforeachdb takes a parameter of query that will get run against all databases on your SQL Server Instance.


EXEC sp_msforeachdb 'DBCC CHECKDB(''?'') WITH NO_INFOMSGS;';

You can set it up as a maintenance plan like this:

Read More »DBCC CheckDB All Databases

Database Health Monitor Version 2.1 coming soon

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 »Database Health Monitor Version 2.1 coming soon