Skip to content

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

Status of DBCC CheckDB

So you are checking your database with DBCC CheckDB and of course if you are like me you use the WITH NO_INFOMSGS parameter. But it turns out that CheckDB is taking longer to run that you expected, and you want to check and see what table is currently being checked.

Here is a quick SQL Script that I use to find the current table that is being checked by DBCC CheckDB

use [your database];
go

-- use this to find the session id of DBCC CheckDB
SELECT session_id, start_time, command, percent_complete, total_elapsed_time,
       estimated_completion_time, database_id, user_id, last_wait_type
  FROM sys.dm_exec_requests
 CROSS APPLY sys.dm_exec_sql_text(sql_handle)
 WHERE command like '%DBCC%';  

DECLARE @sessionID as integer = 53; -- enter the session ID of DBCC CheckDB Here

SELECT name, schema_id, type_desc
  FROM sys.objects
 WHERE object_id = (SELECT TOP 1 resource_associated_entity_id
                      FROM sys.dm_tran_locks
					 WHERE request_session_id = @sessionID
					   AND resource_type = 'OBJECT'
					   AND resource_associated_entity_id <> 50);



status of dbcc checkdb


Read More »Status of DBCC CheckDB

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